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

FILTER

Execution Plan image: image\Plan_Filter_Step.gif   

Row operation.

FILTER condition that is applied when performing a table access (such as during a FILTER operation) is sometimes implicit. Any FILTER clause condition when no index can be used to assist in the evaluation. Unfortunately, the WHERE performs a TABLE ACCESS BY ROWID does not show up in the plan. When FILTER shows up in a plan, it is usually the result of a missing index or the disabling of an existing index.

The FILTER operation was in a prior example—the CONNECT BY operation example. In the query shown in the following example, the WHERE criteria on the State column is not applied until after the CONNECT BY hierarchy has completed; the resulting rows are filtered to determine which meet the specified State criteria.

Example

select Company_ID, Name
from COMPANY
where State = 'VA'
connect by Parent_Company_ID = prior Company_ID
start with Company_ID = 1;

Execution Plan

FILTER
CONNECT BY
INDEX UNIQUE SCAN COMPANY_PK
TABLE ACCESS BY ROWID COMPANY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$PARENT

Interpreting the Execution Plan

The plan shows that first the COMPANY_PK index is being used to find the root node (Company_ID = 1), the index on the Parent_Company_ID column is used to provide values for queries against the Company_ID column in an iterative fashion. After the hierarchy of Company_ID is complete, the FILTER operation—the WHERE clause related to the State value—is applied. The query does not use the index on the State column, although it is available and the column is used in the WHERE clause.

 

FIRST ROWS

Execution plan image: image\Plan_First_Row.gif

Row operation.

FIRST ROWS uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best response time (minimum resource use to return the first row of the result set).

The following statement changes the goal of the cost-based optimizer for your session to best response time:

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

 

Related Topics

 

FIXED INDEX

Execution Plan image: image\Plan_Fixed_Table.gif   

Set operation.

Where an index exists on a V$ table, it will normally be used whenever the column is used for an exact lookup. The execution plan reveals that this is so through the special access path FIXED INDEX. For instance, the following query uses the SID index on V$SESSION:

select *
from v$session where sid=171

Rows

Execution Plan

1

FIXED TABLE FIXED INDEX #1 X$KSUSE
cpu=1 elapsed=1 logical=0 physical=0

Remembering that the "index" is not really an Oracle B-tree index and in has more in common with a hash cluster, it’s not surprising to see that the index is disabled if a range scan is attempted:

select *
from v$session where sid<8

Rows

Execution Plan

700

FIXED TABLE FULL X$KSUSE
cpu=19 elapsed=19 logical=0 physical=0

 

FIXED TABLE

Execution Plan image: image\Plan_Fixed_Table.gif   

Set operation.

Fixed tables are those not in Oracle’s data dictionary. FIXED TABLE is normally used to optimize V$ and X$ statements. However, join order for a V$ query is determined by the order of tables in the FROM clause. Be careful when using FIXED TABLE because:

  • Neither the cost-based optimizer nor the rule-based optimizer recognizes the presence of V$ indexes when determining join order or method.

  • There are never any optimizer statistics held against the V$ or X$ tables and consequently the cost-based optimizer has no information to use to determine the best join order.

 

Related Topics

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating