Let’s do a simple trick. You want to get min and max value of a column which is already indexed. As you know if you have an btree index on a column and try to get a Min or Max value then you will see an “Index Full Scan(Min/Max)” line in the execution plan. since indexes store data in order, it is enough to look for the first or last index leaf to get min or max value.
as you see, we are able to see INDEX FULL SCAN (MIN/MAX) step and our query cost is 2. same thing happens if I replace Max with a Min function.
what if we ask for both, Min and Max ?
suddenly, Oracle stops using index and change index search with a full table scan. Why? despite of seeing very high cost (cost is 394, it was 2 when it used index) maybe this is because table is so small and Oracle decided to use a full table instead of index scan? let’s check:
now we have 1.1 million rows in the table and it is not that small now. I’ve already rebuild the index and gather the statistics so let’s try again:
as we can see our table is not that small and even so Oracle uses a full table scan with cost 6367. even in that big table index scan cost does not change much:
just index scan cost is 3. so instead of full table scan, you can select min first and then run max as different 2 queries and your total cost is almost 6. this is much much less than Oracle’s default execution plan. Even using a HINT won’t work because Oracle is not able to do that with both min and max aggregate functions. I didn’t go deeper but as I understand Oracle can use just one directional index search (asc or desc) not both so instead of using an index it decides full table scan operation.
You might think that maybe this is because column contains null values and not marked as a not null and somehow this is effecting the optimizer? yes and no. let’s delete null values and modify column not null:
huh! now it changed and used the index with a cost 707. this is much better than full table scan (6367 cost) but if you pay attention, you will see that this is not “Index Full Scan (Min/Max)“, instead this is “Index Fast Full Scan”. in a Index Full Scan (min/max) Oracle just read first or last leaf almost nothing more and this is more than enough to get what we need but in that case Oracle has read entire index (allleaves) so this cost as a lot! as I said running min and max in a 2 different query will cost us 6 (each of them is 3).
so how can we workaround?
as you see we are so close to total cost of 6. Optimizer does a lot for us but sometimes we need to help.
Have healthy days, please stay home if you can. stay safe.