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

COUNT

Execution Plan image: image\Plan_Count.gif   

Aggregation operation.

COUNT is executed when the RowNum pseudo-column is used without specifying a maximum value for RowNum. COUNT receives rows from its child operations and increments the RowNum counter. If a limiting counter is used on the RowNum pseudo-column, then the COUNT STOPKEY operation is used instead of COUNT.

Example

select Name, City, State, RowNum
from COMPANY
where City > 'Roanoke'
order by Zip;

The query shown in the preceding listing selects rows from the COMPANY table. Each row will have the original row number returned.

Execution Plan

SORT ORDER BY
COUNT
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

Interpreting the Execution Plan

The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY table that satisfy the WHERE clause condition (where City > 'Roanoke'). The ROWIDs from the City index scan are used to query the COMPANY table for the Name and State column values. For each row returned, the counter is incremented. Because of the use of the index, the rows that are returned are the "lowest" city names that are greater than the value 'Roanoke'. The rows are returned from the COMPANY$CITY index in ascending order of the City column’s value. The RowNum pseudo-column will then be calculated and put into the row. The SORT ORDER BY operation will order the rows by Zip, as requested in the ORDER BY clause.

Note: The RowNum values are assigned before the ordering takes place.

 

COUNT STOPKEY

Execution Plan image: image\Plan_Count_StopKey.gif   

Aggregation operation.

COUNT STOPKEY condition is generated. No More Rows receives rows from the previous operation and increments a counter; if the counter reaches a given threshold, a COUNT STOPKEY pseudo-column is used with a limiting counter.

Example

select Name, City, State
from COMPANY
where City > 'Roanoke'
and Rownum <= 100;

The query shown in the preceding listing selects from the COMPANY table based on two criteria in its WHERE clause. The first criteria can use the COMPANY$CITY index to obtain ROWIDs for the rows to return. The second criteria limits the number of returned records to 100. Since the Name and State columns are required by the query, the COMPANY table will have to be accessed; the City values are already available via the index searches.

Execution Plan

COUNT STOPKEY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

Interpreting the Execution Plan

The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY table that satisfy the City value condition. The ROWIDs from the index scan are used to query the COMPANY table for the Name and State values. For each row returned, the counter is incremented so that only 100 rows are returned. Because of the use of the index, the rows that are returned are the 100 "lowest" City names that are greater than the value 'Roanoke', and City will sort the records.

 

CREATE AS SELECT

 

The CREATE AS SELECT operation creates a table using selected information from another table.

 

  

 

 

CUBE SCAN

 

Operation: CUBE SCAN

The CUBE SCAN operation retrieves cube organized data with inner join operations.

 

Option: PARTIAL OUTER

The CUBE SCAN PARTIAL OUTER operation retrieves data from at least one dimension of a cube with outer join operations while retrieving the rest of the dimensions of a cube with inner join operations.

 

Option: OUTER

The CUBE SCAN OUTER operation retrieves cube organized data with outer join operations.

 

  

 

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating