Partition Local or Global Index in Oracle

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;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.