MIN/MAX Quiz (Funtime) August 29, 2011

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;

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 :)

#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.

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.