After massive deletion on a partition, index performance degraded severely.
No execution plan changed, but consistent gets increased much high.
With many tests I found that this occur when unique index is scanned by INDEX_DESC hint.
Below I simulated this interesting phenomenon.
/*------------------------------------------------------------------*/
/* Test of performance overhead of Unique index + INDEX_DESC hint */
/*------------------------------------------------------------------*/
-------------------------
-- Create Test Table
-------------------------
drop table jjj;
create table jjj (c1 number not null, c2 varchar2(10) not null) ;
--create unique index
create unique index jjj_n1 on jjj (c1);
--populate 100000 rows
insert into jjj
select level, 'aaaaaaaa' from dual connect by level <= 100000;
commit;
exec dbms_stats.gather_table_stats(user, 'jjj')
select index_name, blevel, leaf_blocks from user_indexes where index_name = 'JJJ_N1';
INDEX_NAME BLEVEL LEAF_BLOCKS
------------------------- ---------- -----------
JJJ_N1 1 187
------------------------------------
--Delete massive rows
------------------------------------
-- delete all rows except 2 rows
delete jjj where c1 between 3 and 100000;
commit;
set autotrace on
select min(c1), max(c1) from jjj;
MIN(C1) MAX(C1)
---------- ----------
1 2
-------------------------------
--Execute the query
-------------------------------
select /*+index_desc(jjj jjj_n1) */ * from jjj
where c1 = 1
;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | JJJ | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| JJJ_N1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=1)
filter("C1"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
498 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
=> 3 consistent gets
=> It's anticipated result
------------
--From now search the values that have no upper values
--search the value placed on most left position of the leaf chain
select /*+index_desc(jjj jjj_n1) */ * from jjj
where c1 = 2
;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | JJJ | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| JJJ_N1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=2)
filter("C1"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
376 consistent gets
0 physical reads
0 redo size
498 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
=> 376 consistent gets
=> about 2 times of the leaf blocks
--search the value placed on about middle position of the leaf chain
select /*+index_desc(jjj jjj_n1) */ * from jjj
where c1 = 50000
;
=> 191 consistent gets
=> about same number of the leaf blocks
--search the value placed on most right position of the leaf chain
select /*+index_desc(jjj jjj_n1) */ * from jjj
where c1 = 99999
;
=> 2 consistent gets
=> there is no overhead
-------------
--Now insert the upper values in some position
--insert the middle position value of the leaf chain
insert into jjj values ( 50000, 'aaaaaaaa');
commit;
select /*+index_desc(jjj jjj_n1) */ * from jjj
where c1 = 2
;
=> 188 consistent gets
=> about same number of the leaf blocks
--insert the left position value of the leaf chain
insert into jjj values ( 100, 'aaaaaaaa');
commit;
select /*+index_desc(jjj jjj_n1) */ * from jjj
where c1 = 2
;
=> 4 consistent gets
=> there is 1 get overhead
==> Conclusion with assumption
1. With many tests I could confirm that this phenomenon is only occur when the index is unique index and the hint INDEX_DESC is used. With non-unique index this phenomenon is never occur.
2. This phenomenon occur in DBMS version of 10g, 11g and 12c also.(in 9i not tested)
3. This is also occur in partitioned unique index.
4. I think this overhead is result of the extra scaning through index leaf nodes to determine the start point of scan.
When case of 'where c1=2', oracle search the higher value than '2', scanning the leaf blocks from the very block of the value '2' until it find the upper value.
If the upper value is not found then this scan will end with the last leaf block(most right block), and return to its start block of this scan by another navigating from right to left, so the number of consistent gets is about double of the number of leaf blocks.
But I cannot address why this extra scan is needed. Because this is not necessary in non-unique index.
5. The workaround is converting unique index to non-unique index. Rebuilding index is best solution of course.
create index jjj_n1 on jjj (c1) ;
alter table jjj add constraint jjj_pk primary key (c1);
