Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How does the CBO choose an index when the COST is the same for 2 indexes?

Ahmed AANGOURFeb 26 2014 — edited Mar 2 2014

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

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?

mariam.kupa

Hello,

I have seen the same problem when using fast refresh materialized view that uses mview log also.

Do an explicit select privilege for the mlog:

grant select on mlog$_<master_table> to <mview_schema>

https://deeparaja.wordpress.com/2012/07/17/materialized-view-fast-refresh-fails-with-ora-12018-ora-00942/

1 person found this helpful

Martin Preiss

thank you for the answer and the link: but in my case the owner of table and mview is the same and the ora-942 is not related to the accessed table, but to an internal object and furthermore a transient object, that cannot be found in the dictionary at all (so the ora-942 makes sense - but not the idea to gather statistics for this object at all).

BobLilly

Hi Martin,

Have you tried the OPT_PARAM('optimizer_dynamic_sampling', '0') hint?

Regards,

Bob

1 person found this helpful

AndrewSayer

Answer

I can replicate on my 12.2 instance, it looks like there's a problem with the lateral view existing in the execution plan. I can't see a no lateral view hint, and I couldn't see anything in the plan outline that looks relevant but you can hint to use an older optimizer environment, 11.1.0.7 did the trick for me (although it was the first I chose).

create materialized view t_mv

select /*+OPTIMIZER_FEATURES_ENABLE('11.1.0.7')*/t.col1, t.col2, r.val

from t,

LATERAL (SELECT MIN(val) AS val

FROM JSON_TABLE ( t.col2, '$[*].target'

COLUMNS (val NUMBER PATH '$')

)

) r;

exec dbms_mview.refresh('t_mv','c')

PL/SQL procedure successfully completed.

I suggest you raise an SR as this could effect a lot of other things, and should be relatively painless as you've got an easily reproducible test case

Marked as Answer by Martin Preiss · Sep 27 2020

3 people found this helpful

Dom Brooks

Would be interesting to know what fix is responsible

https://blog.dbi-services.com/ofe-optimizer-features-enable/

https://blog.tanelpoder.com/posts/scripts-for-drilling-down-into-unknown-optimizer-changes/

1 person found this helpful

Donatello Settembrino

trying on a 12.2 with SYS user (disabling query rewrite) it seems to work correctly without modifying OFE

SQL> select user from dual;

USER

------

SYS

SQL> create materialized view t_mv

2 disable query rewrite

3 as

4 select t.col1, t.col2, r.val

5 from t,

6 LATERAL (SELECT MIN(val) AS val

7 FROM JSON_TABLE ( t.col2, '$[*].target'

8 COLUMNS (val NUMBER PATH '$')

9 )

10 ) r;

Creata vista materializzata.

SQL> exec dbms_mview.refresh('T_MV');

Procedura PL/SQL completata correttamente.

1 person found this helpful

Mustafa KALAYCI

Hi Martin,

I have the same result but if you change the query as a subquery it works. of course mv does not support scalar subqueries so I created a view to achive that:

create or replace view v_x as 
select t.col1, t.col2, (SELECT min(val) AS val
                         FROM JSON_TABLE ( t.col2, '$[*].target'
                                           COLUMNS (val NUMBER PATH '$')
                                          )
                       ) as val
from t;
drop materialized view t_mv;
create materialized view t_mv
as
select * from v_x;
exec dbms_mview.refresh('t_mv');

it works and when I checked the execution plan of the view I can see that VW_LAT*** view. I hope that gives some idea.

1 person found this helpful

Martin Preiss

Hi Andrew,

thank you for the hint: I didn't consider this option since the lateral join was a 12c addition (if I recall this right). This should work in the given context.

Regards

Martin

Martin Preiss

Hi Donatello,

SYS is special: I would not be surprised to see different strategies, when SYS is concerned.

Regards

Martin

Martin Preiss

Hi Mustafa,

thank you for adding this solution, which would also be a valid option.

Regards

Martin

1 person found this helpful

Donatello Settembrino

in fact, I did not indicate it as a solution but it seemed "strange" to me that without changing the behavior of the optimizer (OFE) it would still work under SYS user.

Regards

1 person found this helpful

Martin Preiss

strange indeed, but SYS tends to include strange effects.

1 person found this helpful

AndrewSayer

Dom Brooks wrote:

Would be interesting to know what fix is responsible

https://blog.dbi-services.com/ofe-optimizer-features-enable/

https://blog.tanelpoder.com/posts/scripts-for-drilling-down-into-unknown-optimizer-changes/

I tried the obvious parameter (with lateral in the name) but it wasn't that. I started writing some scripts a while ago to brute force these things with explain plan, but only managed to get as far as it cycling through fix controls (and can confirm there's no fix control in it). Mauro Pagano's Pathfinder (which my scripts mimic) does include hidden parameters but it has to execute the SQL each time and needs to be run as SYS (which doesn't seem to have the problem) https://mauro-pagano.com/category/pathfinder/ . Might be worth kicking off overnight and checking the results in the morning.

1 - 13

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 30 2014

Added on Feb 26 2014

#general-database-discussions

20 comments

8,345 views

Oracle Database Discussions

How does the CBO choose an index when the COST is the same for 2 indexes?

Comments

Post Details