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
------------- ------------------------------ ----------
CRM_ETL       GTT_RES_DLY
CRM_ETL       GTT_RES_PRDCT_CT
CRM_ETL       GTT_RES_PRDCT_RATE_CT
CRM_ETL       GTT_RRSD_DRVR
CRM_ETL       GTT_SUS_RES
SYS           L$1
SYS           L$2
SYS           WRI$_ADV_OBJSPACE_TREND_DATA
SYS           WRI$_ADV_OBJSPACE_CHROW_DATA
SQLTXPLAIN    SQLG$_TAB_SUBPART_COLUMNS
SQLTXPLAIN    SQLG$_DBA_SUBPART_HISTOGRAMS
SQLTXPLAIN    SQLG$_WARNING

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