Oracle partition index

In the previous article, we learned about Oracle Partitioned table. In continuation of that series,i will here be presenting the Oracle partition index

Here are the some of facts about Understanding Partitioned Indexes

Local Partition 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

FAQ

Also Reads

• Oracle database Administration Tutorials
• https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/index-partitioning.html#GUID-569F94D0-E6E5-45BB-9626-5506DE18FF00
• change date format in oracle