Link to home
Start Free TrialLog in
Avatar of sigma19
sigma19Flag for United States of America

asked on

convert Global index to local index in oracle

Hi experts,

How do i convert non unique global index which has data to a local index on a partitioned table without dropping and creating the index. Any thoughts??
Avatar of Sean Stuber
Sean Stuber

Sorry, but you can't.
Avatar of sigma19

ASKER

The problem is our DB developers created all indexes as GLOBAL. when we are dropping the partitions the indexes are getting invalid and we need to rebuild them everytime which we want to avoid.

So, the only option is drop and recreate as LOCAL index right??
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sdstuber is 100% correct so please give credit to his answer.

It is important to understand why. You must understand the concept of Oracle "segments".

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/logical.htm

A regular table is a single segment.
A regular index is a single segment.
A partitioned table is multi-segment (each partition is a segment).
A global index is a single segment (though it indexes multiple table segments).
A locally partitioned index is multi-segment (a segment goes with each partition).

A segment cannot span tablespaces. A partitioned object (partitioned table or locally partitioned index) may span tablespaces (each segment must be whole within a tablespace).

So your question is in essence "Can I split a single segment object into a multi-segment object".

And the answer is as stated, it takes a rebuild to do this, as the datablocks & extents must physically be re-organized and assigned to different segments.
Avatar of sigma19

ASKER

Thanks for the response..

Can you give me some pros and cons of having LOCAL vs GLOBAL index on PK (while quering , DML operations) . I am aware that the partition key should be part of PK to be LOCAL index.
You've already discovered the big one.

If you need to manipulate (in your case, drop) a table partition then the entire global index will need to be rebuilt where as a local is, well, "localized" to just that partition.

Note in 11g you can have reference partitioning which allows local partitions based on the partition key of a different table.

For instance,  a table of orders partitioned by shipping month

the order lines could be reference partitioned by shipping month even though they don't actually have a shipping month column in them.

This may or may not apply to your specific example.  Only mentioned it because of your previous post about "required" partition keys