Types of Index Partitions

In previous article we have seen overview of index partitioning and advantages of it.

In this article we will see example of different types of indexes on partitioned tables.

1.Non-partitioned Index:

We can create normal non-partitioned index on partitioned table.

Example : TRANSACTIONS table is partitioned table in my database.

SQL> CREATE INDEX TRANSACTIONS_IND ON TRANSACTIONS(DATE_OF_TRANSACTION);

Index created.

SQL> 

2.Global Range Partitioned Indexes : We can create GLOBAL index on partitioned table :

Example : 

SQL> CREATE INDEX TRANSACTION_GLOBAL_IND 
  2  ON TRANSACTIONS(DATE_OF_TRANSACTION)
GLOBAL PARTITION BY RANGE (DATE_OF_TRANSACTION)
(PARTITION P1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
 PARTITION P2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
 PARTITION P3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
 PARTITION P4 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
 PARTITION P5 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
 PARTITION P6 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
 PARTITION P7 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
 PARTITION P8 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
 PARTITION P9 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
 PARTITION P10 VALUES LESS THAN (MAXVALUE)
 );  3    4    5    6    7    8    9   10   11   12   13   14  
ON TRANSACTIONS(DATE_OF_TRANSACTION)
                *
ERROR at line 2:
ORA-01408: such column list already indexed

We have already created index on this table and on the same column. Lets drop it and create it again.

SQL> DROP INDEX 
  2  TRANSACTIONS_IND;

Index dropped.

SQL> CREATE INDEX TRANSACTION_GLOBAL_IND 
ON TRANSACTIONS(DATE_OF_TRANSACTION)
GLOBAL PARTITION BY RANGE (DATE_OF_TRANSACTION)
(PARTITION P1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
 PARTITION P2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
 PARTITION P3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
 PARTITION P4 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
 PARTITION P5 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
 PARTITION P6 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
 PARTITION P7 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
 PARTITION P8 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
 PARTITION P9 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
 PARTITION P10 VALUES LESS THAN (MAXVALUE)
 );  2    3    4    5    6    7    8    9   10   11   12   13   14  

Index created.

SQL> 

3.Global Hash Partitioned Index : Hash partitioned index will distribute data evenly using hashing algorithm.

Note : With Global option we can only create hash and range index partitioning.

SQL> CREATE INDEX TRAN_HASH_IDX
ON TRAN_HASH(TRAN_ID) GLOBAL 
PARTITION BY HASH(TRAN_ID)
(PARTITION P1 TABLESPACE PART1,
PARTITION P2  TABLESPACE PART2,
PARTITION P3  TABLESPACE PART1,
PARTITION P4  TABLESPACE PART2);  2    3    4    5    6    7  

Index created.

SQL> 

4.Local Index on Partitioned Table : 

SQL> CREATE INDEX TRANSACTION_IDX ON TRANSACTIONS(DATE_OF_TRANSACTION) LOCAL;

Index created.

5.LOCAL Partitioned Index : You need to define same number of partitions for the index as you have defined for the table if you are creating local partitioned index and you want to explicitly define partitions.

SQL> CREATE INDEX TRANSACTION1_IDX_PART ON TRANSACTIONS1(DATE_OF_TRANSACTION) LOCAL
(PARTITION P1,
PARTITION P2,
PARTITION P3,
PARTITION P4,
PARTITION P5,
PARTITION P6,
PARTITION P7,
PARTITION P8,
PARTITION P9
)
;
  2    3    4    5    6    7    8    9   10   11   12
Index created.

6.Create Global Index :

SQL> CREATE TABLE DEPT_LIST
(EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_ID CHAR(3))
PARTITION BY LIST(DEPT_ID)
(PARTITION P1 VALUES('SLS','ACC'),
PARTITION P2 VALUES('HRM','MAR'));   2    3    4    5    6    7

Table created.

SQL>
CREATE INDEX DEPT_LIST_IDX ON DEPT_LIST(DEPT_ID) GLOBAL;SQL>

Index created.

SQL>

7.Create Local Bitmap Index :

SQL> CREATE BITMAP INDEX DEPT_LIST_BIT_IDX ON DEPT_LIST(EMP_NAME);
CREATE BITMAP INDEX DEPT_LIST_BIT_IDX ON DEPT_LIST(EMP_NAME)
                                         *
ERROR at line 1:
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables


SQL>

In above code we can see that we are not able to create bitmap index globally as it partitioned table. Let’s try with LOCAL keyword[Local Index].

SQL> CREATE BITMAP INDEX DEPT_LIST_BIT_IDX ON DEPT_LIST(EMP_NAME) LOCAL;

Index created.

SQL>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp