Home » SQL & PL/SQL » SQL & PL/SQL » Use ANSI standard JOIN syntax
| Use ANSI standard JOIN syntax [message #598030] |
Wed, 09 October 2013 18:10  |
 |
nataliafoster26
Messages: 64
Registered: October 2013
|
Member |
|
|
does anyone know what this mean? Use ANSI standard JOIN syntax
for example i have this code.
SELECT resv_num, unit_date
FROM p_resv_unit ru, p_pm_unit_night pun
WHERE pun.property_id = in_property_id
AND pun.pm_unit_num = cvUnitNum
AND pun.unit_date BETWEEN start_date AND end_date
AND pun.resv_unit_id = ru.resv_unit_id;
nResvNum p_resv_unit.resv_num%TYPE;
dUnitDate p_pm_unit_night.unit_date%TYPE;
tabUnitNum Util_Pkg.var_arr;
nUnitCnt NUMBER := 1;
bResFound BOOLEAN;
BEGIN
and is it a good idea to change it, because both ways it works?
[Updated on: Wed, 09 October 2013 18:10] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Use ANSI standard JOIN syntax [message #598038 is a reply to message #598030] |
Thu, 10 October 2013 01:37  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Hi Natalia. Yes, you are right, both ways work. They're the same from a performance perspective too. Your post is a little hard to understand, but it looks to me that you are asking whether it is necessary/better practice to use the ANSI syntax over the older, Oracle syntax.
Personally, I much prefer the ANSI syntax, primarily because
It is so much easier to read.
It keeps all your joins separate from all your predicates.
It prevents silly mistakes like accidentally leaving out a single (+) on a set of join predicates in the where clause, thereby negating your outer join.
It is ridiculously easy to switch between inner, left, right, and full joins.
I'm also fairly certain that the Oracle syntax has one or two restrictions that the ANSI syntax does not (but for the life of me I can't remember them right now - maybe they'll be in one of the links that Lalit posted)
|
|
|
|
| Re: Use ANSI standard JOIN syntax [message #598042 is a reply to message #598038] |
Thu, 10 October 2013 02:06 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
With regard to restrictions on the use of ANSI syntax, I don't remember any published list. The only issue I can recall with 11.2.x was with fast refresh of materialized views. I don't have the example to hand, but I think fast refresh of a join view failed when using ANSI.
Overall, as I have said many times, I think ANSI syntax is astronomically superior: far easier to maintain, far less prone to error.
|
|
|
|
|
|
|
|
| Re: Use ANSI standard JOIN syntax [message #598045 is a reply to message #598042] |
Thu, 10 October 2013 02:23 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
John Watson wrote on Thu, 10 October 2013 08:06With regard to restrictions on the use of ANSI syntax, I don't remember any published list. Ok, but I was talking about restrictions on the Oracle syntax  .
Quote: The only issue I can recall with 11.2.x was with fast refresh of materialized views. I don't have the example to hand, but I think fast refresh of a join view failed when using ANSI. I did not know that, thanks, I'll maybe have a trawl to see if i can find a discussion on it.
Quote:Overall, as I have said many times, I think ANSI syntax is astronomically superior: far easier to maintain, far less prone to error. Preaching to the converted here John. Agree 100%, I was hoping that my post made that clear, maybe not.
|
|
|
|
|
|
| Re: Use ANSI standard JOIN syntax [message #598048 is a reply to message #598047] |
Thu, 10 October 2013 02:39 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I'm getting different performance characteristics (12.1.0.1), which I wasn't expecting:orclz>
orclz> set autot trace exp
orclz> select ename,dname from emp,dept where emp.deptno(+)=dept.deptno
2 union
3 select ename,dname from emp,dept where emp.deptno=dept.deptno(+);
Execution Plan
----------------------------------------------------------
Plan hash value: 2227970010
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 1218 | 12 (50)| 00:00:01 |
| 1 | SORT UNIQUE | | 29 | 1218 | 12 (50)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 15 | 630 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 14 | 588 | 6 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
orclz> select ename,dname from emp full outer join dept using(deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 51889263
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 240 | 6 (0)| 00:00:0
| 1 | VIEW | VW_FOJ_0 | 15 | 240 | 6 (0)| 00:00:0
|* 2 | HASH JOIN FULL OUTER| | 15 | 630 | 6 (0)| 00:00:0
| 3 | TABLE ACCESS FULL | DEPT | 4 | 88 | 3 (0)| 00:00:0
| 4 | TABLE ACCESS FULL | EMP | 14 | 280 | 3 (0)| 00:00:0
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
orclz> |
|
|
|
| Re: Use ANSI standard JOIN syntax [message #598050 is a reply to message #598044] |
Thu, 10 October 2013 02:43 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
pablolee wrote on Thu, 10 October 2013 09:21Michel Cadot wrote on Thu, 10 October 2013 08:10
Quote:I'm also fairly certain that the Oracle syntax has one or two restrictions that the ANSI syntax does not
The first example is FULL OUTER JOIN which does not exist in Oracle syntax.
Hmm, but that can, of course, be achieved by unioning a left and a right join (which does exactly the same amount of work (but with MUCH more code) so I'm not sure that I'd agree that that is a restriction as such.
I would point out, that I could very well be 'mis-remembering' (I'm getting on a bit you know  )
Yes, hopefully you can always have another SQL syntax otherwise Oracle would not be complete regarding SQL standard.
Another example is when you have an OR in the outer condition:
drop table t1;
drop table t2;
create table t1 (col1a int, col1b int, col1c int);
create table t2 (col2a int, col2b int);
insert into t1 values (1, 2, 3);
insert into t1 values (1, 2, 4);
insert into t1 values (2, 3, 4);
insert into t1 values (3, 3, 4);
insert into t1 values (3, 3, 5);
insert into t1 values (2, 4, 5);
insert into t2 values (1, 2);
insert into t2 values (3, 5);
commit;
SQL> select *
2 from t1 left outer join t2
3 on t1.col1a = t2.col2a or t1.col1b = t2.col2b
4 /
COL1A COL1B COL1C COL2A COL2B
---------- ---------- ---------- ---------- ----------
1 2 3 1 2
1 2 4 1 2
2 3 4
3 3 4 3 5
3 3 5 3 5
2 4 5
6 rows selected.
SQL> select *
2 from t1, T2
3 where t1.col1a = t2.col2a (+)
4 or t1.col1b = t2.col2b (+)
5 /
or t1.col1b = t2.col2b (+)
*
ERROR at line 4:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
|
|
|
|
| Re: Use ANSI standard JOIN syntax [message #598051 is a reply to message #598050] |
Thu, 10 October 2013 02:45 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Quote:Another example is when you have an OR in the outer condition:
I think that's the one I was thinking of (IIRC I saw it posted on OTN - not even that long ago!). Cheers Michel
|
|
|
|
|
|
| Re: Use ANSI standard JOIN syntax [message #598053 is a reply to message #598048] |
Thu, 10 October 2013 02:55 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
John, very interesting. I was always under the impression that oracle did the same amount of work either way (more weight to the ANSI argument - if it was ever needed). Thanks for this.
|
|
|
|
| Re: Use ANSI standard JOIN syntax [message #598059 is a reply to message #598048] |
Thu, 10 October 2013 03:42 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
John Watson wrote on Thu, 10 October 2013 13:09I'm getting different performance characteristics (12.1.0.1), which I wasn't expecting
John,
As pablolee said, even I always thought that it takes the same amount of work in either of the syntax. However, quite surprisingly it is not.
I don't have version 12 at my work right now, but I could test it in version 11, and I have seen this first time -
SQL> SELECT * FROM v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> EXPLAIN PLAN FOR SELECT ENAME, DNAME FROM EMP FULL OUTER JOIN DEPT USING (DEPTNO)
2 /
Explained
SQL> SELECT * FROM table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2779170970
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 56 | 31 (4)| 00:00:01
| 1 | VIEW | VW_FOJ_0 | 4 | 56 | 31 (4)| 00:00:01
|* 2 | HASH JOIN FULL OUTER| | 4 | 160 | 31 (4)| 00:00:01
| 3 | TABLE ACCESS FULL | DEPT | 3 | 60 | 15 (0)| 00:00:01
| 4 | TABLE ACCESS FULL | EMP | 4 | 80 | 15 (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
20 rows selected
SQL> EXPLAIN PLAN FOR SELECT ENAME, DNAME
2 FROM EMP, DEPT
3 WHERE EMP.DEPTNO(+) = DEPT.DEPTNO
4 UNION
5 SELECT ENAME, DNAME
6 FROM EMP, DEPT
7 WHERE EMP.DEPTNO = DEPT.DEPTNO(+)
8 /
Explained
SQL> SELECT * FROM table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1800384560
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 320 | 63 (53)| 00:00:01 |
| 1 | SORT UNIQUE | | 8 | 320 | 63 (53)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 4 | 160 | 31 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT | 3 | 60 | 15 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 4 | 80 | 15 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 4 | 160 | 31 (4)| 00:00:01 |
| 7 | TABLE ACCESS FULL| EMP | 4 | 80 | 15 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| DEPT | 3 | 60 | 15 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
Note
-----
- dynamic sampling used for this statement (level=2)
25 rows selected
I make a small change in the query, and -
SQL> EXPLAIN PLAN FOR SELECT ENAME, DNAME
2 FROM EMP, DEPT
3 WHERE EMP.DEPTNO(+) = DEPT.DEPTNO
4 UNION ALL
5 SELECT ENAME, DNAME
6 FROM EMP, DEPT
7 WHERE EMP.DEPTNO = DEPT.DEPTNO(+)
8 AND EMP.DEPTNO IS NULL
9 /
Explained
SQL> SELECT * FROM table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 912395105
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 200 | 61 (51)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN OUTER | | 4 | 160 | 31 (4)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 3 | 60 | 15 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 4 | 80 | 15 (0)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 1 | 40 | 31 (4)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| EMP | 1 | 20 | 15 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| DEPT | 3 | 60 | 15 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - filter("EMP"."DEPTNO" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
25 rows selected
Regards,
Lalit
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 05:16:48 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
