Chat now with support
Chat with Support

SQL Optimizer for Oracle 10.0 - User Guide

Welcome to SQL Optimizer
About SQL Optimizer SQL Optimization Workflow New in This Release Additional Resources Database Privileges Database Privileges Script Connect to the Database Windows Layout Customize Toolbars Keyboard Shortcuts Support Bundle Register SQL Optimizer Check for Updates SQL Operations
ALL PARTITION ALTER INDEX AND EQUAL ANTI JOIN BITMAP AND BITMAP COMPACTION BITMAP CONSTRUCTION BITMAP CONVERSION BITMAP INDEX BITMAP JOIN INDEX UPDATE BITMAP JOIN INDEX UPDATE STATEMENT BITMAP KEY ITERATION BITMAP MERGE BITMAP MINUS BITMAP OR BUFFER SORT CARTESIAN JOIN COLLECTION ITERATOR CONCATENATION CONNECT BY CONNECT BY PUMP COUNT COUNT STOPKEY CREATE AS SELECT CUBE SCAN DDL STATEMENT DELETE DOMAIN INDEX FAST FULL INDEX SCAN FILTER FIRST ROWS FIXED INDEX FIXED TABLE FOR UPDATE FULL INDEX SCAN FULL INDEX SCAN DESCENDING FULL INDEX SCAN (MIN/MAX) HASH GROUP BY HASH GROUP BY PIVOT HASH JOIN HASH JOIN BUFFERED HASH PARTITION HASH UNIQUE INDEX INDEX BUILD NON UNIQUE INDEX RANGE SCAN INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN (MIN/MAX) INDEX SAMPLE FAST FULL SCAN INDEX SKIP SCAN INDEX SKIP SCAN DESCENDING INDEX UNIQUE SCAN INLIST ITERATOR INLIST PARTITION INSERT INTERSECTION INTO INVALID PARTITION ITERATOR PARTITION LOAD AS SELECT MAT_VIEW ACCESS MAT_VIEW REWRITE ACCESS MERGE JOIN MINUS MULTI-TABLE INSERT NESTED LOOPS OUTER JOIN PARTITION PARTITION HASH EMPTY PARTITION LIST PARTITION RANGE PROJECTION PX BLOCK ITERATOR PX COORDINATOR PX ITERATOR PX PARTITION PX PARTITION HASH ALL PX PARTITION LIST ALL PX PARTITION RANGE ALL PX RECEIVE PX SEND RANGE PARTITION RECURSIVE EXECUTION RECURSIVE WITH PUMP REFERENCE MODEL REMOTE SELECT SEMI JOIN SEQUENCE SINGLE PARTITION SINGLE RANGE PARTITION SORT SORT AGGREGATE SORT GROUP BY SORT GROUP BY CUBE SORT GROUP BY NOSORT SORT GROUP BY ROLLUP SORT JOIN SORT ORDER BY SORT UNIQUE SQL MODEL TABLE ACCESS TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ACCESS BY INDEX ROWID TABLE ACCESS BY LOCAL INDEX ROWID TABLE ACCESS BY ROWID TABLE ACCESS BY USER ROWID TABLE ACCESS CLUSTER TABLE ACCESS FULL TABLE ACCESS HASH TABLE ACCESS SAMPLE TABLE QUEUE TEMP TABLE GENERATION TEMP TABLE TRANSFORMATION UNION UNION ALL UNION ALL (RECURSIVE WITH) UNPIVOT UPDATE VIEW VIEW PUSHED PREDICATE WINDOW
Optimize SQL
Create Optimize SQL Sessions Open Optimizer SQL Sessions Rewrite SQL Generate Execution Plan Alternatives
Optimize Indexes Batch Optimize SQL Scan SQL Inspect SGA Analyze Impact Manage Plans Configure Options SQL Optimizer Tutorials About Us Legal Notices

SORT GROUP BY

Execution Plan image: image\Plan_Sort_Group_By.gif   

Aggregation operation.

SORT GROUP BYperforms grouping functions on sets of records.

Example

select Zip, COUNT(*)
from COMPANY
group by Zip;

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.

 

Related Topics

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

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

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.

 

Related Topics

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.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating