I would like to blog about one particular case when that feature can cause performance problems.
Let's create some test data:
SQL> create table t
2 partition by range(owner)
3 (
4 partition values less than ('SYS'),
5 partition values less than (maxvalue)
6 )
7 as
8 select *
9 from dba_objects;
SQL>
SQL> create index t_name_i on t(object_name);
Consider the following query:
SQL> explain plan for 2 select max(object_name) 3 from t; SQL> select * 2 from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 2886567490 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 25 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| T_NAME_I | 1 | 25 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------It's quite simple and the queries like that are common in my environment. For instance, they are used to monitor application activities: "select max(date_column) from some_table" and the stuff alike.
Now I'm going to drop a partition of that table:
SQL> alter table t drop partition for ('X') update indexes;
The index is marked as having orphaned entries after that:
SQL> select index_name, orphaned_entries 2 from ind 3 where table_name='T'; INDEX_NAME ORPHANED_ ------------------------------ --------- T_NAME_I YESCausing some important changes in the plan:
SQL> explain plan for
2 select max(object_name)
3 from t;
SQL> select *
2 from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 3249307143
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 148 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | INDEX FAST FULL SCAN| T_NAME_I | 96566 | 2357K| 148 (1)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TBL$OR$IDX$PART$NUM("T",0,8,0,"T".ROWID)=1)
The filter clearly was occured due to orphaned index entries which is confirmed by looking into the CBO traces (event 10053):
Added Filter for Orphaned Entries of Index T_NAME_I:
TBL$OR$IDX$PART$NUM("T",0,8,0,"T".ROWID)=1
The IFFS (INDEX FAST FULL SCAN) would cause a huge amount of I/O being executed against large indexes.For instance, some queries started scanning multi-gigabyte indexes in my database after I had dropped a single small partition while executing periodic maintenance tasks.
Of course, in such case Global Index Maintenance can't be delayed and an index synchronization procedure must be executed ASAP:
SQL> alter index t_name_i coalesce cleanup; SQL> select index_name, orphaned_entries 2 from ind 3 where table_name='T'; INDEX_NAME ORPHANED_ ------------------------------ --------- T_NAME_I NO SQL> explain plan for 2 select max(object_name) 3 from t; SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 2886567490 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 25 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| T_NAME_I | 1 | 25 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
