Home » SQL & PL/SQL » SQL & PL/SQL » ANSI vs Oracle Join Syntax
ANSI vs Oracle Join Syntax [message #684475] Sat, 12 June 2021 09:43 Go to next message
dancko
Messages: 101
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 #684476 is a reply to message #684475] Sat, 12 June 2021 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You can answer this question for yourself by writing the SQL both ways & then produce & compare the EXECUTION PLAN for both.
Re: ANSI vs Oracle Join Syntax [message #684477 is a reply to message #684475] Sat, 12 June 2021 10:27 Go to previous messageGo to next message
John Watson
Messages: 8580
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 #684478 is a reply to message #684477] Sat, 12 June 2021 11:10 Go to previous messageGo to next message
dancko
Messages: 101
Registered: June 2013
Location: italy
Senior Member
Thanks for the response. It's seems like that there is not an univocal answer but it's depends on data and in which context the query is executed.
Re: ANSI vs Oracle Join Syntax [message #684479 is a reply to message #684478] Sun, 13 June 2021 01:24 Go to previous messageGo to next message
John Watson
Messages: 8580
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 Go to previous messageGo to next message
Littlefoot
Messages: 21683
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3067
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 Go to previous message
Littlefoot
Messages: 21683
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.
Previous Topic: dbms_scheduler.create_job() throws error ORA-27362
Next Topic: Help improve SQL Query
Goto Forum:
  


Current Time: Wed Jul 28 15:08:00 CDT 2021