The advantage of the indexes is the Oracle query engine will scan only
the required index partition to serve the query, thus speeding up the query significantly.
While creating the index on the Oracle table partitions we can use these keyword ‘LOCAL’.
Local indexes are partitioned indexes which are easy to manage and each index partition is associated with its respective table partitions.
The major advantage of local indexes is there will not be any index invalidation when you perform alter table exchange, drop, add partitions.
SQL> CREATE TABLE emp (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
hire_date DATE,
salary NUMBER
)
PARTITION BY RANGE (hire_date)
(
PARTITION p_2010s VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')), -- Partition for employees hired before 2020
PARTITION p_2020s VALUES LESS THAN (TO_DATE('01-JAN-2030', 'DD-MON-YYYY')), -- Partition for employees hired before 2030
PARTITION p_2030s VALUES LESS THAN (TO_DATE('01-JAN-2040', 'DD-MON-YYYY')), -- Partition for employees hired before 2040
PARTITION p_2040s VALUES LESS THAN (TO_DATE('01-JAN-2050', 'DD-MON-YYYY')), -- Partition for employees hired before 2050
PARTITION p_2050s VALUES LESS THAN (TO_DATE('01-JAN-2060', 'DD-MON-YYYY')) -- Partition for employees hired before 2060
);
SQL> select * from emp;
EMP_ID EMP_NAME HIRE_DATE SALARY
---------- ---------------------------------------- ----------
1 John Doe 15-MAY-15 50000
2 Jane Smith 25-NOV-22 60000
3 David Brown 05-FEB-31 65000
4 Sara Wilson 10-AUG-43 70000
5 Michael Green 20-JAN-55 75000Check the Index:-
SQL> select index_name from dba_indexes where owner='SAM';
INDEX_NAME
--------------------------
SYS_C007884
Let us create index:- (LOCAL index with out Index partitioning)
SQL> create index EMP_NAME_IDX on emp(EMP_NAME);-------------------No Key word like "LOCAL" used ..
Index created.
SQL> select index_name from dba_indexes where owner='SAM';
INDEX_NAME
---------------------------
SYS_C007884
EMP_NAME_IDX
SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes where INDEX_NAME='EMP_NAME_IDX';
INDEX_NAME INDEX_TYPE PAR GLO
-------------------- --------------------------- --- ---
EMP_NAME_IDX NORMAL NO YES
Note:- EMP_NAME_IDX is not the part of Local index and it is Normal Index we create it.
I just crosschecked the user part indexes and "no rows selected"
SQL> select locality from user_part_indexes where index_name='EMP_NAME_IDX'; -- this is normal Index ...
no rows selected
Let's create index with LOCAL - key word on SALARY columns
EMP_ID EMP_NAME HIRE_DATE SALARY
---------- ---------------------------------------- ----------
1 John Doe 15-MAY-15 50000
2 Jane Smith 25-NOV-22 60000
3 David Brown 05-FEB-31 65000
4 Sara Wilson 10-AUG-43 70000
5 Michael Green 20-JAN-55 75000Part 1 :- LOCAL index without Partition
SQL> create index salary_idx on emp(salary) LOCAL;--------
SQL> select index_name from dba_indexes where owner='SAM';
INDEX_NAME
--------------------
SYS_C007884
EMP_NAME_IDX
SALARY_IDX
col LOCALITY for a20
col TABLE_NAME for a10
SQL> select INDEX_NAME,TABLE_NAME, LOCALITY from user_part_indexes;
INDEX_NAME TABLE_NAME LOCALITY
-------------------- ---------- --------------------
SALARY_IDX EMP LOCAL
SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes where INDEX_NAME='SALARY_IDX';
INDEX_NAME INDEX_TYPE PAR GLO
-------------------- --------------------------- --- ---
SALARY_IDX NORMAL YES NO
SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='SALARY_IDX';
INDEX_NAME PARTITION_NAME STATUS
-------------------- --------------- --------
SALARY_IDX P_2010S USABLE
SALARY_IDX P_2020S USABLE
SALARY_IDX P_2030S USABLE
SALARY_IDX P_2040S USABLE
SALARY_IDX P_2050S USABLENote :-
1) When you create a index on a partitioned table using "LOCAL" clause, it automatically creates index as many as in the table partitions.
2) Similarly when we add a new partition to the table, Oracle automatically adds one index partition to the index as well.
Note:- dropping partition from the table and check the status of Partition index
SQL> ALTER TABLE EMP drop partition P_2050S; Table altered. SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='SALARY_IDX'; INDEX_NAME PARTITION_NAME STATUS -------------------- --------------- -------- SALARY_IDX P_2010S USABLE SALARY_IDX P_2020S USABLE SALARY_IDX P_2030S USABLE SALARY_IDX P_2040S USABLE
------------This is pure Index without (Index-Partation)
SQL> alter index SALARY_IDX drop partition P_2040S;
alter index SALARY_IDX drop partition P_2040S
*
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index
SQL> alter index SALARY_IDX add partition P_2050S;
SQL> alter index SALARY_IDX add partition P_2050S;
alter index SALARY_IDX add partition P_2050S
*
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index
SQL>
SQL> drop index SALARY_IDX;
Index dropped.
SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes where INDEX_NAME='SALARY_IDX';
no rows selected
SQL>
SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes
2 /
INDEX_NAME INDEX_TYPE PAR GLO
-------------------- --------------------------- --- ---
SYS_C007884 NORMAL NO NO
EMP_NAME_IDX NORMAL NO YES
IDX_EMP_SALARY NORMAL YES NO
Part -2 --Local Index with partition
*****************************************
SQL>
SQL> CREATE INDEX idx_emp_salary
ON emp (salary)
LOCAL
(PARTITION p_2010s TABLESPACE users,
PARTITION p_2020s TABLESPACE users,
PARTITION p_2030s TABLESPACE users,
PARTITION p_2040s TABLESPACE users,
PARTITION p_2050s TABLESPACE users); 2 3 4 5 6 7 8
ON emp (salary)
*
ERROR at line 2:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
Create Index with it's own partitions but on Local non-prefixed indexes
SQL> select TABLE_OWNER,TABLE_NAME,PARTITION_NAME from dba_tab_partitions where TABLE_OWNER='SAM' and TABLE_NAME='EMP';
TABLE_OWNE TABLE_NAME PARTITION_NAME
---------- ---------- ---------------
SAM EMP P_2010S
SAM EMP P_2020S
SAM EMP P_2030S
SAM EMP P_2040S
Note:- A local index is one on one mapping between an index partation and table partation.
You can create index for particular partition only not on whole table.
SQL> CREATE INDEX idx_emp_salary
ON emp (salary)
LOCAL
(
PARTITION p_2010s TABLESPACE users,
PARTITION p_2020s TABLESPACE users,
PARTITION p_2030s TABLESPACE users,
PARTITION p_2040s TABLESPACE users
); 2 3 4 5 6 7 8 9
Index created.
SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='IDX_EMP_SALARY';
INDEX_NAME PARTITION_NAME STATUS
-------------------- --------------- --------
IDX_EMP_SALARY P_2010S USABLE
IDX_EMP_SALARY P_2020S USABLE
IDX_EMP_SALARY P_2030S USABLE
IDX_EMP_SALARY P_2040S USABLE
SQL> select INDEX_NAME,TABLE_NAME, LOCALITY from user_part_indexes;
INDEX_NAME TABLE_NAME LOCALITY
-------------------- ---------- --------------------
IDX_EMP_SALARY EMP LOCAL
Let's try to drop partation from the IDX_EMP_SALARY index:-
---------------------------------------------------------------
alter index IDX_EMP_SALARY drop partition P_2040S;
You can't drop partation from the Index:-
--------------------------------------------
SQL> alter index IDX_EMP_SALARY drop partition P_2040S;
alter index IDX_EMP_SALARY drop partition P_2040S
*
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index
Now let's drop a PARTITION from the Table and check the impact of Index partition
SQL> alter table emp drop PARTITION P_2040S;
Table altered.
SQL> select TABLE_OWNER,TABLE_NAME,PARTITION_NAME from dba_tab_partitions where TABLE_OWNER='SAM' and TABLE_NAME='EMP';
TABLE_OWNE TABLE_NAME PARTITION_NAME
---------- ---------- ---------------
SAM EMP P_2010S
SAM EMP P_2020S
SAM EMP P_2030S
SQL>
SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='IDX_EMP_SALARY';
INDEX_NAME PARTITION_NAME STATUS
-------------------- --------------- --------
IDX_EMP_SALARY P_2010S USABLE
IDX_EMP_SALARY P_2020S USABLE
IDX_EMP_SALARY P_2030S USABLE
Note: When you dropped partition from the table, it's index partition also dropped automatically.Prefixed vs. Non-Prefixed:
A local prefixed index means the indexed column(s) include the partitioning column (hire_date in this case) as part of the indexed columns.
A local non-prefixed index means the indexed column(s) do not include the partitioning column.
Local Prefixed Index:-
CREATE INDEX idx_emp_prefixed ON emp (hire_date, salary) LOCAL;
Local Non-Prefixed Index:-
CREATE INDEX idx_emp_nonprefixed ON emp (salary) LOCAL;
Global Index:-
Global indexes can be range partitioned using a different partition scheme from the underlying table.
i.e. on a table (containing two columns id and start_date) that is hash partitioned by id, the global index on start_date can be range partitioned.
There are two types of global indexes.They are,
1. Global non-paritioned indexes (similar to the normal non-partitioned indexes)
2. Global partitioned indexes.
Now employees table creation and let's create Index
department_id column is partitioned
SQL> CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE USERS,
PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE USERS,
PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE USERS);
Table created.
SQL>
SQL>
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------- ----------
EMPLOYEES_PART1
EMPLOYEES_PART2
EMPLOYEES_PART3
SQL>
SQL> declare
v_no number :=1;
begin
delete employees;
for i in 1..10 loop
insert into employees values(v_no,'name...',v_no);
v_no := v_no+1;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES',cascade=>true);
PL/SQL procedure successfully completed.
SQL>
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------- ----------
EMPLOYEES_PART1 9
EMPLOYEES_PART2 1
EMPLOYEES_PART3 0Let's create Index on employee_id column since column is not partition.
It means that for employees table and it's column employee_id ,
CREATE INDEX employees_global_idx ON employees(employee_id) GLOBAL;
col owner for a10
col TABLE_NAME for a15
col PARTITIONED for a10
SQL> select OWNER, INDEX_NAME, TABLE_NAME,STATUS, PARTITIONED from dba_indexes where owner='SAM' and INDEX_NAME='EMPLOYEES_GLOBAL_IDX';
OWNER INDEX_NAME TABLE_NAME STATUS PARTITIONE
---------- -------------------- --------------- -------- ----------
SAM EMPLOYEES_GLOBAL_IDX EMPLOYEES VALID NO
Let's check Global index with Index partation
select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';
SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';
no rows selected
No rows selected means,its a Global Non-partitioned index.
SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';
INDEX_NAME INDEX_TYPE GLO
-------------------- --------------------------- ---
EMPLOYEES_GLOBAL_IDX NORMAL YESNote:- Now dropping the partation and will check the impact on (EMPLOYEES_GLOBAL_IDX) index
-----
SQL> alter table EMPLOYEES drop PARTITION EMPLOYEES_PART3;
Table altered.
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------- ----------
EMPLOYEES_PART1 9
EMPLOYEES_PART2 1
SQL> select OWNER, INDEX_NAME, TABLE_NAME,STATUS, PARTITIONED from dba_indexes where owner='SAM' and INDEX_NAME='EMPLOYEES_GLOBAL_IDX';
OWNER INDEX_NAME TABLE_NAME STATUS PARTITIONE
---------- -------------------- --------------- -------- ----------
SAM EMPLOYEES_GLOBAL_IDX EMPLOYEES VALID NO
SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';
INDEX_NAME INDEX_TYPE GLO
-------------------- --------------------------- ---
EMPLOYEES_GLOBAL_IDX NORMAL YES
Note:- No impact on the Global Index
Now:-
Lets drop this index and create a partioned global index
SQL> drop index EMPLOYEES_GLOBAL_IDX;
Index dropped.
Here is an example for a range-based globally partitioned index,
SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(3),
PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN(11),
PARTITION p4 VALUES LESS THAN(20),
PARTITION p5 VALUES LESS THAN(MAXVALUE));
SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
2 3 (PARTITION p1 VALUES LESS THAN(3),
4 PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN(11),
PARTITION p4 VALUES LESS THAN(20),
PARTITION p5 VALUES LESS THAN(MAXVALUE));
5 6 7
Index created.
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------- ----------
EMPLOYEES_PART1 9
EMPLOYEES_PART2 1
EMPLOYEES_PART3
EMPLOYEES_PART4
SQL>
SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME INDEX_TYPE GLO
------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_PART_IDX NORMAL NO
SQL>
SQL> select locality from dba_part_indexes where index_name='EMPLOYEES_GLOBAL_PART_IDX';
LOCALITY
--------------------
GLOBAL
SQL>
SQL> select INDEX_NAME,PARTITION_NAME from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME
------------------------- ---------------
EMPLOYEES_GLOBAL_PART_IDX P1
EMPLOYEES_GLOBAL_PART_IDX P2
EMPLOYEES_GLOBAL_PART_IDX P3
EMPLOYEES_GLOBAL_PART_IDX P4
EMPLOYEES_GLOBAL_PART_IDX P5
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES',cascade=>true);
PL/SQL procedure successfully completed.
SQL>
SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME INDEX_TYPE GLO
------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_PART_IDX NORMAL YES
Lets remove a empty partition :-
SQL> select count(*) from employees where employee_id between 12 and 20;
COUNT(*)
----------
0
Note:---Removing Partation from Global indexes i.e. employees_global_part_idx
SQL> ALTER index employees_global_part_idx drop partition p4;
Index altered.
Before dropping :-
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P3 USABLE
EMPLOYEES_GLOBAL_PART_IDX P4 USABLE -------------dropped
EMPLOYEES_GLOBAL_PART_IDX P5 USABLE
After dropping:-
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P3 USABLE
EMPLOYEES_GLOBAL_PART_IDX P5 USABLENow we will drop the partition P3 and see status.
When we drop this partition, it should invalidate the next highest partition. --VIMP( Keep in Mind)
Here, P5 is next highest partition.
SQL> alter index employees_global_part_idx drop partition p3;
Index altered.
SQL>
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P5 UNUSABLE ---------
Let rebuild - index paritioned - p5
-------------------------------------
SQL> alter index employees_global_part_idx rebuild partition p5;
Index altered.
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P5 USABLE
VIMP (Keep this in mind) :- If you drop a partition from the base table all index partition become invalidated
Below are the my Base table's partations..
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------- ----------
EMPLOYEES_PART1 9 ----------let's dropp this ..
EMPLOYEES_PART2 1
EMPLOYEES_PART3 1
EMPLOYEES_PART4 1
SQL> alter table EMPLOYEES drop partition EMPLOYEES_PART1;-------
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------- ----------
EMPLOYEES_PART2 1
EMPLOYEES_PART3 1
EMPLOYEES_PART4 1
SQL>
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX P2 UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX P5 UNUSABLE
Let's rebuild the partition
=================================
SQL> alter index employees_global_part_idx rebuild partition p1;
Index altered.
SQL> alter index employees_global_part_idx rebuild partition p2;
Index altered.
SQL> alter index employees_global_part_idx rebuild partition p5;
Index altered.
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P5 USABLEIn case you're using a global partitioned index,
you can specify the update global indexes clause to prevent the global index from being marked as unusable.
The two big advantages in specifying the update global indexes clause is that the index remains online and
available during the maintenance operation and you don't have to rebuild it after the maintenance operation.
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------- ----------
EMPLOYEES_PART2 1
EMPLOYEES_PART3 1
EMPLOYEES_PART4 1
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P5 USABLE
Let drop EMPLOYEES_PART2 with clause -- update global indexes and check the status of indexes USABLE or UNUSABLE
SQL> alter table EMPLOYEES drop partition EMPLOYEES_PART2 update global indexes;
Table altered.
partation is dropped :-
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------- ----------
EMPLOYEES_PART3 1
EMPLOYEES_PART4 1
Becasue of the Clause -- status is USABLE
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P5 USABLE
Global Index maintenance is now asynchronous
--------------------------------------------------------
Maintaining global indexes were always a expensive task in Oracle, particularly in the context of partitioned tables,
where dropping or truncating a table partition could cause a global index to become UNUSABLE/INVALID
unless specified to update the indexes during the drop/truncate operation.
However, updating the index entries during partition drop/truncate can eventually slow down the actual drop/truncate operation.
With Oracle 12c, a drop or truncate table partition (with update indexes clause) is optimized by deferring the maintenance of associated global indexes, while still leaving the indexes in VALID state.
Prior to 12c, a drop/truncate table partition (with update indexes clause) would cause a synchronous maintenance for the associated global indexes and thereby would delay the actual drop/truncate operation. However, with 12c this global index maintenance operation is performed asynchronously which optimizes the drop or truncate partition operation.
SQL> select INDEX_NAME,PARTITION_NAME,num_rows,orphaned_entries from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME NUM_ROWS ORP
--------------------------------- --------------------------------- ---------- ---
EMPLOYEES_GLOBAL_PART_IDX P5 1 YES
EMPLOYEES_GLOBAL_PART_IDX P2 0 YES
EMPLOYEES_GLOBAL_PART_IDX P1 0 YESAs we can observe, the global indexes are still in VALID/USABLE state.
No comments:
Post a Comment