In a partitioned table, what is the difference between a local index and a global index. Is there a advantage/disadvantage in choosing one against the other?
Printable View
In a partitioned table, what is the difference between a local index and a global index. Is there a advantage/disadvantage in choosing one against the other?
If the range of the index partition is the same as the range on the table partition, it's a local index, else it's a global index. If the search for data is at the partition level it's good to have a local index, else have a global index.
Hi, 27th April 2001 20:52 hrs chennai
The diff is as follows.
If the table partition range and the index partition range are same then it is called as Global Partitoning.
If the range of the table partition and the Index partition range differs then it is called as Local partition.
I think the global partition range are same then the access speed will be much higher .
Cheers
padmam
Hi, 27th April 2001 20:58 hrs chennai
As Halo said is right i have changed the meaning
Panjub its vice versa.
The Global Index Partiton refers from more than one underlying table partiton i.e the Index partition range are not same as that of table partition
Cheers
padmam
hi, 27th April 2001 21:06 hrs chennai
There are several adv of using local index partitioning.
Alos you have to see whcih among the local prefixed,local Non prefixed,global prefixed,global nonprefixed is more adv.
It is more expensive to probe into a nonprefixed index than to probe into a prefixed index.
Local indexes have the following advantages as per oracle docs.
=====================================
Only one index partition needs to be rebuilt when a maintenance operation (other than SPLIT PARTITION, or ADD PARTITION for a hash partition) is performed on an underlying table partition.
The duration of a partition maintenance operation remains proportional to partition size if the partitioned table has only local indexes.
Local indexes support partition independence.
Local indexes support smooth roll-out of old data and roll-in of new data in historical tables.
Oracle can take advantage of the fact that a local index is equipartitioned with the underlying table to generate better query access plans.
Local indexes simplify the task of tablespace incomplete recovery. In order to recover a partition or subpartition of a table to a point in time, you must also recover the corresponding index entries to the same point in time. The only way to accomplish this is with a local index; then you can recover the corresponding table and index partitions or subpartitions together.
=========
Cheers
padmam