Min and Max do not Mix May 27, 2009

If you want to select the min and max value for an indexed column in a table, it may not perform as well as you like, despite some nice tricks Oracle can do with min and max. I’ll show you what I mean and a little trick, which might be the fastest way to get the min and max values in Oracle.

You can use this scipt here to replicate the below. It creates a test table of 99,999 rows. It was written on oracle 10 and should work on 11 unchanged {post a comment if you try and find anything is different} and you just need to remove the “purge” part of the drop command to get it to work on 9 {I think}.

You can see that to count the 99,999 rows the CBO expects to fast full scan index T_PK for a cost of 49 {which equates roughly to the number of expected I/O operations} and the execution results in 215 buffer gets, which is the number of times a block was read from memory.

The plan shows that Oracle will use an INDEX FULL SCAN (MIN/MAX) to get the result. I find it a little misleading that it is called a “full scan” and it shows as processing 99,999 rows when in fact what Oracle does is nip down the ‘edge’ of the index to get the result. It reads the root block and one leaf block to get the result {Oracle does not need to visit the table, the data for column ID is in the index}.
If you have any doubts, look at the consistent gets – 2.
It’s fast and efficient.
It works with MIN too:-

Damn! Oracle goes back to the index fast full scan and 215 consistent gets!

Way back when I was beta testing Oracle 10 I found this and reported it as a bug. Oracle Corp replied that it was not a bug, it was expected behaviour. It would scan the whole index even though, as a human, you could see a better way. The reason given, and I have some sympathy with this, is that spotting that only min and max supported by an index had been asked for and there was a more efficient way to do this would have to be added to the CBO.
Where we differed is they thought this would be a rare event, I thought it would be a more common event and worth their effort.

Never mind, you can do it yourself {I won’t line this up like the previous examples, they need too much editing to fit!}:

— now together
select max(id) from test_1
union
select min(id) from test_1;

There are times when a human beats the computer. 4 consistent gets. I’ve played with this a little and on my test system the select min(id),max(id) version takes around 4ms. The union method takes 0-1ms.
I see a couple of oddities.

– Firstly, the plan has 3 sort operations, but the statistics show only 1 sort. I am not fully sure what is going on, it might be that the plan is a little duff, it might be that at execution time Oracle realises it really does have only 1 record to sort and so does not. {I’ll buy the person who tells me the cause of the remaining sort and how to get rid of it a pint at the next UKOUG – if you do so before I blog about it}

– Secondly, if you look at the plan, the CBO estimates a lot of effort for the sorts and thus the total cost of the statement is high relatively high, 601. I need to look into that.

I’ve used this trick of splitting “select min,max” into two statements unioned together several times over the years and I find it very useful. Below I go into a few more oddities, but I know I ramble on so feel free to stop now.

In previous postings I have looked at the fastest way to select the number of records in a table. Well, if your tables have a surrogate primary key consisting of an ascending number (generated from a sequence and a very common occurrence) and you know there are few or no skipped values, you can use the above trick to get the MIN(ID) and MAX(ID) and thus the difference is the number of rows. You can do it in one statement, as shown below:

With this version the CBO recognises that the statement cost is low (look, cost 4), the odd sorts are still there in the plan on a single row but the statement takes 4 consistent gets.

I used to think, wrongly, that Oracle would only use the INDEX FULL SCAN (MIN/MAX) for getting the absolute minimum and maximum values. I don’t know why I thought this but check out the next two examples:

As you can see, Oracle knows it can get the Max (and min) value using the INDEX RANGE SCAN MIN/MAX efficiently, even for the second case where I am asking for the max value ABOVE a stated point.
I half expected oracle to do a range scan from the value I gave it. But it has the sense to know that, logically, the maximum value below X can be found by looking for X and then it is the record before that (or where it would be if it does not exist). The maximum above X is the maximum. In restrospect, I was asking a daft question there :-)

Neatly explained, beautifully illustrated….to be honest, i just discovered this blog an hour back and i ve already become a fan…good work Martin..keep it up…and thanks to Coskan for having had a link to this lovely blog….

The remaining sort is because you said “UNION” (give distinct rows) not “UNION ALL” (give all rows) That is the SORT UNIQUE. The “SORT AGGREGATE” operations are short-circuited by the INDEX FULL SCAN (MIN/MAX).

I was just examining the same problem, and came across your post. I guess we come to the same conclusions :-)
However I would like to add, that you go back from “INDEX FULL SCAN (MIN/MAX)” to a “INDEX FAST FULL SCAN”. If you loose the “not null” on column ID you go back to a FTS. This can of course have a tremendous impact on performance.

[…] was just reading Martin Widlake’s Blog https://mwidlake.wordpress.com/2009/05/27/min-and-max-do-not-mix/ and thought to give it a try on my development environment to have a further look into the […]