"Oracle support of ANSI joins.", version 9.2
pete moss, November 03, 2003 - 3:15 am UTC
Tom - totally agree with you about natural joins, a really stupid idea!
A very important fact to note for anyone who is contemplating using ANSI join syntax in a new application is to note the fact that forms 9i does NOT support the new syntax! We made the assumption that the new release of forms would support the new features of the database only to discover, to the setback of our project, that it will not compile ANSI style joins - crazy I know, and we had trouble getting oracle support to believe us on this - but trust me on this, we now have a proper bug number and everything ;)
pete :)
But surely
Fred Funk, November 03, 2003 - 7:32 am UTC
"to the setback of our project"
You would have noticed this the first time you attempted to compile the form !
Forms is always a few steps behind in the versions of PL/SQL - it would be almost impossible for it to keep up.
well, yes!
pete moss, November 03, 2003 - 8:17 am UTC
As you point out, we did spot this straight away - but we had been planning all along to use ANSI style joins (going so far as getting training for developers who did not know the ansi syntax!), which meant that we had to start developing our system knowing that we will, at some point - once it is supported, go back and retro-code loads of queries into that style, if we want to have all sql through the system to be consistent. which is a pain, to say the least!
hey ho.
November 03, 2003 - 2:18 pm UTC
well, i'm not going back and changing my decodes to case, nor would I ever rewrite the old style joins to ansi. but anyway.
in forms however, how often do you really join? and why isn't this code in STORED PROCEDURES (if it is doing data stuff)? forms is pretty much "default block" stuff or built on stored procedures....
and the difference between:
from emp inner join dept on (emp.deptno = dept.deptno)
vs
from emp, dept where emp.deptno = dept.deptno
hopefully didn't take a fully day or training?
Forms SQL and PL/SQL
Gary, November 03, 2003 - 7:08 pm UTC
Forms is quite happy with ANSI joins as part of the definition of a block based on a where clause or a record group.
The issue is with Forms' PL/SQL.
I'm using Forms 6i, which states "This release of Form Builder uses PL/SQL 8 on the client and the server."
But Oracle version numbering makes it difficult to work out what equates to what.
Stick to either procedures in the database, or use dynamic SQL in the forms. That way you can always gain the advantage of migration to later database versions without forcing a migration of your forms versions.
PS. I also agree that NATURAL joins are a mistake. Just have those often standard 'CREATED_BY', 'CREATED_ON' columns on your tables and watch the lack of results.
Maybe we could petition Oracle for a parameter for turning it off.
ANSI reference
A reader, March 28, 2007 - 5:14 pm UTC
Hi Tom,
My apologies if I submitted the question again, as there were some interruption when I pressed "Submit Review" button.
Is there any complete reference book available to know which SQL command/syntax is ANSI standard? For example, I though MINUS will be an ANSI standard, but I came to know that actually it is not :-)
Regards,
March 30, 2007 - 11:45 am UTC
you can order the ansi standards from ansi
http://ansi.org/ however, they are rather "dry" and somewhat meaningless.
Buggy?
Allan, February 19, 2008 - 11:18 am UTC
Historically there have been bugs related to ANSI syntax, in fact even the 10.2.0.4 projected issues list includes 10 bugs/issues related to ANSI syntax.
In the past I've encountered some of these bugs myself, and have continued to use and advocate the "traditional" Oracle style.
I'd like to know if you feel that the implementation of ANSI syntax is now equally robust compared to the traditional syntax.
February 19, 2008 - 5:06 pm UTC
unfortunately, there are bugs in non-ansi joins too, probably more than 10 in fact.
I personally do not use the new syntax (except in the rare case of a full outer join, a truly rare beast to encounter). I have no comment on it really.
ANSI syntax useful for outer joins
A reader, February 19, 2008 - 11:49 pm UTC
I've found the ANSI syntax useful for outer joins, in that it allows a table to be outer joined to multiple other tables, whereas the join operator (+) does not.
February 20, 2008 - 8:18 am UTC
inline views - i like them for that myself.
ANSI SQL Join in 10.2.0.1 and 10.2.0.4
Rajeshwaran, Jeyabal, January 18, 2011 - 7:35 am UTC
Results from 10.2.0.1.0 patchsets.
rajesh@10GR2.1> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Elapsed: 00:00:00.03
rajesh@10GR2.1>
rajesh@10GR2.1>
rajesh@10GR2.1> set autotrace traceonly explain;
rajesh@10GR2.1> select ename
2 from emp e,dept d
3 where e.deptno = d.deptno;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 126 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
rajesh@10GR2.1>
rajesh@10GR2.1> select ename
2 from emp e join dept d
3 on (e.deptno = d.deptno);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4269077325
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 168 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPTNO"="D"."DEPTNO")
rajesh@10GR2.1>
rajesh@10GR2.1> set autotrace off;
rajesh@10GR2.1>Results from 10.2.0.4.0 patchsets.
rajesh@10GR2.4> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Elapsed: 00:00:00.76
rajesh@10GR2.4>
rajesh@10GR2.4>
rajesh@10GR2.4> set autotrace traceonly explain;
rajesh@10GR2.4> select ename
2 from emp e,dept d
3 where e.deptno = d.deptno;
Elapsed: 00:00:00.50
Execution Plan
----------------------------------------------------------
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 126 | 5 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 126 | 5 |
----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
rajesh@10GR2.4>
rajesh@10GR2.4> select ename
2 from emp e join dept d
3 on (e.deptno = d.deptno);
Elapsed: 00:00:00.50
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 5 |
| 1 | NESTED LOOPS | | 14 | 168 | 5 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 5 |
| 3 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 3 | 0 |
--------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
rajesh@10GR2.4>
rajesh@10GR2.4> set autotrace off;
rajesh@10GR2.4>
Tom:
Is that ANSI SQL's to be avoided in Oracle database 10g?