SQL Optimizer for Oracle 9.3.3

The query shown in the preceding listing selects the number of rows in each Zip, as listed in the COMPANY table. One record is returned for each distinct value in the Zip column.

Execution Plan

SORT GROUP BY
TABLE ACCESS FULL COMPANY

Interpreting the Execution Plan

The Execution Plan shows that the COMPANY table was scanned. After the table has been read, the SORT operation sorts and groups the records according to the value of their Zip columns. Each group is then counted, and the result is returned to the requester.

SORT GROUP BY CUBE

Execution Plan image: $image\Plan_Count_GroupBy_Cube.gif$

Aggregation operation.

The subtotals created by ROLLUP represent only a fraction of the possible subtotal combinations. The easiest way to generate the full set of subtotals needed for cross-tabular reports is to use the CUBE extension.

CUBE enables a SELECT statement to calculate subtotals for all of the possible combinations of a group of dimensions. It also calculates a grand total. This is the set of information typically needed for all cross-tabular reports, so CUBE can calculate a cross-tabular report with a single SELECT statement. Like ROLLUP, CUBE is a simple extension to the GROUP BY clause.

Example

CUBE displays in the GROUP BY clause in a SELECT statement.

The following is the syntax for CUBE:

SELECT ... GROUP BY
CUBE (grouping_column_reference_list)

CUBE takes a specified set of grouping columns and creates subtotals for all of the possible combinations. In terms of multi-dimensional analysis, CUBE generates all of the subtotals that could be calculated for a data cube with the specified dimensions. When you have specified CUBE(Time, Region, Department), the result set will include all of the values that would be included in an equivalent ROLLUP statement plus additional combinations. When n columns are specified for a CUBE, there will be 2n combinations of subtotals returned. The following is an example of a three-dimensional CUBE, and its output. This example of CUBE uses the data in the video store database.

SELECT Time, Region, Department,
SUM(Profit) AS Profit FROM sales
GROUP BY CUBE (Time, Region, Dept);

Output

CUBE Aggregation across Three Dimensions

Time Region Department Profit
---- ------ ---------- ------
1996 Central VideoRental 75,000
1996 Central VideoSales 74,000
1996 Central NULL 149,000
1996 East VideoRental 89,000
1996 East VideoSales 115,000
1996 East NULL 204,000
1996 West VideoRental 87,000
1996 West VideoSales 86,000
1996 West NULL 173,000
1996 NULL VideoRental 251,000
1996 NULL VideoSales 275,000
1996 NULL NULL 526,000
1997 Central VideoRental 82,000
1997 Central VideoSales 85,000
1997 Central NULL 167,000
1997 East VideoRental 101,000
1997 East VideoSales 137,000
1997 East NULL 238,000
1997 West VideoRental 96,000
1997 West VideoSales 97,000
1997 West NULL 193,000
1997 NULL VideoRental 279,000
1997 NULL VideoSales 319,000
1997 NULL NULL 598,000
NULL Central VideoRental 157,000
NULL Central VideoSales 159,000
NULL Central NULL 316,000
NULL East VideoRental 190,000
NULL East VideoSales 252,000
NULL East NULL 442,000
NULL West VideoRental 183,000
NULL West VideoSales 183,000
NULL West NULL 366,000
NULL NULL VideoRental 530,000
NULL NULL VideoSales 594,000
NULL NULL NULL 1,124,000

Partial Cube

Partial CUBE resembles partial ROLLUP in that you can limit it to certain dimensions. In this case, subtotals of all of the possible combinations are limited to the dimensions within the cube list (in parentheses), as in:

GROUP BY expr1, CUBE(expr2, expr3)

The above syntax example calculates 2*2, or 4, subtotals. That is:

(expr1, expr2, expr3)

(expr1, expr2)

(expr1, expr3)

(expr1)

Using the video store database example, we can issue the following statement:

SELECT Time, Region, Department,
SUM(Profit) AS Profit FROM sales
GROUP BY Time CUBE(Region, Dept);

Output

Partial CUBE

When to Use CUBE

The following are cases where you would use CUBE:

Use CUBE in any situation requiring cross-tabular reports. The data needed for cross-tabular reports can be generated with a single SELECT using CUBE. Like ROLLUP, CUBE can be helpful in generating summary tables. Note that the population of the summary tables is even faster when the CUBE query executes in parallel.
CUBE is especially valuable in queries that use columns from multiple dimensions rather than columns representing different levels of a single dimension. For instance, a commonly requested cross-tabulation might need subtotals for all the combinations of month, state, and product. These are three independent dimensions, and analysis of all possible subtotal combinations is commonplace. In contrast, a cross-tabulation showing all possible combinations of year, month, and day would have several values of limited interest, since there is a natural hierarchy in the time dimension. Subtotals such as profit by day of month summed across year would be unnecessary in most analyses.

