Hello Folks,
I have a table named TRANSRPDATES with 3 indexes:
Index Name Pos# Order Column Name
------------------------------ ---------- ----- ------------------------------
p_transrpdates 1 ASC transik
2 ASC accik
r_transrpdates_accik 1 ASC accik
r_transrpdates_rpdefik 1 ASC rpdefik
I execute the following query:
DELETE FROM SCDAT.TRANSRPDATES WHERE TRANSRPDATES.TRANSIK = :v1 AND TRANSRPDATES.ACCIK = :v2;
If statistics are gathered when the table contains rows the CBO chooses the unique index. That's logical for everybody I guess.
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 40 | 3 (0)| 00:00:01 |
| 1 | DELETE | TRANSRPDATES | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TRANSRPDATES | 1 | 40 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | P_TRANSRPDATES | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TRANSRPDATES"."TRANSIK"=TO_NUMBER(:V1) AND
"TRANSRPDATES"."ACCIK"=TO_NUMBER(:V2))
What is confusing for me is that if the statistics are gathered when the table is empty (all table and index statistics = 0) the CBO chooses to use an index range scan on the non unique index:
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 92 | 2 (0)| 00:00:01 |
| 1 | DELETE | TRANSRPDATES | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TRANSRPDATES | 1 | 92 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | R_TRANSRPDATES_ACCIK | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TRANSRPDATES"."TRANSIK"=TO_NUMBER(:V1))
3 - access("TRANSRPDATES"."ACCIK"=TO_NUMBER(:V2))
To have a better understanding on this I decided to generate a 10053 trace file when the CBO chooses the non-unique index.
here is an extract:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TRANSRPDATES Alias: TRANSRPDATES
#Rows: 0 #Blks: 33172 AvgRowLen: 0.00 ChainCnt: 0.00
Index Stats::
Index: P_TRANSRPDATES Col#: 1 2
LVLS: 2 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
Index: R_TRANSRPDATES_ACCIK Col#: 2
LVLS: 2 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
Index: R_TRANSRPDATES_RPDEFIK Col#: 6
LVLS: 2 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
***************************************
1-ROW TABLES: TRANSRPDATES[TRANSRPDATES]#0
Access path analysis for TRANSRPDATES
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TRANSRPDATES[TRANSRPDATES]
Column (#1): TRANSIK(
AvgLen: 22 NDV: 0 Nulls: 0 Density: 0.000000 Min: 0 Max: 0
Column (#2): ACCIK(
AvgLen: 22 NDV: 0 Nulls: 0 Density: 0.000000 Min: 0 Max: 0
ColGroup (#1, Index) P_TRANSRPDATES
Col#: 1 2 CorStregth: 0.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 1.0000
Table: TRANSRPDATES Alias: TRANSRPDATES
Card: Original: 0.000000 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
Access Path: TableScan
Cost: 9540.36 Resp: 9540.36 Degree: 0
Cost_io: 9479.00 Cost_cpu: 236232408
Resp_io: 9479.00 Resp_cpu: 236232408
Access Path: index (UniqueScan)
Index: P_TRANSRPDATES
resc_io: 2.00 resc_cpu: 15583
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 2.00 Resp: 2.00 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 1.0000
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 1.0000
Access Path: index (AllEqUnique)
Index: P_TRANSRPDATES
resc_io: 2.00 resc_cpu: 15583
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 2.00 Resp: 2.00 Degree: 1
Access Path: index (AllEqRange)
Index: R_TRANSRPDATES_ACCIK
resc_io: 2.00 resc_cpu: 14443
ix_sel: 0.010000 ix_sel_with_filters: 0.010000
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: R_TRANSRPDATES_ACCIK
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.00 Bytes: 0
We can notice that the cost for the 2 indexes is 2.
My guess is that the CBO chooses to use the index which is potentially the smallest, that is to say the one with few index keys.
Index R_TRANSRPDATES_ACCIK has only one key whereas index P_TRANSRPDATES has 2 keys.
The alphabetic order of the index name does not matter here since the R_TRANSRPDATES_ACCIK is alphabeticaly after the unique index P_TRANSRPDATES.
Does someone have an idea on this?