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
How to Make Oracle Use the Correct Index
How to Make Oracle Use the Correct Index
Jan 25, 2021
19
min read
There's Always Another Bug Hiding Just Around the Corner
There's Always Another Bug Hiding Just Around the Corner
Aug 14, 2013
6
min read
Oracle: Is OUTER JOIN Better Than NOT EXISTS?
Oracle: Is OUTER JOIN Better Than NOT EXISTS?
Jan 7, 2008
2
min read
No Comments Yet
Let us know what you think