local vs global index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: local vs global index

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    78
    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?

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  3. #3
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Diff

    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
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  4. #4
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Re: Diff

    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
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  5. #5
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Advantage

    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

    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width