Home » SQL & PL/SQL » SQL & PL/SQL » How does oracle run sql
How does oracle run sql [message #386403] Sat, 14 February 2009 09:46 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi Guys,

I have 2 questions

First
I just wanted to understand how oracle executes sql, i read some article it says refer to explain plan, but explain plan dont tell us in which sequence the statements is executed.

For instance i have the following query

select a.col1, b.col3, c.col4
from t1 a, t2 b, t3 c
WHERE a.col1 = c.col1(+)
AND a.col1 = c.col1(+)


Im assuming it executes the where clause first, then does the outer join, but anyway, does it matter if its executed in other sequence? For the case above, it will lead us to the same output if the and clause is executed before where clause.

Second
And how do we find which is the driving table in the case of a long query. Because some queries seems to have outer/inner join all through the sql, columns are being selected from everywhere, how do we actually identify which is the driving table?
Re: How does oracle run sql [message #386404 is a reply to message #386403] Sat, 14 February 2009 09:53 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

When all else fail Read The Fine Manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#sthref956
Re: How does oracle run sql [message #386406 is a reply to message #386404] Sat, 14 February 2009 09:58 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thanks for the link, i will take note of the posting guidelines in future
Re: How does oracle run sql [message #386408 is a reply to message #386403] Sat, 14 February 2009 10:10 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
ajitpal.s wrote on Sat, 14 February 2009 16:46
First
I just wanted to understand how oracle executes sql, i read some article it says refer to explain plan, but explain plan dont tell us in which sequence the statements is executed.

For instance i have the following query

select a.col1, b.col3, c.col4
from t1 a, t2 b, t3 c
WHERE a.col1 = c.col1(+)
AND a.col1 = c.col1(+)


Im assuming it executes the where clause first, then does the outer join, but anyway, does it matter if its executed in other sequence? For the case above, it will lead us to the same output if the and clause is executed before where clause.

As the WHERE clause does not contain any filtering condition (it is only the join condition - maybe by mistake twice the same, so table T2 is not joined), there is no separate "executing" of the WHERE clause - it is used in the join(s).
ajitpal.s wrote on Sat, 14 February 2009 16:46
Second
And how do we find which is the driving table in the case of a long query. Because some queries seems to have outer/inner join all through the sql, columns are being selected from everywhere, how do we actually identify which is the driving table?

The order of tables in the explain plan determines the order of their execution. By the way, there are more types of join (HASH, NESTED LOOPS, ...), and they use different methods.

I do not know, which article you read, but I recommend you to read Oracle Performance Tuning Guide, available with all Oracle documentation e.g. online on http://tahiti.oracle.com/.
Previous Topic: ORA-29279: SMTP permanent error: 553
Next Topic: 22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows)
Goto Forum:
  


Current Time: Fri Dec 02 22:47:28 CST 2016

Total time taken to generate the page: 0.05470 seconds