Posted by: oraclerob | November 7, 2007

Are ANSI SQL and Oracle SQL the same?

There is two types of syntax for writing SQL joins the ANSI way and the Oracle way. It has been said on many a website that they are interchangeable – really ?

All tests carried out on Oracle 10.2.0.1.0, Windows 2003

First the setup

DROP TABLE ROB_TEST1 PURGE;

DROP TABLE ROB_TEST2 PURGE;

CREATE TABLE ROB_TEST1 (ID1 NUMBER, ID2 NUMBER);

CREATE TABLE ROB_TEST2 (ID1 NUMBER, ID2 NUMBER);

INSERT INTO ROB_TEST1 VALUES (1,1);

INSERT INTO ROB_TEST1 VALUES (2,2);

INSERT INTO ROB_TEST1 VALUES (3,3);

INSERT INTO ROB_TEST1 VALUES (4,4);

INSERT INTO ROB_TEST1 VALUES (5,5);

INSERT INTO ROB_TEST2 VALUES (1,1);

INSERT INTO ROB_TEST2 VALUES (3,3);

If I wanted to return all results from ROB_TEST1 and either a matching 1 or a NULL from ROB_TEST2 (an outer join) then in Oracle’s syntax I would write

SELECT R1.ID2 A ,R2.ID2 B
FROM ROB_TEST1 R1, ROB_TEST2 R2
WHERE R1.ID1 = R2.ID2(+)
AND R2.ID2(+) = 1;

A B
———- ———-
1 1
5
4
3
2

And in ANSI syntax

SELECT R1.ID2 A, R2.ID2 B
FROM ROB_TEST1 R1 LEFT JOIN ROB_TEST2 R2 ON R1.ID1 = R2.ID2 AND R2.ID2 = 1;

A B
———- ———-
1 1
5
4
3
2

What however if my requirement changes and that I required a matching 1 or 3 from ROB_TEST2. ANSI syntax first this time

SELECT R1.ID2 A, R2.ID2 B
FROM ROB_TEST1 R1 LEFT JOIN ROB_TEST2 R2 ON R1.ID1 = R2.ID2 AND R2.ID2 IN (1,3);

A B
———- ———-
1 1
2
3 3
4
5

And in the Oracle syntax

SELECT R1.ID2 A ,R2.ID2 B
FROM ROB_TEST1 R1, ROB_TEST2 R2
WHERE R1.ID1 = R2.ID2(+)
AND R2.ID2(+) IN (1,3);

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

What? Not the result I was expecting. So it appears that the syntax has some slight differences. I am fine with that, the joys of outer joins, but what was interesting is that I would have expected the Oracle syntax to work for the majority of requirements and ANSI to have a problem (support for ANSI syntax only started in version 9i I think).

After a few days I thought I try and find out why only the ANSI syntax produced a reault. The only way I could possibly find out what was happening was to dump the oracle optimizers thought processes.

A dump of the SQL can be obtained with the following

ALTER SESSION SET EVENTS ’10053 trace name context forever, level 12′ ;

And then running the statement and exit the session of turn the event off. You will find the result in udump directory

Now I do not claim to be an expert in interpretting the output, there are many people better than me, but with a bit of ‘googling’ it is possible to try and work it out bits and pieces then hopefully glue something togetger.

I have cut the snippet it out that is important

