Here are the some of facts about Understanding Partitioned Indexes
- A local index on a partitioned table is created where the index is partitioned in exactly the same manner as the underlying partitioned table. That is, the local index inherits the partitioning method of the table. This is known as equi-partitioning.
- For local indexes, the index keys within the index will refer only to the rows stored in the single underlying table partition. A local index is created by specifying the LOCAL attribute, and can be created as UNIQUE or NON-UNIQUE.
- The table and local index are either partitioned in exactly the same manner, or have the same partition key because the local indexes are automatically maintained, can offer higher availability.
- As the Oracle database ensures that the index partitions are synchronized with their corresponding table partitions, it follows that the database automatically maintains the index partition whenever any maintenance operation is performed on the underlying tables
- for example, when partitions are added, dropped, or merged.
- A local index is prefixed if the partition key of the table and the index key are the same; otherwise it is a local non-prefixed index
SQL> CREATE TABLE EXP_PART (id number, item_id number, name varchar2(20)) PARTITION BY RANGE (id, item_id) (partition EXP_PART_1 values less than (10, 100), partition EXP_PART_2 values less than (20, 200), partition EXP_PART_3 values less than (30, 300), partition EXP_PART_4 values less than (40, 400)); Table created. SQL> CREATE INDEX test_idx ON EXP_PART(id, item_id) LOCAL (partition test_idx_1, partition test_idx_2, partition test_idx_3, partition test_idx_4); Index created. SQL> SELECT index_name, partition_name, status FROM user_ind_partitions where index_name='TEST_IDX' ORDER BY index_name, partition_name; INDEX_NAME PARTITION_NAME STATUS --------- ----------- ------ TEST_IDX IX_TEST_IDX_1 USABLE TEST_IDX IX_TEST_IDX_2 USABLE TEST_IDX IX_TEST_IDX_3 USABLE TEST_IDX IX_TEST_IDX_4 USABLE
Global Partitioned Indexes
- A global partitioned index is an index on a partitioned or non-partitioned table that is partitioned independently, i.e. using a different partitioning key from the table. Global-partitioned indexes can be range or hash partitioned.Global partitioned indexes are more difficult to maintain than local indexes. However, they do offer a more efficient access method to any individual record.
- During table or index interaction during partition maintenance, all partitions in a global index will be affected.
- When the underlying table partition has any SPLIT, MOVE, DROP, or TRUNCATE maintenance operations performed on it, both global indexes and global partitioned indexes will be marked as unusable. It therefore follows that partition independence it is not possible for global indexes.
- Depending on the type of operation performed on a table partition, the indexes on the table will be affected. When altering a table partition, the UPDATE INDEXES clause can be specified. This automatically maintains the affected global indexes and partitions.
- The advantages of using this option are that the index will remain online and available throughout the operation, and does not have to be rebuilt once the operation has completed.
SQL> CREATE TABLE EXP_PART (id number, item_id number, name varchar2(20)) PARTITION BY RANGE (id, item_id) (partition EXP_PART_1 values less than (10, 100), partition EXP_PART_2 values less than (20, 200), partition EXP_PART_3 values less than (30, 300), partition EXP_PART_4 values less than (40, 400)); Table created. SQL> CREATE UNIQUE INDEX EXP_PART_IDX ON EXP_PART(id, item_id) GLOBAL PARTITION BY RANGE (id, item_id) (partition EXP_PART_IDX_1 values less than (20, 200), partition EXP_PART_IDX_2 values less than (maxvalue, maxvalue)); Index created. SQL> SELECT index_name, partition_name, status FROM user_ind_partitions where index_name='EXP_PART_IDX' ORDER BY index_name, partition_name; INDEX_NAME PARTITION_NAME STATUS EXP_PART_IDX EXP_PART_IDX_1 USABLE EXP_PART_IDX EXP_PART_IDX_2 USABLE
Global Non-partitioned Indexes
- Global non-partitioned indexes offer the same efficient access to any individual record in any partition, and behave just like a non-partitioned index. Since the index structure is not partitioned, the index is available to all partitions.
- A scenario where this type of index would be useful is with a query that does not include the partition key of the table as a filter, but you still want the oracle optimizer to use an index
The index is called prefixed if the leading column(s) in the index definition is (are) the partition key column(s), otherwise it is called non prefixed.
No you cannot subpartition global index, but you can partition it. The only way to end up with index subpartition is to have sub-partitioned table with local index
By querying the column, LOCALITY, in DBA_PART_INDEXES.