Most discussions about Partitioning in Oracle are around Table Partitioning. Rarely do we come across Index Partitioning.
A couple of days ago, there was an Oracle Community question on Partitioned Indexes.
So, here is a quick listing of Index Partitioning options (these tests are in 11.2.0.4)
First, I start with a regular, non-partitioned table.
I now attempt to create an Equi-Partitioned (LOCAL) Index on it.
As expected I can't create a LOCAL index on a non-partitioned table.
Can I create any partitioned index on this table ?
I try two different GLOBAL PARTITIONed Indexes
So, I must have a MAXVALUE partition for the Index. Note that the two indexes above are now Partitioned without the table itself being partitioned.
The above indexes are Prefixed Global Partitioned Indexes. Can I create a Non-Prefixed Global Partitioned Index -- an Index where the Partition Key is not formed by the left-most columns of the index.
So, I have proved that a Non-Partitioned Table cannot have a LOCAL Partitioned Index or a Non-Prefixed Global Partitioned Index but can still have a Global Partitioned Index where the Partition Key is left-prefixed from the Index Key. Also, that a Global Partitioned Index can be a Composite Index with columns of different datatypes.
Let me now proceed with a Partitioned Table.
First, the Equi-Partitioned (LOCAL) Index.
The usage of the LOCAL keyword instead of GLOBAL defines the Index as equi-partitioned with the table. Index Partitions are automatically created to match the Table Partitions with the same Partition Names. It is possible to create a LOCAL Partitioned Index and manually specify Partition Names but this, in my opinion, is a bad idea. Attempting to manually name each Partition for the Index can result in a mis-match between Table Partition Names and Index Partition Names.
Next, I define two GLOBAL Partitioned Indexes on this table.
The Equi-Partitioned (LOCAL) Index has the same number (and, recommended, names) of Partitions as the Table.
However, the GLOBAL Indexes can have different numbers of Partitions.
As with the first case, I cannot create a Global Non-Prefixed Partitioned Index (where the Index Partition key is not a left-prefix of the Index).
In this blog post, I haven't touched on Partial Indexing (a 12c feature).
I haven't touched on Unique LOCALly Partitioned Indexes.
I haven't demonstrated the impact of Partition Maintenance operations (TRUNCATE, DROP, MERGE, ADD, SPLIT) on LOCAL and GLOBAL Indexes here -- although I have touched on such operations and LOCAL indexes in earlier blog posts.
.
.
.
A couple of days ago, there was an Oracle Community question on Partitioned Indexes.
So, here is a quick listing of Index Partitioning options (these tests are in 11.2.0.4)
First, I start with a regular, non-partitioned table.
SQL> create table non_partitioned 2 (id_col number, 3 data_col_1 number, 4 data_col_2 number, 5 data_col_3 varchar2(15) 6 ) 7 / Table created. SQL>
I now attempt to create an Equi-Partitioned (LOCAL) Index on it.
SQL> create index equi_part on non_partitioned (id_col) local; create index equi_part on non_partitioned (id_col) local * ERROR at line 1: ORA-14016: underlying table of a LOCAL partitioned index must be partitioned SQL>
As expected I can't create a LOCAL index on a non-partitioned table.
Can I create any partitioned index on this table ?
I try two different GLOBAL PARTITIONed Indexes
SQL> create index global_part 2 on non_partitioned (id_col) global 3 partition by range (id_col) 4 (partition p_100 values less than (101), 5 partition p_200 values less than (201) 6 ) 7 / ) * ERROR at line 6: ORA-14021: MAXVALUE must be specified for all columns SQL> SQL> create index global_part 2 on non_partitioned (id_col) global 3 partition by range (id_col) 4 (partition p_100 values less than (101), 5 partition p_200 values less than (201), 6 partition p_max values less than (MAXVALUE) 7 ) 8 / Index created. SQL> SQL> create index global_part_comp 2 on non_partitioned (id_col, data_col_3) global 3 partition by range (id_col, data_col_3) 4 (partition p_1 values less than (101,'M'), 5 partition p_2 values less than (101,MAXVALUE), 6 partition p_3 values less than (201,'M'), 7 partition p_4 values less than (201,MAXVALUE), 8 partition p_max values less than (MAXVALUE, MAXVALUE) 9 ) 10 / Index created. SQL>
So, I must have a MAXVALUE partition for the Index. Note that the two indexes above are now Partitioned without the table itself being partitioned.
SQL> select index_name, partitioned 2 from user_indexes 3 where table_name = 'NON_PARTITIONED' 4 order by 1 5 / INDEX_NAME PAR ------------------------------ --- GLOBAL_PART YES GLOBAL_PART_COMP YES SQL>
The above indexes are Prefixed Global Partitioned Indexes. Can I create a Non-Prefixed Global Partitioned Index -- an Index where the Partition Key is not formed by the left-most columns of the index.
SQL> create index global_part_nonprefix 2 on non_partitioned (id_col, data_col_3) global 3 partition by range (data_col_1) 4 (partition p_1 values less than (101), 5 partition p_2 values less than (201), 6 partition p_max values less than (MAXVALUE) 7 ) 8 / partition by range (data_col_1) * ERROR at line 3: ORA-14038: GLOBAL partitioned index must be prefixed SQL> SQL> !oerr ora 14038 14038, 00000, "GLOBAL partitioned index must be prefixed" // *Cause: User attempted to create a GLOBAL non-prefixed partitioned index // which is illegal // *Action: If the user, indeed, desired to create a non-prefixed // index, it must be created as LOCAL; otherwise, correct the list // of key and/or partitioning columns to ensure that the index is // prefixed SQL>
So, I have proved that a Non-Partitioned Table cannot have a LOCAL Partitioned Index or a Non-Prefixed Global Partitioned Index but can still have a Global Partitioned Index where the Partition Key is left-prefixed from the Index Key. Also, that a Global Partitioned Index can be a Composite Index with columns of different datatypes.
Let me now proceed with a Partitioned Table.
SQL> create table partitioned 2 (id_col number, 3 data_col_1 number, 4 data_col_2 number, 5 data_col_3 varchar2(15) 6 ) 7 partition by range (id_col) 8 (partition p_100 values less than (101), 9 partition p_200 values less than (201), 10 partition p_max values less than (MAXVALUE) 11 ) 12 / Table created. SQL>
First, the Equi-Partitioned (LOCAL) Index.
SQL> create index part_equi_part 2 on partitioned (id_col) local 3 / Index created. SQL> select partition_name, partition_position 2 from user_ind_partitions 3 where index_name = 'PART_EQUI_PART' 4 order by 2 5 / PARTITION_NAME PARTITION_POSITION ------------------------------ ------------------ P_100 1 P_200 2 P_MAX 3 SQL>
The usage of the LOCAL keyword instead of GLOBAL defines the Index as equi-partitioned with the table. Index Partitions are automatically created to match the Table Partitions with the same Partition Names. It is possible to create a LOCAL Partitioned Index and manually specify Partition Names but this, in my opinion, is a bad idea. Attempting to manually name each Partition for the Index can result in a mis-match between Table Partition Names and Index Partition Names.
Next, I define two GLOBAL Partitioned Indexes on this table.
SQL> create index part_gbl_part 2 on partitioned (data_col_1) global 3 partition by range (data_col_1) 4 (partition p_1 values less than (1001), 5 partition p_2 values less than (2001), 6 partition p_3 values less than (3001), 7 partition p_4 values less than (4001), 8 partition p_max values less than (MAXVALUE) 9 ) 10 / Index created. SQL> create index part_gbl_part_comp 2 on partitioned (data_col_2, data_col_3) global 3 partition by range (data_col_2, data_col_3) 4 (partition p_a values less than (10, 'M'), 5 partition p_b values less than (10, MAXVALUE), 6 partition p_c values less than (20, 'M'), 7 partition p_d values less than (20, MAXVALUE), 8 partition p_e values less than (30, 'M'), 9 partition p_f values less than (30, MAXVALUE), 10 partition p_max values less than (MAXVALUE, MAXVALUE) 11 ) 12 / Index created. SQL> SQL> l 1 select index_name, partition_name, partition_position 2 from user_ind_partitions 3 where index_name in 4 (select index_name from user_indexes 5 where table_name = 'PARTITIONED' 6 ) 7* order by 1,3 SQL> / INDEX_NAME PARTITIO PARTITION_POSITION ------------------ -------- ------------------ PART_EQUI_PART P_100 1 PART_EQUI_PART P_200 2 PART_EQUI_PART P_MAX 3
PART_GBL_PART P_1 1 PART_GBL_PART P_2 2 PART_GBL_PART P_3 3 PART_GBL_PART P_4 4 PART_GBL_PART P_MAX 5
PART_GBL_PART_COMP P_A 1 PART_GBL_PART_COMP P_B 2 PART_GBL_PART_COMP P_C 3 PART_GBL_PART_COMP P_D 4 PART_GBL_PART_COMP P_E 5 PART_GBL_PART_COMP P_F 6 PART_GBL_PART_COMP P_MAX 7 15 rows selected. SQL>
The Equi-Partitioned (LOCAL) Index has the same number (and, recommended, names) of Partitions as the Table.
However, the GLOBAL Indexes can have different numbers of Partitions.
As with the first case, I cannot create a Global Non-Prefixed Partitioned Index (where the Index Partition key is not a left-prefix of the Index).
SQL> create index part_global_part_nonprefix 2 on partitioned (id_col, data_col_3) global 3 partition by range (data_col_1) 4 (partition p_1 values less than (101), 5 partition p_2 values less than (201), 6 partition p_max values less than (MAXVALUE) 7 ) 8 / partition by range (data_col_1) * ERROR at line 3: ORA-14038: GLOBAL partitioned index must be prefixed SQL>
In this blog post, I haven't touched on Partial Indexing (a 12c feature).
I haven't touched on Unique LOCALly Partitioned Indexes.
I haven't demonstrated the impact of Partition Maintenance operations (TRUNCATE, DROP, MERGE, ADD, SPLIT) on LOCAL and GLOBAL Indexes here -- although I have touched on such operations and LOCAL indexes in earlier blog posts.
.
.
.