Monday, June 20, 2016

The RANK function for DBAs

The analytic function RANK is perhaps not the most commonly used by DBAs.
For what it's worth, here is a short demonstration:

1. Find the number of rows for all the tables in a schema:
select table_name, num_rows 
from dba_tables 
where owner='SYSTEM' 
and num_rows >0 
ORDER BY NUM_ROWS;

The above simple query results in:
TABLE_NAME                      NUM_ROWS
------------------------------ ---------
REDO_DB                                1
REPCAT$_TEMPLATE_TYPES                 2
REPCAT$_AUDIT_ATTRIBUTE                2
REPCAT$_TEMPLATE_STATUS                3
AQ$_INTERNET_AGENT_PRIVS               4
AQ$_INTERNET_AGENTS                    6
AQ$_QUEUE_TABLES                      15
REPCAT$_RESOLUTION_METHOD             19
AQ$_QUEUES                            28
REPCAT$_OBJECT_TYPES                  28
MVIEW$_ADV_PARAMETERS                 40
LOGSTDBY$SKIP_SUPPORT                351
HELP                                 938

13 rows selected.

To check where in the sort order a table with, say, 10 rows, would be, use the RANK function:
select RANK(10) WITHIN GROUP(order by num_rows)"Rank" 
from dba_tables 
where owner='SYSTEM' 
and num_rows >0;

      Rank
----------
         7

A table with 10 rows would be on 7th place, in the sort order shown above (ascending by default), squeezed in between the tables AQ$_INTERNET_AGENTS and AQ$_QUEUE_TABLES.

No comments:

Post a Comment