Partitioned Indexes

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.

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.
.
.
.