Dec
5
Written by:
Richard To
Friday, December 05, 2008 7:23 AM
Written by Rene Woody
This blog is a
continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer
for Oracle. It covers how to specify the table join syntax to be used when generating SQL alternatives.
When the syntax for SQL statements was originally developed, the conditions of joining tables were specified in the WHERE clause. The Ansi-92 SQL standard introduced specifying table joins with the INNER JOIN, CROSS JOIN and OUTER JOIN with joining conditions in the FROM clause.
One technique for rewriting the syntax of the SQL statement is to try using different JOIN syntax.
Rewrite SQL using the Ansi-92 JOIN syntax
Specify to use the JOIN clause from the Ansi-92 SQL standard when generating the SQL alternatives. During the optimization, the SQL statement is converted to the Ansi-92 SQL standard and then the SQL syntax transformation rules are applied to rewrite the converted SQL statement. Next, the Oracle hints are applied to the original SQL and the transformed SQL. So you may see SQL alternatives that use the join syntax from the original statement SQL, but these SQL alternatives are simply the original SQL with an Oracle hint applied.
The OUTER JOIN is not including in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the OUTER JOIN using the (+) operator. So to avoid producing the wrong result set, the conversion of the OUTER JOIN syntax cannot be applied.
For example:
SELECT DPT_ID
FROM EMPLOYEE
INNER JOIN DEPARTMENT
ON EMP_DEPT = DPT_ID
Rewrite SQL without using the Ansi-92 JOIN syntax
Specify to join tables in the FROM clause without the JOIN syntax or using a comma. The join analysis occurs in the WHERE clause which specifies that the column in one table is compared to a column in another table. During the optimization, the SQL statement is converted from the Ansi-92 SQL standard and then SQL syntax transformation rules are applied to rewrite the converted SQL. Next, the Oracle hints are applied to the original SQL and the transformed SQL. So you may see SQL alternatives that use the JOIN syntax from the original SQL statement, but these SQL alternatives are simply the original SQL with an Oracle hint applied.
The OUTER JOIN is not including in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the OUTER JOIN using the (+) operator. So to avoid producing the wrong result set, the conversion of the outer join syntax cannot be applied.
For example:
SELECT DPT_ID
FROM EMPLOYEE,
DEPARTMENT
WHERE DPT_ID = EMP_DEPT