cube - Oracle

When you are working with large databases with Oracle Database 10g, there are certain topics with which you need to be familiar. These topics are covered in this article, which is the second of two parts. It is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (McGraw-Hill/Osborne, 2004; ISBN: 0072230789).

TABLE OF CONTENTS:

By: McGraw-Hill/Osborne

Poor Best

Rating:

/ 6

December 15, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

The cube extension takes rollup a step further by generating subtotals for each combination of the group by attributes, totals by attribute, and the grand total. The following is an example of the cube extension, using the same query we used for rollup:

select c.cust_gender gender,
       b.channel_class channel_class,
       to_char(a.time_id, 'yyyy-mm') month,
       count(*) unit_count,
       sum(a.amount_sold) amount_sold
from sales a, channels b, customers c
where a.channel_id = b.channel_id
and a.cust_id = c.cust_id
and to_char(a.time_id, 'yyyy-mm') between '2001-01' and '2001-02'
group by cube(c.cust_gender,
               b.channel_class,
               to_char(a.time_id, 'yyyy-mm'));

GENDER	CHANNEL_CLASS	MONTH	UNIT_COUNT	AMOUNT_SOLD
----------	----------------------	---------	----------------	-------------------
			39924	4216833.67
		2001-01	20739	2113709.13
		2001-02	19185	2103124.54
	Direct		20427	2113199.92
	Direct	2001-01	11039	1106147.18
	Direct	2001-02	9388	1007052.74
	Others		12603	1278645.06
	Others	2001-01	6796	657218.93
	Others	2001-02	5807	621426.13
	Indirect		6894	824988.69
	Indirect	2001-01	2904	350343.02
	Indirect	2001-02	3990	474645.67
F			14274	1552931.54
F		2001-01	7540	768012.01
F		2001-02	6734	784919.53
F	Direct		7209	752861.03
F	Direct	2001-01	4001	387000.9
F	Direct	2001-02	3208	365860.13
F	Others		4542	472249.42
F	Others	2001-01	2486	242615.9
F	Others	2001-02	2056	229633.52
F	Indirect		2523	327821.09
F	Indirect	2001-01	1053	138395.21
F	Indirect	2001-02	1470	189425.88
M			25650	2663902.13
M		2001-01	13199	1345697.12
M		2001-02	12451	1318205.01
M	Direct		13218	1360338.89
M	Direct	2001-01	7038	719146.28
M	Direct	2001-02	6180	641192.61
M	Others		8061	806395.64
M	Others	2001-01	4310	414603.03
M	Others	2001-02	3751	391792.61
M	Indirect		4371	497167.6
M	Indirect	2001-01	1851	211947.81
M	Indirect	2001-02	2520	285219.79