I have the problem, that I could describe in several steps:
1. Insert 160K into table t_reg_account. Here I use simple insert statement without any hints. I_ACC_PK index on participant_code,
account_type, account_value, account_sequence.
2. Execute select:
SELECT * FROM
( SELECT ra.* FROM t_reg_account ra
WHERE ra.participant_code = 'PA1'
AND ra.account_type = 'P1'
AND ra.account_value = '111558888888'
ORDER BY ra.account_sequence desc )
WHERE rownum = 1;
Please note, that subselect returns all 160K row.
SELECT ra.* FROM t_reg_account ra
WHERE ra.participant_code = 'PA1'
AND ra.account_type = 'P1'
AND ra.account_value = '111558888888'
ORDER BY ra.account_sequence desc
It is executed pretty fast(several consistent gets, from execution plan below)
3. Remove all rows from table t_reg_account using DELETE statement.
4. Execute select from 2. Now the same query consumes several hundreds of consistent gets on empty table.
I understood that after "delete all statement", I_ACC_PK index is into "inappropriate state" and I found that index rebuild
fixes the problem(I made index analysis, after rebuild it has the same values as at the beginning). However I don't understand
why did this happen. Could you, please, explain me what is wrong with the index and how to fix it.
All performed steps in details are below.
====================================================================================================================
159299 rows are in t_reg_account
SQL> SELECT * FROM
2 ( SELECT ra.* FROM t_reg_account ra
3 WHERE ra.participant_code = 'PA1'
4 AND ra.account_type = 'P1'
5 AND ra.account_value = '111558888888'
6 ORDER BY ra.account_sequence desc )
7 WHERE rownum = 1;
1 row is selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID 82wrsqhu9n7yw, child number 2
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1
Plan hash value: 1606291734
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 2 | 1134 | 3 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 12806 | 1813K| 3 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 2 | | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------
SQL> select * from table(dbms_xplan.display_cursor('82wrsqhu9n7yw', null, 'ALLSTATS LAST'));
SQL_ID 82wrsqhu9n7yw, child number 2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1
Plan hash value: 1606291734
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 |
| 2 | VIEW | | 1 | 2 | 1 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 1 | 12806 | 1 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 1 | 2 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
4 - access("RA"."PARTICIPANT_CODE"='PA1' AND "RA"."ACCOUNT_TYPE"='P1' AND
"RA"."ACCOUNT_VALUE"='111558888888')
91 rows selected.
SQL> analyze index I_ACC_PK validate structure;
Index analyzed.
SQL> select * from index_stats;
HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ----------
3 1024 I_ACC_PK 159299 899 7156750 8000
BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE
---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ----------
898 6 33161 8032 0 0 159299 1 7240192 7189911
PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------ ------------ -------------------- ---------- ------------ -------------- ----------------
100 1 4 0 0 3 57
DELETE FROM T_REG_ACCOUNT;
0 rows are in t_reg_account
SQL> SELECT * FROM
2 ( SELECT ra.* FROM t_reg_account ra
3 WHERE ra.participant_code = 'PA1'
4 AND ra.account_type = 'P1'
5 AND ra.account_value = '111558888888'
6 ORDER BY ra.account_sequence desc )
7 WHERE rownum = 1;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID 82wrsqhu9n7yw, child number 3
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1
Plan hash value: 1606291734
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 74 (100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1 | 567 | 74 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 1 | 145 | 74 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 1 | | 73 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
1 - filter(ROWNUM=1)
4 - access("RA"."PARTICIPANT_CODE"='PA1' AND "RA"."ACCOUNT_TYPE"='P1' AND
"RA"."ACCOUNT_VALUE"='111558888888')
Note
-----
- cardinality feedback used for this statement
30 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('82wrsqhu9n7yw', null, 'ALLSTATS LAST'));
SQL_ID 82wrsqhu9n7yw, child number 2
-------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1
Plan hash value: 1606291734
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 4 |
| 2 | VIEW | | 1 | 2 | 1 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 1 | 12806 | 1 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 1 | 2 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
4 - access("RA"."PARTICIPANT_CODE"='PA1' AND "RA"."ACCOUNT_TYPE"='P1' AND
"RA"."ACCOUNT_VALUE"='111558888888')
SQL_ID 82wrsqhu9n7yw, child number 3
-------------------------------------
SELECT * FROM ( SELECT ra.* FROM t_reg_account ra WHERE
ra.participant_code = 'PA1' AND ra.account_type = 'P1'
AND ra.account_value = '111558888888' ORDER BY
ra.account_sequence desc ) WHERE rownum = 1
Plan hash value: 1606291734
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 901 |
|* 1 | COUNT STOPKEY | | 1 | | 0 |00:00:00.01 | 901 |
| 2 | VIEW | | 1 | 1 | 0 |00:00:00.01 | 901 |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_REG_ACCOUNT | 1 | 1 | 0 |00:00:00.01 | 901 |
|* 4 | INDEX RANGE SCAN DESCENDING | I_ACC_PK | 1 | 1 | 0 |00:00:00.01 | 901 |
----------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
4 - access("RA"."PARTICIPANT_CODE"='PA1' AND "RA"."ACCOUNT_TYPE"='P1' AND
"RA"."ACCOUNT_VALUE"='111558888888')
Note
-----
- cardinality feedback used for this statement
121 rows selected.
SQL> analyze index I_ACC_PK validate structure;
Index analyzed.
SQL> select * from index_stats;
HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ----------
3 1024 I_ACC_PK 35621 899 1600589 8000
BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE
---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ----------
898 6 33161 8032 35621 1600589 35621 1 7240192 1633750
PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ------------ -------------------- ---------- ------------ -------------- ----------------
23 1 4 0 0 3 56