A recent Twitter thread had an interesting test case of the optimizer choosing sub-optimal skip scan over range scan. I've checked what's going on, and it happens to be a known bug. Here's a slightly simplified test case: [sourcecode lang="sql" gutter="false" collapse="true"] drop table as_big_lookup_table cascade constraints purge; create table as_big_lookup_table as select rownum pk_col, mod(rownum,30) type_col, mod(rownum,5000) join_col, rpad('x',30,'x') padding_col from dual connect by rownum <=10000 / create index as_big_lookup_table_good on as_big_lookup_table (type_col, join_col); create index as_big_lookup_table_bad on as_big_lookup_table (pk_col, type_col, join_col); explain plan for select pk_col, join_col from as_big_lookup_table where type_col = 10 and join_col = 10; select * from table(dbms_xplan.display(format=>'basic +rows +cost +predicate')); [/sourcecode] We have a table with two similar indexes. One is a perfect match for the table access by (type_col, join_col) columns, and another is quite bad as the leading column is (essentially) primary key. But when we query the table by those two columns, for some reason Optimizer chooses to skip scan the bad index. What is going on? Let's compare the three plans:
- Bad plan in 18c
- Good plan in 18c - forced with an index_rs_asc hint
- Bad plan in 12.1.0.2 - forced with an index_ss hint
Share this
Previous story
← Oracle autonomous transaction processing - a good start
You May Also Like
These Related Stories
Oracle Database 18c schema only accounts
Oracle Database 18c schema only accounts
Mar 16, 2018
5
min read
Shutdown while upgrading to 18c
Shutdown while upgrading to 18c
Sep 12, 2018
5
min read
How to Upgrade Grid Infrastructure to 18c
How to Upgrade Grid Infrastructure to 18c
Jul 10, 2019
11
min read
No Comments Yet
Let us know what you think