Thanks for the question, Rajvir.
Asked: February 25, 2021 - 11:10 am UTC
Last updated: March 01, 2021 - 3:34 pm UTC
Version: Oracle 11G
Viewed 1000+ times
You Asked
Hi Tom,
We have a table which is having 18.2 crore data with 420 columns.
We have created 2 index on 2 different columns.
N_CUST_SKEY - Index and properly analyzed
COD_CUST_ID - Index and properly analyzed
Query 1:
select * from DIM_KYC where N_CUST_SKEY=14879358670;
Explain plan for Query 1
Plan hash value: 1160736793
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7110 | 4 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| DIM_KYC | 1 | 7110 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_DIM_KYC_N_CUST_SKEY | 1 | | 3 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N_CUST_SKEY"=14879358670)
Note
-----
- dynamic sampling used for this statement (level=2)Query 2:
select * from DIM_KYC where COD_CUST_ID=250781885;
Explain plan for Query 2
Plan hash value: 2375061313
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 108K| 737M| 105K (1)| 00:31:45 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| DIM_KYC | 108K| 737M| 105K (1)| 00:31:45 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_COD_CUST_ID_DIM_KYC | 108K| | 80 (0)| 00:00:02 | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COD_CUST_ID"=250781885)
Note
-----
- dynamic sampling used for this statement (level=2)Both the columns which are having index are unique but still we are getting two different cost on the same table.
Any Suggestion why is this so?
and Chris said...
If the optimizer chooses a unique index, you should see INDEX UNIQUE SCAN in the plan.
But these plans have use INDEX RANGE SCANs. So either:
- It's a multi-column unique index and you're only using the first column
- The unique constraint is supported by a non-unique index
- There is no unique index on these columns!
Either way, the cost difference is because the first query expects to find one row, the second 108,000 rows!
You can see this by looking at the "Rows" column. So the question is:
Are those estimates accurate?
You can verify this by getting the execution plan for these queries and comparing the E(stimated)-rows and A(ctual)-rows columns. Do this by:
set serveroutput off
alter session set statistics_level = all;
select ...
select *
from table ( dbms_xplan.display_cursor ( format => 'ALLSTATS LAST' ) );
https://blogs.oracle.com/sql/how-to-create-an-execution-plan