RATIO_TO_REPORT Function For Percentage Calculation

Conventional way:

select table_name, sum(num_rows),sum(num_rows)/tot_rows*100 "PERCENTAGE"

  from dba_tables, (select sum(num_rows) tot_rows from dba_tables)

  where owner = 'SCOTT'

 group by table_name , tot_rows

Elapsed: 00:00:00.43

Using function:

select table_name, sum(num_rows), (ratio_to_report(sum(num_rows)) over())*100 "PERCENTAGE" from dba_tables

where owner = 'SCOTT'

group by table_name

Elapsed: 00:00:00.16

· The query produces the same answer but it does so more efficiently, because it does not have to make two passes over the data to arrive at the answer.

·Because the analytic functions are built-in, queries that use them will find the answer more rapidly than the "pure" SQL-based approach.

·We achieved 27 msec just by using this built in function!