RE: Index on Partition Table

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 10 Jun 2010 16:31:52 -0700
Message-ID: <6592BDAF72405343BA096C66B735B38E0258EB8DE6_at_alvxmbw04.prod.quest.corp>

If you don't specify GLOBAL, then a local partitioned index will be created. A local index will be partitioned using the same partitioning scheme as the table.

Local indexes simplify partition operations like DROP PARTITION, EXCHANGE PARTITION (including indexes) - you don't need to rebuild the index after you do those operations. There is a significant limitation on local indexes: for a unique locally partitioned index, or a local index used to enforce a primary key or unique constraint, the index columns must be a superset of the partitioning key. If your index columns are not a superset of the partitioning key, then you will be unable to create a unique locally partitioned index on your partitioned table, and a non-unique locally partitioned index cannot be used by Oracle to enforce a primary key or unique constraint.

Global indexes are either not partitioned, or partitioned using a different partitioning method than the table, so any partition operation will require an index rebuild. The partition operations include an UPDATE GLOBAL INDEXES clause so that you can rebuild the indexes while you are, for example, doing a SPLIT PARTITION.

Depending on how the index is used, either can have performance benefits.

De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de Michael Dinh Envoyé : jeudi 10 juin 2010 16:18
À : oracle-l_at_freelists.org
Objet : Index on Partition Table

For a partition table, what is the difference when creating index if global is specified or not.

Example:

ALTER TABLE fct ADD CONSTRAINT fct_pk PRIMARY KEY (key) USING INDEX;

ALTER TABLE fct ADD CONSTRAINT fct_pk PRIMARY KEY (key) USING INDEX GLOBAL;

Thanks

Michael Dinh : XIFIN : 858.436.2929

NOTICE OF CONFIDENTIALITY - This material is intended for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of this email (and attachments) is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. Please notify the sender of the error and delete the e-mail you received. Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 10 2010 - 18:31:52 CDT