Skip to Main Content
Ask TOM

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Tony.

Asked: November 02, 2003 - 2:26 pm UTC

Last updated: February 20, 2008 - 8:18 am UTC

Version: 9.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,
While looking into the documents of 9i, I found that Oracle now supports ANSI joins. Since Oracle optimizer uses where clause to draw the execution plan (cost etc.), how it does with new natural joins? Is it just another layer on the top of existing Oracle's way of joins? I was wondering that if this not the case, then did Oracle change its Kernel in 9i to accommodate this. I am curious to know more details about this. Any help in this regard will be highly appreciated.
Regards,

and Tom said...



natural joins -- a bug waiting to happen -- are done exactly the same way as they were when you specified the join conditions yourself. There is no difference there, just the syntax is different. We look up in both tables for all of the common column names and build the where clause for you


scott@ORA920> select ename, dname from emp natural join dept;

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

14 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=14 Bytes=252)
1 0 HASH JOIN (Cost=6 Card=14 Bytes=252)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=4 Bytes=44)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=14 Bytes=98)



scott@ORA920> select ename, dname from emp, dept
2 where emp.deptno = dept.deptno;

ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING

14 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=14 Bytes=252)
1 0 HASH JOIN (Cost=6 Card=14 Bytes=252)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=3 Card=4 Bytes=44)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=14 Bytes=98)



i strongly, vehemently strongly, discourage the use of this "feature" called natural joins

to join tables by their colum names -- how patently "just a bad idea". We've bugs logged saying "hey, this natural join returns the wrong answer" -- but wait -- they were comparing it to what they THOUGHT should have been the query, not with what the query really way. Consider a model like this:


create table emp ( empno, deptno, ename );
create table dept ( deptno, dname, empno /* person responsible for this dept */ );


now, generate a list of enames and the dname they work in using a natural join, whoops.


Never never never use this "feature"



Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

"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.

Tom Kyte
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,
Tom Kyte
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.
Tom Kyte
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.
Tom Kyte
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?

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

AltStyle によって変換されたページ (->オリジナル) /