Partitioning -- 3a : Indexes on a Partitioned Table

Building on the case study of the Range Partitioned Table from the previous Blog Post, here are some Indexes.

SQL> select index_name, tablespace_name, partitioned, uniqueness
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  /

INDEX_NAME                     TABLESPACE_NAME                PAR UNIQUENES
------------------------------ ------------------------------ --- ---------
SYS_C0017514                   HEMANT                         NO  UNIQUE

SQL> 
SQL> select column_name, column_position
  2  from user_ind_columns
  3  where index_name = 'SYS_C0017514'
  4  /

COLUMN_NAME                    COLUMN_POSITION
------------------------------ ---------------
SALE_ID                                      1

SQL> 

We have an Index automatically built for the Primary Key constraint (note that the Index was created in the user's DEFAULT Tablespace).  This Index is a Global, Non-Partitioned Index.   Therefore, any Partition Maintenance operation (for a non-empty Partition) on the table may set the Index UNUSABLE unless the UPDATE INDEXES clause is used.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
VALID

SQL> alter table sales_data drop partition P_2016;

Table altered.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
VALID

SQL> insert into sales_data
  2  values (1001,to_date('01-FEB-2017','DD-MON-RR'),'ABC1001',1,10,1000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table sales_data drop partition P_2017;

Table altered.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
UNUSABLE

SQL> 
SQL> alter index SYS_C0017514 rebuild;

Index altered.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
VALID

SQL> 

When I dropped the *empty* P_2016 Partition, the Index remained valid.  However, when I dropped the *non-empty*  P_2017 Partition (even if it has a single row and irrespective of whether Table/Partition statistics have been gathered) without the UPDATE INDEXES clause, the Index became UNUSABLE.  An UNUSABLE Unique Index will not allow fresh inserts (into *any* Partition of the table).

Next, I attempt to create a Local Partitioned Index.  Such an Index has a Partition corresponding to each Table Partition.

SQL> create index sales_data_lcl_ndx_1
  2  on sales_data (sale_date, invoice_number) local
  3  /

Index created.

SQL> select partitioned      
  2  from user_indexes
  3  where index_name = 'SALES_DATA_LCL_NDX_1'
  4  /

PAR
---
YES

SQL> select partitioned, tablespace_name, status   
  2  from user_indexes
  3  where index_name = 'SALES_DATA_LCL_NDX_1'
  4  /

PAR TABLESPACE_NAME                STATUS
--- ------------------------------ --------
YES                                N/A

SQL> 
SQL> select partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name = 'SALES_DATA_LCL_NDX_1'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
P_2018                         TBS_YEAR_2018                  USABLE
P_2019                         TBS_YEAR_2019                  USABLE
P_2020                         TBS_YEAR_2020                  USABLE
P_MAXVALUE                     USERS                          USABLE

SQL> 

A Local Partitioned Index is created with the LOCAL keyword in the CREATE INDEX statement.
For a Partitioned Index, the TABLESPACE_NAME and STATUS attributes carry no meaning at the Index level --- these have values for each Partition.

Note how the Index Partitions were created with the same Partition Name and Tablespace Name as the Table Partitions. Similarly, any Partition Maintenance operations (DROP, MERGE, SPLIT) at the Table level will automatically be applied to the Index, dropping/creating the corresponding Index Partition(s).

In my next post, I will look at two other types of Index definitions on a Partitioned Table  (Global Partitioned Index and Bitmap Index).