MIN/MAX Quiz (Funtime) August 29, 2011Posted by Richard Foote in MAX, MIN, Oracle Indexes.
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 :)