jump to navigation

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

About these ads

Comments»

1. Flado - August 29, 2011

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

2. Tony Sleight - August 29, 2011

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.

3. oramoss - August 29, 2011

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

4. mwidlake - August 30, 2011

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.

5. Gary Myers (@syd_oracle) - August 30, 2011

Is the session NLS_SORT the same as the one for the index (database default) ? [Yes, I'm being difficult!]

Richard Foote - August 31, 2011

Hi Gary

Yes, you are being way too difficult :)

6. AliD - August 31, 2011

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

7. Richard Foote - August 31, 2011

Thanks for all the comments guys and well done !!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,861 other followers

%d bloggers like this: