Partition Local or Global Index for Partition table in Oracle
Partitioning Index have two type:
Local Index: is the index used in oracle for partition table. It is one to one relation with table partition and index partition.
Global Index: is the index used in oracle for partition table or normal table. It is one to many relation with an index linked with multiple table partition or table.
Note: Non partitioned indexes are global indexes by default.
Index are further sub divided with prefixed or non prefixed:
Prefixed: in this left most column is the key used in partition for table, used by optimizer as less cost.
Non prefixed: index an column that is not the key for partition for table
Local Index Syntax:
-- It will automatically create according to partition of table
CREATE INDEX SALES_IDX ON SALES (date_captured) LOCAL;
OR
-- You can specify manually
CREATE INDEX SALES_IDX_IDX ON sales (date_captured) LOCAL
(PARTITION SALES_q1 TABLESPACE users,
PARTITION SALES_q2 TABLESPACE users,
PARTITION SALES_q3 TABLESPACE users,
PARTITION SALES_q4 TABLESPACE users);
Global index syntax:
-- Default index
CREATE INDEX SALES_IDX ON SALES (date_captured);
OR
--create global index by Range partition, you can also create global partition on hash
CREATE INDEX sales_idx ON sales (date_Captured)
GLOBAL PARTITION BY RANGE (date_captured)
(PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01/04/2017', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01/07/2017', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01/09/2017', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users);
Check the index is local or global:
select INDEX_NAME,INDEX_type ,GLOBAL_STATS from dba_indexes;
Check the partition index:
select index_name, partition_name,status from dba_ind_partitions;
Rebuild the global or local index:
local or global index having partition:
SQL> alter index sales_IDX rebuild;
alter index sales_IDX rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
Rebuild partition index by specifying partition name.
alter index sales_IDX rebuild partition sales_Q4 tablespace users;
Check the index list with column with below query:
column b.tablespace_name format a20
column table_name format a20
column index_name format a30
column column_name format a20
Select a.table_name, a.index_name, a.column_name,b.tablespace_name
FROM dba_ind_columns a,dba_indexes b where b.index_name = a.index_name and
a.table_owner='RETAIL' and a.table_name = 'SALES'
Order by a.table_name, a.Index_name,a.column_position,a.column_name;