12.2
I've got a fairly big query that a user s having trouble with. One of the tables in the joins is partitioned and user wants to query all the data for november in this table.
Table is range partitioned and has a number of indexes on it.
If we use between, oracle takes 1 plan
AND t.date_field between '01-Nov-2020' and '30-Nov-2020'
If we use all the date fields in an IN list it chooses another which runs far quicker (10 minutes down to 2 seconds but same result)
AND t.date_field in ( '01-Nov-2020', '02-Nov-2020'......'30-Nov-2020')
I can limit it down to just 2 dates on a single table query on the partitioned table and see plan changing
just by doing
select count(*) from part_Table
where date_Field IN ( '01-Nov-2020', '02-Nov-2020')
which has different plan then
select count(*) from part_Table
where date_Field BETWEEN '01-Nov-2020' and '02-Nov-2020'
both give same result but use different indexes.
So ok Oracle looks at the 2 operators differently, I cant find a note to say what the difference actually is and how we can help it pick a good plan other than giving it a hint, anyone know?