l thought that this might be an interesting challenge. A developer is struggling to adjust to Oracle Database, having come from a Microsoft SQL Server development background, and he is looking for a translator that will convert his ANSI SQL to SQL that will work with Oracle Database 8i. My reply indicated that Oracle provides a free ANSI SQL to Oracle specific SQL translator and tutoring tool in the free Oracle XE. A free translator and tutor? Sure, below is an example using Oracle Database 11.2.0.1. First, let’s create a couple of tables and collect the statistics for those tables:

We now have three tables with a NOT NULL constraint on a column. Let’s assume that the following two ANSI SQL statements are crafted to access the three tables:

SELECT
T1.PADDING T1_PADDING,
T2.PADDING T2_PADDING
FROM
T1 LEFT JOIN T2
ON T1.C1=T2.C1
WHERE
T1.C1 IN (
SELECT
T3.C1
FROM
T3);
SELECT
T1.PADDING T1_PADDING,
T2.PADDING T2_PADDING
FROM
T1 LEFT JOIN T2
ON T1.C1=T2.C1
WHERE
T1.C1 NOT IN (
SELECT
T3.C1*2
FROM
T3);

Note that in the above, one of the SQL statements contains an IN clause, and the other a NOT IN clause. That IN clause could also be written as an EXISTS clause (because of the NOT NULL constraint on column C1), or as an inline view. Oracle Database 8i will NOT automatically transform an IN clause into an EXISTS clause, even when it is much more efficient to execute the SQL statement with the EXISTS clause (more recent release versions of Oracle Database might perform the transformation). So let’s try an experiment (if you are running Oracle Database 8i, will have to manually convert the above ANSI joins into Oracle specific syntax to try this on Oracle Database 8i):

Now take a look inside the 10053 trace files, you might find equivalent, transformed versions of the SQL statements like these (reformatted to add extra whitespace) in the WATCH_TRANSFORM trace file (note that in the thread Yasu mentioned that he received a different final transformation when connected as the SYS user than he did when connected as a normal user):

If you are looking at the above, trying to learn alternate SQL syntax that may be more efficient than what you have used in the past, you should note that the last of the above SQL statements is technically not logically equivalent to the submitted SQL statement due to the potential for duplicate values in column C1 in table T3 (also note that the first SQL statement is missing the IN clause). However, the optimizer in 11.2.0.1 understands how to handle this join as a semi-join, and will stop the join to the second table when the first matching row is found.

In the WATCH_TRANSFORM2 trace file, we see the NOT IN version of the SQL statement:

If you are looking at the above, trying to learn alternate SQL syntax that may be more efficient than what you have used in the past, you should note that the last of the above SQL statements is technically not logically equivalent to the submitted SQL statement due to the potential for duplicate values in column C1 in table T3. However, the optimizer in 11.2.0.1 understands how to handle this join as a anti-join, and will stop the join to the second table when the first matching row is found.

Let’s try again with a change to the OPTIMIZER_FEATURES_ENABLE parameter:

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='8.1.7';
ALTER SESSION SET TRACEFILE_IDENTIFIER='WATCH_TRANSFORM3';
SELECT
T1.PADDING T1_PADDING,
T2.PADDING T2_PADDING
FROM
T1 LEFT JOIN T2
ON T1.C1=T2.C1
WHERE
T1.C1 IN (
SELECT
T3.C1
FROM
T3);
ALTER SESSION SET TRACEFILE_IDENTIFIER='WATCH_TRANSFORM4';
SELECT
T1.PADDING T1_PADDING,
T2.PADDING T2_PADDING
FROM
T1 LEFT JOIN T2
ON T1.C1=T2.C1
WHERE
T1.C1 NOT IN (
SELECT
T3.C1*2
FROM
T3);

Now you might see something like this in the WATCH_TRANSFORM3 trace file (the IN syntax):

Notice in the above, the odd last transformation. Oracle Database 8i is not supposed to be able to transform a SQL statement using a NOT IN clause into a SQL statement using a NOT EXISTS clause, even though it might be more efficient.

Just for fun, let’s see what happens when we add a NO_QUERY_TRANSFORMATION hint after the SELECT in the SQL statements while leaving the modified OPTIMIZER_FEATURES_ENABLE parameter set:

Notice in the above, that again the optimizer has transformed the IN syntax into EXISTS syntax, even though Oracle Database 8i did not support that automatic transformation (and we did specifically request no query transformations through the use of the hint).

In all of the above cases, the first transformed SQL statement printed in the 10053 trace file excluded the IN and the NOT IN clauses, but did include the ANSI to Oracle specific join syntax conversion.

It is nice to know that Oracle Corp. throws in something free with Oracle Database: a free ANSI SQL to Oracle Specific SQL translator, and a free SQL tutor that teaches rewriting SQL statements into alternate SQL syntax that just might be more efficient than the original SQL statement (you might be able to use the more efficient version with older Oracle Database releases, as was the case for the NOT IN to TO EXISTS transformation).

Thank you for the compliment. I wonder if an external table dfinition is possible? The format of the 10053 trace file seems to have changed quite a bit between releases, so it might be challenging to even write a utility to extract the SQL statements, but it might be a fun exercise.

—

In this article I mentioned that someone in the OTN thread by the name of “Yasu” noticed a different final execution plan when the SQL statements were executed as the SYS user. I thought that it might be interesting to test Yasu’s statement, so here are the SQL statements found in the first trace file ‘WATCH_TRANSFORM’ from a 11.2.0.2 database for the SYS user and a normal user:
WATCH_TRANSFORM for SYS user:

If the output is examined closely, the third SQL statement listed for the two users is very different. This little test might explain why the SYS user could see different performance characteristics than would a normal database user.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: