Add/Drop Partitions

Hi,


created a partition table with Global Partition indexing. I dropped a partition and created new partition without any index creations. The drop partition still left the partition index and didn't create a new index for the new added partition.

SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS;


INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PTTRACKLOG_PAR_INDX PT1 UNUSABLE
PTTRACKLOG_PAR_INDX PT3 USABLE
PTTRACKLOG_PAR_INDX PT2 USABLE


SQL> select table_name, partition_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
ASH_PTTRACKLOG PT4
ASH_PTTRACKLOG PT3
ASH_PTTRACKLOG PT2

From the above - PT1 partition is dropped and PT4 is added. PT1 index is still existing but unusable and PT4 partition didn't create any new index on PT4?
If i drop a partition, should this also drop the corresponding partition index if the Global indexing is using?

If a new parition is added, will this create a new partition index automatically, with the same name as new tabl partition?

Should all the partition indexes needs to be rebulit or just the index on the new partition added?


Thanks.

>> If i drop a partition, should this also drop the corresponding partition index if the Global indexing is using?

There is no corresponding index partition for the table partition unless you have used local partitioning of the index.

>>If a new parition is added, will this create a new partition index automatically, with the same name as new tabl partition?

No -- that happens with local index partitioning.


why did you choose to use global partitioning instead of local?

Global indexing means 1 index for the entire table. That index should have gone invalid and require a rebuild as soon as you dropped one of the partitions , unless you did a UPDATE INDEXES in the drop partition command.

Adding a new partition always invalidates global indexes.

Local indexes are not the same as partitioned indexes. If you create local indexes then Oracle will take care of creating or dropping the associated partitions of the index when the base partition goes away. If you just created a partitioned index on the table (ie not as a local index) then you have to do all the work.

What create statement did you use for the index?

Quote:

---

Originally Posted by slimdave

why did you choose to use global partitioning instead of local?

---

Because he finds it more appropriate for his need dear :p
Does your question mean all the global indexing can be substituted
by local indexing?

Quote:

---

Originally Posted by yxez

Does your question mean all the global indexing can be substituted
by local indexing?

---

Local indexes must contain the partition key, and it is ideal if the leading column of the index IS the partition key. If you can't do that then local indexes won't work.

Quote:

---

Originally Posted by jhmartin

Global indexing means 1 index for the entire table.

---

Hi dear....i forgot my partitioning knowledge because I haven't read
it again for a long time. I have just created a partitioned table
and a global index and did not bother about other options.

What is the advantage/disadvantage again of a Global Index vs
Global Partitioned Index vs a Local index in a Partitioned Table?

If i relate it to a partitioned table:

A. Non-partitioned, index by travel_date and fullname
---------------------------------------------------
Select fullname from TRAVEL where travel_date='01-DEC-93'
and fullname like 'BIN%LADEN%OSAMA%'

B. Partitioned by travel_date, Global index by travel_date and fullname
-------------------------------------------------------------
Select fullname from TRAVEL PARTITION(TRAVEL1993)where
and fullname like 'BIN%LADEN%OSAMA%'

Which do u think runs faster? Or is partitioning just for ease
in managing large tables and not really performance.

Is it the same with Partitioned Indexes? They have same
performance benefit with non partitioned index but only segment management differs.


Thanks

>> Global indexing means 1 index for the entire table.

Global partitioned indexes do not mean that.

>> Adding a new partition always invalidates global indexes.

No, it never does.

>> Local indexes are not the same as partitioned indexes. ... If you just created a partitioned index on the table (ie not as a local index)

Rather a confusing explanation -- local and global are just two different types of partitioned indexes.

I admit that they are both different kinds of partitioned indexes, but they differ in their behavior significantly so they shouldn't be generalized as just partitioned indexes. Local indexes have unique behavior in regards to table partition operations.

Now that I think about it, you are correct that adding a partition wouldn't need to invalidate a global index. It is dropping that was the problem.

Quote:

---

Originally Posted by jhmartin

Local indexes must contain the partition key, and it is ideal if the leading column of the index IS the partition key. If you can't do that then local indexes won't work.

---

So, if I have an EMP table(Hiradate,Fullname)
partitioned by HireDate, I can only create a partitioned
index on hiredate column, And can create only a global index for
Fullname column.
What is the difference in performance in I create a global index
on Hiredate rather than a partitioned index? I guess none? :confused:

Quote:

---

Originally Posted by yxez

So, if I have an EMP table(Hiradate,Fullname)
partitioned by HireDate, I can only create a partitioned
index on hiredate column, And can create only a global index for
Fullname column.

---

no -- if you partition the index on the same key as the table with the same intervals then you have a locally partitioned index. If you partition on a different column you have a global partitioned index.

Quote:

---

What is the difference in performance in I create a global index
on Hiredate rather than a partitioned index? I guess none? :confused:

---

There is no magic performance boost, it all depends on how you use the data. Also this is all very well documented.

yes...very well explained dear :cool:

I just wonder why do I have to locally index my Hiredate
when the table is already partitioned by it and usually it has low
cardinality(lots of dups) because its a range partition

People,
I am not very good in partitioning but here is what I know about that
Local indexes helps in maintanence. You can rebuild just one partition, truncate/drop/add corresponding partition when you truncate/drop/add partition to the table, etc.
If you do all that with global indexes, it will invalidate the entire index and you will have to rebuild it which means generally sorting the entire data in the table (that's lot of work)
One can build an index on one column and partition by another (not included in the index) as fat as I remember, so no issue here with local indexes. The issue appears when one tries to build UNIQUE index. Then the index key should contain the partition key and that will force global index creation.
On the other hand, the primary/unique key constraint can be build on pre-created index which is not obligatory to be uniue, but I guess (no real experience) that such situation will bother the performance when checking the constraint

Regards
Boris

Quote:

---

Originally Posted by yxez

yes...very well explained dear :cool:

I just wonder why do I have to locally index my Hiredate
when the table is already partitioned by it and usually it has low
cardinality(lots of dups) because its a range partition

---

For what reason are you partitioning by hiredate?