Oracle Tip – ORDER BY with NULLS LAST or NULLS FIRST

The purpose of this tip is to introduce you to an seldom-used by very useful clause within the ORDER BY of a SQL statement – NULLS FIRST or NULLS LAST.

Here is a situation that I ran into:
I was looking into the Oracle 11g system view v$sql_plan_monitor to find out the top execution plan steps that took the longest time to get an idea of the corresponding queries.

So, I did this:

SELECT a.plan_time, a.*
FROM v$sql_plan_monitor a
ORDER BY a.plan_time DESC

..and here are the first few rows that I got

**only a few rows and few columns are shown since the intent of this tip is to explain NULLS FIRST/LAST and not v$sql_plan_monitor)

PLAN_TIME KEY STATUS FIRST_REFRESH_TIME LAST_REFRESH_TIME FIRST_CHANGE_TIME LAST_CHANGE_TIME
9.96E+11 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58
9.96E+11 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58
1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58
1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58
1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58
1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58
1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58
1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58
1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58
1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58 1/24/2013 1:58

I was expecting the plan_time to be a huge number in the first rows since I had specifically said “ORDER BY a.plan_time DESC“

What happened here?

NULL’s got in the way. The rows with NULL values in PLAN_TIME were displayed first before it got to actually displaying what I wanted. When it did get to the rows with a non-NULL value in PLAN_TIME, the rows were ordered correctly

PLAN_TIME KEY STATUS FIRST_REFRESH_TIME LAST_REFRESH_TIME FIRST_CHANGE_TIME LAST_CHANGE_TIME
223 1.63E+11 DONE (FIRST N ROWS) 1/24/2013 9:19 1/24/2013 9:19
223 1.63E+11 DONE (FIRST N ROWS) 1/24/2013 9:19 1/24/2013 9:19
223 1.63E+11 DONE (FIRST N ROWS) 1/24/2013 9:19 1/24/2013 9:19
5 1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58
5 9.92E+11 DONE 1/24/2013 1:58 1/24/2013 1:58
5 9.92E+11 DONE 1/24/2013 1:58 1/24/2013 1:58
5 1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58
5 1.01E+12 DONE (ALL ROWS) 1/24/2013 1:58 1/24/2013 1:58
3 7.17E+11 DONE (ALL ROWS) 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55
3 7.17E+11 DONE (ALL ROWS) 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55
3 7.17E+11 DONE (ALL ROWS) 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55
3 7.17E+11 DONE (ALL ROWS) 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55
2 7.17E+11 DONE (ALL ROWS) 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55 1/24/2013 8:55

To get around this problem, you could also use the NULLS LAST. So to get the same 13 rows above with a non-NULL value in the PLAN_TIME columns, I could do this:

SELECT a.plan_time, a.*
FROM
(
    SELECT *
    FROM v$sql_plan_monitor
    ORDER BY plan_time DESC NULLS LAST
) a
WHERE rownum < 13

Similarly, depending on your need you could use NULLS FIRST with either the ASC or DESC clause in ORDER BY.

Why is this a big deal when I could have added a “WHERE plan_time IS NOT NULL” to the statement?

Besides the fact that we would not have a case for this database post :-), I can show a situation where it can help. Say, you wanted to do some kind of ranking for all the rows and that includes rows with NULL values in plan_time, you would have had to do some kind of a DECODE statement with NVL or something similar. A NULLS LAST or NULLS FIRST is more elegant in such a scenario.

Advertisements

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