If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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?

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.

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.

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?

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.