Add/Drop Partitions

[akondala]

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.

[slimdave]

>> 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?

[jhmartin]

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?

[yxez]

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

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

[jhmartin]

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.

[yxez]

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

[slimdave]

>> 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.

[jhmartin]

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.

[yxez]

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?

[slimdave]

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.

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

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