Sunday, April 24, 2011

Nulls in Ordering

You want to find out the tables with the highest number of rows in a database. Pretty simple, right? You whip up the following query:

select owner, table_name, num_rows
from dba_tables
order by num_rows;

And, here is the output:

OWNER         TABLE_NAME                       NUM_ROWS
------------- ------------------------------ ----------
SYS           L$1
SYS           L$2
... output truncated ...

Whoa! The NUM_ROWS columns comes up with blanks. Actually they are nulls. Why are they coming up first? This is due to the fact that these tables have not been analyzed. CRM_ETL seems like an ETL user. The tables with GTT_ in their names seem to be global temporary table, hence there are no statistics. The others belong to SYS and SQLTXPLAIN, which are Oracle default users and probably never analyzed. Nulls are not comparable to actual literals; so they are neither less or greater than the others. By default the nulls come up first when asking for a ordered list. 

You need to find the tables with the highest number of rows fast. If you scroll down, you will see these rows; but it will take some time and it makes you impatient.You can add a new predicate something like: where num_rows is not null; but it's not really elegant. It will do the null processing. And what if you want the table names with null num_rows as well? This construct will eliminate that possibility. So, you need a different approach.

Nulls Last

If you want to fetch the nulls but push them tot he end of the list rather than first, you should add a new clause to the order by - NULLS LAST, as shown below.

select owner, table_name, num_rows
from dba_tables
order by 3 desc nulls last

Here is the output:

OWNER            TABLE_NAME       NUM_ROWS
---------------- --------------- ----------
CRM_ETL F_SALES_SUMM_01 1664092226
CRM_ETL          F_SALES_SUMM_02 948708587
CRM_ETL          F_SALES_SUMM_03 167616243
... output truncated ...

This solves the problem. The nulls will be shown; but after the last of the rows with non-null num_rows value.


Wissem said...

Thank you! Indeed really useful!

Ittichai Chammavanijakul said...

It's good to know. Thanks for sharing.


Painting workshop said...

Thanks for sharing this! I could use this! By the way Happy Easter!...Daniel

Yasir said...

Well,its amazing to know so many people not know it.
If we have a table t with indexed column t1,would the index be used if we specify

select * from t order by t1 nulls last

Unknown said...

@Yasir yes the index will be used.

Eduardo Legatti said...

Hi Arup,

I believe that you forgot to put the (DESC) ORDER BY clause in your first SELECT statement. In fact, NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC)



Jyothish said...

I think better to analyze schema first.NUM_ROWS will not change after deletion.Ater analyze schema you will get correct num_rows.

data recovery services dallas said...

This is a great sharing, I m glad to read such article. i appreciate the writer for sharing good info. Keep sharing such kind of nice info thanks