SORT GROUP BY NOSORT

Execution Plan image: $image\Plan_Count_GroupBy_NoSort.gif$

Aggregation operation.

One cause of sorting is when indexes are created . Creating an index for a table involves sorting all of the rows in a table based on the values of the indexed columns. Oracle also allows you to create indexes without sorting, using the SORT GROUP BY NOSORT operation. When the rows in the table are loaded in ascending order, you can create the index faster without sorting.

NOSORT Clause

To create an index without sorting, load the rows into the table in ascending order of the indexed column values. Your operating system may provide a sorting utility to sort the rows before you load them. When you create the index, use the NOSORT clause on the CREATE INDEX statement. For example, this CREATE INDEX statement creates the index EMP_INDEX on the ENAME column of the emp table without sorting the rows in the EMP table:

CREATE INDEX emp_index
ON emp(ename)
NOSORT;

When to Use the NOSORT Clause

Presorting your data and loading it in order may not always be the fastest way to load a table. When you have a multiple-CPU computer, you may be able to load data faster using multiple processors in parallel, each processor loading a different portion of the data. To take advantage of parallel processing, load the data without sorting it first. Then create the index without the NOSORT clause. When you have a single-CPU computer, you should sort your data before loading, when possible. Then create the index by using the NOSORT clause.

GROUP BY NOSORT

Sorting can be avoided when performing a GROUP BY operation when you know that the input data is already ordered, so that all rows in each group are clumped together. This may be the case when the rows are being retrieved from an index that matches the grouped columns, or when a sort-merge join produces the rows in the right order. ORDER BY sorts can be avoided in the same circumstances. When no sort takes place, the Execution Plan indicates GROUP BY NOSORT.

SORT GROUP BY ROLLUP

Execution Plan image: $image\Plan_Count_GroupBy_RollUp.gif$

Aggregation operation.

SORT GROUP BY ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query. For example, ROLLUP displays in the GROUP BY clause in a SELECT statement.

The syntax is:

SELECT ... GROUP BY ROLLUP(grouping_column_reference_list)

The ROLLUP action is straightforward: it creates subtotals which roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns. For instance, if a query specifies ROLLUP on grouping columns of Time, Region, and Department (n=3), the result set will include rows at four aggregation levels.

Example

This example of ROLLUP uses a video store database:

SELECT Time, Region, Department,
SUM(Profit) AS Profit FROM sales
GROUP BY ROLLUP(Time, Region, Dept);

As you can see in the output that follows, this query returns the following sets of rows:

Regular aggregation rows that would be produced by GROUP BY without using ROLLUP.
First-level subtotals aggregating across Department for each combination of Time and Region.
Second-level subtotals aggregating across Region and Department for each Time value.
A grand total row.

ROLLUP Aggregation across Three Dimensions
Time Region Department Profit
---- ------ ---------- ------
1996 Central VideoRental 75,000
1996 Central VideoSales 74,000
1996 Central NULL 149,000
1996 East VideoRental 89,000
1996 East VideoSales 115,000
1996 East NULL 204,000
1996 West VideoRental 87,000
1996 West VideoSales 86,000
1996 West NULL 173,000
1996 NULL NULL 526,000
1997 Central VideoRental 82,000
1997 Central VideoSales 85,000
1997 Central NULL 167,000
1997 East VideoRental 101,000
1997 East VideoSales 137,000
1997 East NULL 238,000
1997 West VideoRental 96,000
1997 West VideoSales 97,000
1997 West NULL 193,000
1997 NULL NULL 598,000
NULL NULL NULL 1,124,000

Note: The NULLs shown in the figures are displayed only for clarity: in standard Oracle output, these cells would be blank.

제품을 선택하십시오.

더 나은 서비스 제공을 위해 채팅 목적을 작성해 주십시오.

문제에 대한 권장 솔루션

SQL Optimizer for Oracle 9.3.3 - User Guide

SORT GROUP BY

Example

Execution Plan

Interpreting the Execution Plan

SORT GROUP BY CUBE

Example

Output

Partial Cube

Output

When to Use CUBE

SORT GROUP BY NOSORT

NOSORT Clause

When to Use the NOSORT Clause

GROUP BY NOSORT

SORT GROUP BY ROLLUP

Example