MIN/MAX Quiz (Funtime) August 29, 2011
Posted by Richard Foote in MAX, MIN, Oracle Indexes.trackback
At my work I’ve recently introduced a little “Question of the Day” for my fellow DBAs, hopefully to pass on a few interesting little titbits of information and have a bit of fun along the way.
I was going to just write a blog article on the following topic of how Oracle deals with MIN and MAX queries, but decided to give you all the chance to have a bit of a think first via a little quiz. This was a question I asked recently, see what you think:
There are many ways one could write a query to determine the MIN and MAX of a column. That said, which of the following is the odd one out:
1) select min(id), max(id) from muse;
2) select min(id) as “MIN(ID)/MAX(ID)” from muse union all select max(id) from muse;
3) select (select min(id) from muse) “MIN(ID)”, (select max(id) from muse) “MAX(ID)” from dual;
4) with min_id as (select min(id) from muse), max_id as (select max(id) from muse) select * from min_id, max_id;
It’s a “big” table, there’s an index on the ID and the ID has a not null constraint (the constraint doesn’t actually matter to the answer of the question, but it can make a subtle difference).
Now, one could come up with lots of different possible answers (eg: option 4 is the only one that uses a WITH clause), however the answer I’m after is one that might be best associated with the common theme of this blog.
You don’t have to make a comment, you can just quietly ponder which option is somewhat different or maybe consider which one you may not want to use.
No more clues 🙂
More details in a few day’s time.
PS. Folk from my work and Jonathan Lewis may not answer as they already know the answer I’m after 🙂
Richard Foote's Oracle Blog 
#1 is the one I’d avoid, as it will require an index full scan at best. All the others can do the job using just two index probes.
LikeLike
I think number 1) will cause a full table scan, as it would require a function based index to use the ID column.
The others I think will use the index on the ID column.
LikeLike
#2 returns two rows, whilst the others return one row…same information, but a subtle difference nonetheless. Not sure if that’s what you are looking for as an answer though…
Flado has a point too in terms of the execution plan differences…#1 uses a FAST FULL SCAN of the index, whilst the others use FULL SCAN (MIN/MAX) operations, i.e. 2 index probes.
Without the constraint, #1 will do a full table scan instead of the Fast Full Scan…because the (B Tree, I assume) index does not hold NULLs in it. If you add a filter of “ID IS NOT NULL” it will revert to the FAST FULL SCAN of the index.
LikeLike
Number 1 will be a fast full index scan (probably) as the CBO is not smart enough to realise that, though it can solve select (min) or select(max) by walking down one edge of the index, it is not coded to cope with the special case of combining these two to get the min and the max in one simple statement. You have to manually do it for Oracle, as is the case with the other examples you have given (and a nice set they are too – so many ways to do a simple thing with SQL).
I’ve got a blog post about it called “why min and max do not mix” or something.
LikeLike
Is the session NLS_SORT the same as the one for the index (database default) ? [Yes, I’m being difficult!]
LikeLike
Hi Gary
Yes, you are being way too difficult 🙂
LikeLike
#1 is the one to avoid due to FFIS at best. I couldn’t reason which one is best, so I ran the queries. #3 came on top. (#3 cost 2, #1 and #4 cost 6, #1 cost a lot)
Now I need to find why! More reading is necessary.. I feel I’m deranged!! (pun intended)
LikeLike
Thanks for all the comments guys and well done !!
LikeLike