**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$3 (#0).
PM: Checking validity of predicate move-around in SEL$3 (#0).
CBQT: Validity checks failed for 26wqg6rfzhm9p.
CVM: Considering view merge in query block SEL$3 (#0)
CVM: Checking validity of merging SEL$2 (#0)
CVM: Considering view merge in query block SEL$2 (#0)
CVM: Checking validity of merging SEL$1 (#0)
CVM: Considering view merge in query block SEL$1 (#0)
Query block (000000000FA34988) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT “R1″.”ID1″ “QCSJ_C000000000300000″,”R1″.”ID2″ “QCSJ_C000000000300002″,”from$_subquery$_004″.”ID1_1″ “ID1″,”from$_subquery$_004″.”ID2_0″ “ID2″ FROM “ROB”.”ROB_TEST1″ “R1″, LATERAL( (SELECT “R2″.”ID2″ “ID2_0″,”R2″.”ID1″ “ID1_1″ FROM “ROB”.”ROB_TEST2″ “R2″ WHERE “R1″.”ID1″=”R2″.”ID2″ AND (“R2″.”ID2″=1 OR “R2″.”ID2″=3)))(+) “from$_subquery$_004″
Query block (000000000FA34988) unchanged
CVM: Merging SPJ view SEL$2 (#0) into SEL$3 (#0)
Query block (000000000FA3BF58) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT “R1″.”ID2″ “A”,”from$_subquery$_004″.”ID2_0″ “B” FROM “ROB”.”ROB_TEST1″ “R1″, LATERAL( (SELECT “R2″.”ID2″ “ID2_0″,”R2″.”ID1″ “ID1_1″ FROM “ROB”.”ROB_TEST2″ “R2″ WHERE “R1″.”ID1″=”R2″.”ID2″ AND (“R2″.”ID2″=1 OR “R2″.”ID2″=3)))(+) “from$_subquery$_004″
Query block (000000000FA3BF58) unchanged
Registered qb: SEL$64EAE176 0xfa3bf58 (VIEW MERGE SEL$3; SEL$2)
signature (): qb_name=SEL$64EAE176 nbfros=2 flg=0
fro(0): flg=0 objn=60322 hint_alias=”R1″@”SEL$2″
fro(1): flg=1 objn=0 hint_alias=”from$_subquery$_004″@”SEL$2″
CVM: Considering view merge in query block SEL$64EAE176 (#0)
CVM: Checking validity of merging SEL$1 (#0)
CVM: Considering view merge in query block SEL$1 (#0)
Query block (000000000FA3BF58) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT “R1″.”ID2″ “A”,”from$_subquery$_004″.”ID2_0″ “B” FROM “ROB”.”ROB_TEST1″ “R1″, LATERAL( (SELECT “R2″.”ID2″ “ID2_0″,”R2″.”ID1″ “ID1_1″ FROM “ROB”.”ROB_TEST2″ “R2″ WHERE “R1″.”ID1″=”R2″.”ID2″ AND (“R2″.”ID2″=1 OR “R2″.”ID2″=3)))(+) “from$_subquery$_004″
Query block (000000000FA3BF58) unchanged
CBQT: Validity checks failed for 26wqg6rfzhm9p.

On first apprearance it looks a crazy messy but there are intersting bits

SELECT “R1″.”ID2″ “A”,”from$_subquery$_004″.”ID2_0″ “B” FROM “ROB”.”ROB_TEST1″ “R1″, LATERAL( (SELECT “R2″.”ID2″ “ID2_0″,”R2″.”ID1″ “ID1_1″ FROM “ROB”.”ROB_TEST2″ “R2″ WHERE “R1″.”ID1″=”R2″.”ID2″ AND (“R2″.”ID2″=1 OR “R2″.”ID2″=3)))(+) “from$_subquery$_004″

I have never seen LATERAL before . A quick look up in the docs reveals that ‘The Oracle TABLE operator in the FROM clause is equivalent to the LATERAL operator in the standard.’

Oracle is creating a table with what appears to be some sort of alias “from$_subquery$_004″ which is reference in the intial select “from$_subquery$_004″.”ID2_0″ “B”

After a bit of a play and a bit of reorganising I can up with this

SELECT R1.ID2 A, (SELECT R2.ID2 FROM ROB_TEST2 R2 WHERE R1.ID1 = R2.ID1 AND R2.ID2 IN (1,3) AND R1.ID2 IN (1,3)) B
FROM ROB_TEST1 R1

A B
———- ———-
1 1
2
3 3
4
5

There you go another way of constructing an outer join in Oracle’s syntax – something new, SQL can be very flexible. After a bit of googling I found out that this technique is called scalar subqueries. What I guess is interesting is that the optimizer could rewrite the ANSI syntax version but not the Oracle syntax.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: