ANSI vs Oracle Join Syntax [message #684475] |
Sat, 12 June 2021 09:43 |
|
dancko
Messages: 108 Registered: June 2013 Location: italy
|
Senior Member |
|
|
Hi to all
I see recommendation to use ANSI join instead of Oracle conventional one.
But the advantages are only better readability and avoid potential unwanted cartesian product?
In particularly, what I want to know is, will there be any difference in performance?
What is better in performance, ANSI syntax or Oracle one?
Thanks in advance.
[Updated on: Sat, 12 June 2021 09:47] Report message to a moderator
|
|
|
|
Re: ANSI vs Oracle Join Syntax [message #684477 is a reply to message #684475] |
Sat, 12 June 2021 10:27 |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Take this example of a full outer exclusive join (remember that from your relational algebra classes?) in the HR demo schema:orclz>
orclz> set autot trace exp
orclz>
orclz> select coalesce(e.last_name,d.department_name) from employees e full join departments d
2 on (e.department_id=d.department_id)
3 where e.department_id is null or d.department_id is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 2631508678
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 6954 | 6 (0)| 00:00:01 |
|* 1 | VIEW | VW_FOJ_0 | 122 | 6954 | 6 (0)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 122 | 3294 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1177 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."DEPARTMENT_ID" IS NULL OR "D"."DEPARTMENT_ID" IS NULL)
2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
orclz>
orclz> select e.last_name from employees e
2 where not exists (select 'x' from departments d where e.department_id=d.department_id)
3 union all
4 select d.department_name from departments d
5 where not exists (select 'x' from employees e where e.department_id=d.department_id);
Execution Plan
----------------------------------------------------------
Plan hash value: 1717839905
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 334 | 6 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 11 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS ANTI | | 17 | 323 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."DEPARTMENT_ID" IS NULL)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
orclz> The optimizer is coming with something very different. Which would be quicker? I dunno! You would have to test, on your data. One thing to note - the estimate for the join cardinality is much better with the Oracle trad syntax.
|
|
|
|
Re: ANSI vs Oracle Join Syntax [message #684479 is a reply to message #684478] |
Sun, 13 June 2021 01:24 |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:it's depends on data and in which context the query is executed. Do you think so? It would be interesting to see your tests. I have always found ANSI syntax to equal to or better than the old style. This is another solution to the full exclusive outer join,select e.last_name from departments d,employees e where e.department_id=d.department_id(+) and d.department_id is null
union all
select d.department_name from employees e,departments d where e.department_id(+)=d.department_id and e.department_id is null
/ but I would be surprised if it performed as well as the ANSI version. When tuning SQL, it is important to try all the equivalent SQLs you can think of to see if there is some way you can help the optimizer.
|
|
|
Re: ANSI vs Oracle Join Syntax [message #684482 is a reply to message #684479] |
Sun, 13 June 2021 11:40 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Based on my own experience:
- some Oracle tools (like Reports Builder, e.g. 10g) don't understand JOINs; they force you to use the old, comma-separated list of tables in the FROM clause (for example, in Format Triggers)
- Oracle's "old" outer join operator, (+), lets you outer join one table to max one another table. OUTER JOIN, on the other hand, lets you outer join it to as many tables as needed, in a simple manner
|
|
|
Re: ANSI vs Oracle Join Syntax [message #684483 is a reply to message #684482] |
Sun, 13 June 2021 13:37 |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Quote:Oracle's "old" outer join operator, (+), lets you outer join one table to max one another table.
You have to be more specific and not confuse people. What you said was true to old, long desupported versions:
SQL> SELECT BANNER
2 FROM V$VERSION
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> SELECT *
2 FROM DUAL T1,
3 DUAL T2,
4 DUAL T3,
5 DUAL T4
6 WHERE T1.DUMMY(+) = T2.DUMMY
7 AND T1.DUMMY(+) = T3.DUMMY
8 AND T1.DUMMY(+) = T4.DUMMY
9 /
AND T1.DUMMY(+) = T3.DUMMY
*
ERROR at line 7:
ORA-01417: a table may be outer joined to at most one other table
SQL> SELECT BANNER
2 FROM V$VERSION
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
SQL> SELECT *
2 FROM DUAL T1,
3 DUAL T2,
4 DUAL T3,
5 DUAL T4
6 WHERE T1.DUMMY(+) = T2.DUMMY
7 AND T1.DUMMY(+) = T3.DUMMY
8 AND T1.DUMMY(+) = T4.DUMMY
9 /
D D D D
- - - -
X X X X
SQL>
SY.
|
|
|
Re: ANSI vs Oracle Join Syntax [message #684497 is a reply to message #684483] |
Tue, 15 June 2021 12:04 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Solomon
What you said was true to old, long desupported versions
Right; thank you for pointing it out.
Though, people (me included) still use that old, long desupported version. I'm pretty much sure that we're not the only ones. Time to upgrade? Certainly, but that's not my call. Do we have newer databases? Yes, 12c and 19c, but something is still on 11g.
|
|
|