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

Optimization

Use this page of the Options dialog to specify customized optimization settings for SQL Rewrite.

To enable the options on this page if disabled, go to Optimize SQL | Optimizer | Intelligence in the Options dialog and select Custom Settings. See Intelligence (Optimize SQL) for more information.

To set optimization options

  1. Click in the main menu.

  2. Select Optimize SQL | Optimizer | Optimization.

  3. Select custom settings to use in optimization. Review the following for additional information:

    Option Group

    Option Description

    View to Inline View Transformation

     

    Transform view to inline view

    • Select to transform the view in the text of the SQL statement to an inline view.

     

    Transformation level

    • Enter the number of levels to search for a view.

     

    Ignore SYS views

    • Select to ignore SYS schema views when applying the view to inline view transformation.

     

    Apply MERGE hint to transformed inline view

    • Select to apply the Oracle MERGE hint to the View's SELECT statement.

     

    Apply NO_MERGE hint to transformed inline view

    • Select to apply the Oracle NO_MERGE hint to the View's SELECT statement.

    Query to inline view transformation

     

    Transform query to inline view

    Select to transform the query to an inline view, a subquery used as a table in a FROM clause. For example:

    Original SQL

    SELECT *

      FROM DEPARTMENT

     WHERE DPT_ID IN (SELECT EMP_DEPT

                        FROM EMPLOYEE)

     

    Alternative SQL

    SELECT DEPARTMENT.*

      FROM DEPARTMENT,

                        (SELECT DISTINCT EMPLOYEE.EMP_DEPT COL1

              FROM EMPLOYEE) TEMP0

      DEPARTMENT.DPT_ID = TEMP0.COL1

    Join Tables

     

    Select one of the following:

    • Rewrite SQL using the same JOIN syntax as the source SQL

      Select this option to instruct SQL Optimizer to use the same JOIN syntax as the original statement to rewrite SQL alternatives.

    • Rewrite SQL using the Ansi-92 JOIN syntax

      Join tables in the FROM clause using the Ansi-92 JOIN syntax. During optimization, SQL Optimizer converts the SQL statement to the Ansi-92 SQL standard and applies SQL syntax transformation rules to rewrite the converted SQL statement. SQL Optimizer then applies Oracle hints to the source SQL and the transformed SQL. You may see SQL alternatives that use the join syntax from the source SQL, but these SQL alternatives are simply the source SQL statement with an Oracle hint applied.

      The OUTER JOIN is not included in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the OUTER JOIN using the (+) operator. The conversion of the OUTER JOIN syntax is not applied to avoid producing the wrong result set.

      For example:

      SELECT DPT_ID

        FROM EMPLOYEE

             INNER JOIN DEPARTMENT

                ON EMP_DEPT = DPT_ID

    • Rewrite SQL without using the Ansi-92 JOIN syntax

      Join tables in the FROM clause without using the Ansi-92 JOIN syntax or a comma. The join analysis occurs in the WHERE clause which specifies the column in one table that is compared to a column in another table. During optimization, SQL Optimizer converts the SQL statement from the Ansi-92 SQL standard and applies SQL syntax transformation rules to rewrite the converted SQL. SQL Optimizer then applies Oracle hints to the source SQL and the transformed SQL. You may see SQL alternatives that use the JOIN syntax from the source SQL, but these SQL alternatives are simply the source SQL statement with an Oracle hint applied.

      The OUTER JOIN is not included in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the OUTER JOIN using the (+) operator. The conversion of the OUTER JOIN syntax is not applied to avoid producing the wrong result set.

      For example:

      SELECT DPT_ID

        FROM EMPLOYEE,

             DEPARTMENT

       WHERE DPT_ID = EMP_DEPT

    • Rewrite SQL with and without using the Ansi-92 JOIN syntax

      Join tables using either one of the JOIN syntax methods.

 

Related Topics

Intelligence (Optimize SQL)

Optimization Approaches

Access Paths

Query Transformations

Join Orders / Operations

Parallel Execution

Other Hints

Quota

Hints

Optimization Approaches

Use this page of the Options dialog to specify customized optimization settings for SQL Rewrite.

To enable the options on this page if disabled, go to Optimize SQL | Optimizer | Intelligence in the Options dialog and select Custom Settings. See Intelligence (Optimize SQL) for more information.

To select optimization approach hints

  1. Click in the main menu.

  2. Select Optimize SQL | Optimizer | Hints | Optimization Approaches.

  3. Select the hints to use.

 

Related Topics

Optimization

Access Paths

Query Transformations

Join Orders / Operations

Parallel Execution

Other Hints

Quota

Access Paths

Use this page of the Options dialog to specify customized optimization settings for SQL Rewrite.

To enable the options on this page if disabled, go to Optimize SQL | Optimizer | Intelligence in the Options dialog and select Custom Settings. See Intelligence (Optimize SQL) for more information.

To select access path hints

  1. Click in the main menu.

  2. Select Optimize SQL | Optimizer | Hints | Access Paths.

  3. Select the hints to use.

 

Related Topics

Optimization

Optimization Approaches

Query Transformations

Join Orders / Operations

Parallel Execution

Other Hints

Quota

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating