Analytics Rock & Roll
Analytic functions were first intodruced in Oracle 8i. It certainly helped in improving performance of queries. The first ones are ROLLUP and CUBE. In this article I will demonstrate ROLLUP function.
SQL> desc customer
Name Null? Type
-------------------------- -------------- ------------------
CUST_ID NOT NULL NUMBER(38)
CUST_FNAME VARCHAR2(20)
CUST_LNAME VARCHAR2(20)
SQL> select * from customer;
CUST_ID CUST_FNAME CUST_LNAME
---------- -------------------- --------------------
1001 SCOTT TOM
1002 PETER DAVID
1003 ELLISON BOBBY
SQL> desc cust_order
Name Null? Type
-------------------------- -------- ------------------
ORDER_ID NUMBER(38)
ORDER_DATE DATE
CUST_ID NUMBER(38)
ORDER_QTY NUMBER(38)
SUPPLIED_QTY NUMBER(38)
ORDER_MESS CHAR(700)
SQL> select * from cust_order where rownum < 10;
ORDER_ID ORDER_DAT CUST_ID ORDER_QTY SUPPLIED_QTY ORDER_MESS
---------- --------- ---------- ---------- ------------ ----------
1 18_JAN-05 1001 16 5 X
2 17_JAN-05 1001 14 5 X
3 01_JAN-02 1001 20 5 X
4 01_JAN-03 1001 15 5 X
5 01_JAN-04 1001 12 5 X
6 01_JAN-02 1001 17 5 X
7 12_JAN-05 1001 10 5 X
8 01_JAN-03 1001 16 5 X
9 01_JAN-02 1001 13 5 X
……………………
…………………….
Many line are omitted.
SQL> select count(*) from cust_order ;
COUNT(*)
----------
127791
I have nicely setup 2 tables and analyzed them.
SQL> select table_name, blocks, avg_row_len from user_tables where table_name like 'CUS%' ;
TABLE_NAME BLOCKS AVG_ROW_LEN
------------------------------ ---------- -----------------------
CUSTOMER 1 19
CUST_ORDER 14200 729
select *
from ( select /*+ ordered */
cust_fname, cust_lname, to_char(b.cust_id) as cust, sum(order_qty) ord_tot, sum(supplied_qty) ord_supp
from cust_order a, customer b
where b.cust_id = a.cust_id
group by cust_fname, cust_lname, to_char(b.cust_id)
UNION
select /*+ Ordered */
'', '', 'Report Total' as cust, sum(order_qty) ord_tot, sum(supplied_qty) ord_supp
from cust_order a, customer b
where b.cust_id = a.cust_id
)
order by 1 NULLS LAST
CUST_FNAME CUST_LNAME CUST ORD_TOT ORD_SUPP
-------------------- -------------------- ----------------- ---------- ----------
ELLISON BOBBY 1003 639105 127791
PETER DAVID 1002 638318 170388
SCOTT TOM 1001 639977 212985
Report Total 1917400 511164
The above type output is very common - printing the report total at the end of the report.
Tkprof output shows:Call Count Cpu Elapsed Disk Query Current Rows
Thanks, TamilParse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0. 72 0.70 0 14474 0 4
Total 4 0. 72 0.70 0 14474 0 4
Rows Row Source Operation
------- ---------------------------------------------------
4 VIEW
4 FILTER
10 SORT GROUP BY ROLLUP
127791 TABLE ACCESS BY INDEX ROWID CUST_ORDER
127795 NESTED LOOPS
3 TABLE ACCESS FULL CUSTOMER
127791 INDEX RANGE SCAN CUST_ORDER_S1 (object id 64731)The number of LIOs is reduced by 50 %.Another option using in-line view.
select (case
when custid is NULL then 'Report Total'
else to_char(custid)
end ) newcust, a.cust_fname, a.cust_lname, ord_tot, ord_supp
from
(
select cust_id as custid , sum(order_qty) ord_tot, sum(supplied_qty) ord_supp
from cust_order b
group by rollup( b.cust_id)
) c,
customer a
where c.custid = a.cust_id(+)
NEWCUST CUST_FNAME CUST_LNAME ORD_TOT ORD_SUPP
------------- -------------------- -------------------- ---------- ----------
1001 SCOTT TOM 639977 212985
1002 PETER DAVID 638318 170388
1003 ELLISON BOBBY 639105 127791
Report Total 1917400 511164
Thanks, TamilParse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.48 1.48 0 28409 0 4
Total 4 1.49 1.49 0 28409 0 4
The above query executes 28,409 LIOs.
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT ORDER BY
4 VIEW
4 SORT UNIQUE
4 UNION-ALL
3 SORT GROUP BY
127791 HASH JOIN
127791 TABLE ACCESS FULL CUST_ORDER
3 TABLE ACCESS FULL CUSTOMER
1 SORT AGGREGATE
127791 NESTED LOOPS
127791 TABLE ACCESS FULL CUST_ORDER
127791 INDEX UNIQUE SCAN CUSTOMER_PK (object id 64728)
The same query is rewritten using ROLLUP function.
select *
from
(
select decode(grouping(cust_fname),1, '*All First Name', cust_fname) FNAME,
decode(grouping(cust_lname),1, '*All Last Name', cust_lname) LNAME,
decode(grouping(b.cust_id), 1, '*Report Total', b.cust_id) custid,
sum(order_qty) ord_tot,
sum(supplied_qty) ord_supp
from cust_order a,
customer b
where b.cust_id = a.cust_id
group by rollup(cust_fname, cust_lname, b.cust_id)
) c
where (c.custid = '*Report Total' and
c.lname = '*All Last Name' and
c.fname = '*All First Name' ) OR
(c.custid != '*Report Total' )
FNAME LNAME CUSTID ORD_TOT ORD_SUPP
-------------------- -------------------- ------------------ ---------- ----------
PETER DAVID 1002 638318 170388
SCOTT TOM 1001 639977 212985
ELLISON BOBBY 1003 639105 127791
*All First Name *All Last Name *Report Total 1917400 511164
No comments:
Post a Comment