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
-
Click in the main menu.
-
Select Optimize SQL | Optimizer | Optimization.
-
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
-
Click in the main menu.
-
Select Optimize SQL | Optimizer | Hints | Optimization Approaches.
-
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
-
Click in the main menu.
-
Select Optimize SQL | Optimizer | Hints | Access Paths.
- Select the hints to use.
Related Topics
Optimization
Optimization Approaches
Query Transformations
Join Orders / Operations
Parallel Execution
Other Hints
Quota