Skip to Main Content
  • Questions
  • Cost for different when two different unique indexes are used on the same table.

Breadcrumb

Question and Answer

Chris Saxon

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 100+ 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 we 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

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.