Local Index's are created using Oracle table partitions.
Partitioned table can be by range, hash and list
create local index on a partitioned table, it automatically creates index partitions as many as in the table partitions. whenever new partition is created automatically index is created
Local partitioned index creates one to one relationship between the table partitions. The key value for the table partition and the index partition must be identical
Advantages #
> Local Index’s are very easy to maintain
> we will go with Local Index’s where drop old partitions and add new partitions to the table
> more faster execution plans using partition pruning
step 1 # create partition table # populate data
step 2 # create local index on partition key
CREATE TABLE emp_info
(
emp_id NUMBER NOT NULL,
join_date DATE NOT NULL,
email VARCHAR2(100)
)
PARTITION BY RANGE (join_date)
(
PARTITION emp_info_p0 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')) TABLESPACE tnc_stage,
PARTITION emp_info_p1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')) TABLESPACE tnc_stage,
PARTITION emp_info_p2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')) TABLESPACE tnc_stage,
PARTITION emp_info_p3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')) TABLESPACE tnc_stage,
PARTITION emp_info_p4 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')) TABLESPACE tnc_stage
);
populate the data into partition table #
declare
begin
for i in 1..100000
loop
insert into emp_info values (i,'13-APR-2010','xyz'||i);
end loop;
end;
/
declare
begin
for i in 100001..200000
loop
insert into emp_info values (i,'13-APR-2011','xyz'||i);
end loop;
end;
/
declare
begin
for i in 200001..300000
loop
insert into emp_info values (i,'13-APR-2012','xyz'||i);
end loop;
end;
/
declare
begin
for i in 300001..400000
loop
insert into emp_info values (i,'13-APR-2013','xyz'||i);
end loop;
end;
/
declare
begin
for i in 400001..500000
loop
insert into emp_info values (i,'13-APR-2014','xyz'||i);
end loop;
end;
/
populating data completed on partition table #
check row count each partition in user_tab_partitions.NUM_ROWS.
row count shows Null defines Online statists gathering are off in 11g but not in 12c
> select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name='EMP_INFO';
> select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name='EMP_INFO';
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
EMP_INFO EMP_INFO_P0
EMP_INFO EMP_INFO_P1
EMP_INFO EMP_INFO_P2
EMP_INFO EMP_INFO_P3
EMP_INFO EMP_INFO_P4
gather stats manually in 11g as follows
SQL> EXEC DBMS_STATS.gather_table_stats('DELTA','EMP_INFO');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name='EMP_INFO';
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
EMP_INFO EMP_INFO_P0 100000
EMP_INFO EMP_INFO_P1 100000
EMP_INFO EMP_INFO_P2 100000
EMP_INFO EMP_INFO_P3 100000
EMP_INFO EMP_INFO_P4 100000
STEP2 #
Creating Local Index # on partition table
>create index partition_local_idx on EMP_INFO (join_date) local;
Querys Used to check partition index's
>select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where index_name='partition_local_idx’;
> select index_name, partition_name from user_ind_partitions where index_name='partition_local_idx';
SQL> select index_name, partition_name from user_ind_partitions where index_name='PARTITION_LOCAL_IDX';
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
PARTITION_LOCAL_IDX EMP_INFO_P0
PARTITION_LOCAL_IDX EMP_INFO_P1
PARTITION_LOCAL_IDX EMP_INFO_P2
PARTITION_LOCAL_IDX EMP_INFO_P3
PARTITION_LOCAL_IDX EMP_INFO_P4
Online gather stats for index are enabled in 10g and 11g
No need to gather stats in recent versions of oracle
Global Index # To be continued --
References #
http://www.oraclebuffer.com/oracle/oracle-12c-global-index-maintenance-is-now-asynchronous/
