Home » RDBMS Server » Performance Tuning » Table Order in Join (11g)
Table Order in Join [message #674657] Thu, 07 February 2019 09:27 Go to next message
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 #674658 is a reply to message #674657] Thu, 07 February 2019 09:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) No
2) No
Re: Table Order in Join [message #674659 is a reply to message #674658] Thu, 07 February 2019 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Produce & compare EXPLAIN PLAN yourself for each of post SQL statements

EXPLAIN PLAN FOR <your SQL statement here>
SELECT * FROM table(dbms_xplan.display);
Re: Table Order in Join [message #674661 is a reply to message #674657] Thu, 07 February 2019 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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:09
So 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 Go to previous message
John Watson
Messages: 8922
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;
Previous Topic: RELY constraints
Next Topic: high buffers
Goto Forum:
  


Current Time: Thu Mar 28 18:35:17 CDT 2024