Home arrow Oracle arrow Page 4 - Large Database Features In Oracle, conclusion

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:
  1. Large Database Features In Oracle, conclusion
  2. RAC Architecture
  3. Self-Managing Databases
  4. cube
  5. Analysis Functions
  6. Windowing Functions
  7. Project 9-2 Using Analytic SQL Functions and Models
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 6
December 15, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

In the results, we can see that counts and sums of amount_sold are returned at the following levels:

  • By GENDER, CHANNEL_CLASS, and MONTH
  • Subtotals by MONTH within CHANNEL_CLASS
  • Subtotals by MONTH within GENDER
  • Subtotals by CHANNEL_CLASS within GENDER
  • Subtotals by MONTH
  • Subtotals by CHANNEL_CLASS
  • Subtotals by GENDER
  • Grand total

     



 
 
>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: