| Table Order in Join [message #674657] |
Thu, 07 February 2019 09:27  |
 |
rrcr
Messages: 18
Registered: January 2019
|
Junior Member |
|
|
Hi ,
I have two doubts.
1. Order of tables in Join
select * from A,B,C
where A.C1=B.C1
AND B.C1=C.C1;
Vs
select * from C,B,A
where C.C1=B.C1
AND B.C1=A.C1;
with table order is there any performance differences if so which one is better?
2. Join Syntax
select * from A,B
WHERE A.C1=B.C1;
VS
SELECT * FROM A
INNER JOIN B
ON A.C1=B.C1;
these two have performace differences if so which one is better?
Thanks
|
|
|
|
|
|
|
|
| Re: Table Order in Join [message #674661 is a reply to message #674657] |
Thu, 07 February 2019 10:21  |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If statistics are gathered for the tables (or dynamic sampling is on which is the default), order does not matter.
This is the same thing if you use Oracle or ANSI syntax (except that some cases can be written in ANSI and can't be in Oracle one).
[Updated on: Thu, 07 February 2019 10:23] Report message to a moderator |
|
|
|
| Re: Table Order in Join [message #674662 is a reply to message #674661] |
Thu, 07 February 2019 10:25 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Also a remind from your previous topic:
Michel Cadot wrote on Sun, 27 January 2019 17:16
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
John Watson wrote on Mon, 28 January 2019 09:09So you haven't tested properly, have you. I would guess that you haven't checked out the execution plans either. You do need to do a bit of investigation, you know. For example,orclx>
orclx> set autot trace exp
orclx> select distinct d.* from dept d join emp e on (e.deptno=d.deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 3764232723
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 7 (29)| 00:00:01 |
| 1 | HASH UNIQUE | | 3 | 69 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN SEMI | | 3 | 69 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
orclx>
[Updated on: Thu, 07 February 2019 10:25] Report message to a moderator |
|
|
|
| Re: Table Order in Join [message #674663 is a reply to message #674657] |
Thu, 07 February 2019 11:03 |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Try this in SQL*Plus, and all should become clear:connect scott/tiger
set autotrace traceonly explain
select * from emp natural join dept;
select * from dept,emp where dept.deptno=emp.deptno; |
|
|
|
] 
Search
Help
Members
Register
Login
Home
