Home » SQL & PL/SQL » SQL & PL/SQL » which execution sequence is maintained in where clause condition ? (Gracle, 10.2.0.1 , windows )
which execution sequence is maintained in where clause condition ? [message #438904] Tue, 12 January 2010 23:33 Go to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Dears

This is a sample query
SELECT a.*, b.*
  FROM emp a, dept b
 WHERE a.deptno = b.deptno
   AND a.ename LIKE '%E%'
   AND a.mgr = '7839'
   AND b.loc LIKE 'D%';


I am in confusion about execute sequence of where clause condition .
Is it first search this a.deptno = b.deptno condition
or AND b.loc LIKE 'D%' this condition ?

Regards
Halim


Re: which execution sequence is maintained in where clause condition ? [message #438905 is a reply to message #438904] Tue, 12 January 2010 23:41 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Check for SQL Logical Operators - Order of Operators Precedence in SQL

Operator Precedence

More useful About SQL Conditions

-Rahul

[Updated on: Tue, 12 January 2010 23:53]

Report message to a moderator

Re: which execution sequence is maintained in where clause condition ? [message #438908 is a reply to message #438905] Wed, 13 January 2010 00:06 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Thanks,
But
I am confused for following situation...


SQL> ed
Wrote file afiedt.buf

  1  SELECT a.*, b.*
  2    FROM emp a, dept b
  3   WHERE a.deptno = b.deptno1  ----invalid identifier
  4     AND a.ename LIKE '%E%'
  5     AND a.mgr = '7839'
  6*    AND b.loc1 LIKE 'D%'  ----invalid identifier
SQL> /
   AND b.loc1 LIKE 'D%'
       *
ERROR at line 6:
ORA-00904: "B"."LOC1": invalid identifier


SQL>
SQL>
SQL> ed
Wrote file afiedt.buf

  1  SELECT a.*, b.*
  2    FROM emp a, dept b
  3   WHERE a.deptno = b.deptno
  4     AND a.ename1 LIKE '%E%'  ----invalid identifier
  5     AND a.mgr = '7839'
  6*    AND b.loc1 LIKE 'D%'  ----invalid identifier
SQL> /
   AND b.loc1 LIKE 'D%'
       *
ERROR at line 6:
ORA-00904: "B"."LOC1": invalid identifier


SQL> ed
Wrote file afiedt.buf

  1  SELECT a.*, b.*
  2    FROM emp a, dept b
  3   WHERE a.deptno = b.deptno1 ----invalid identifier
  4     AND a.ename1 LIKE '%E%' ----invalid identifier
  5     AND a.mgr = '7839'
  6*    AND b.loc1 LIKE 'D%'  ----invalid identifier
SQL> /
   AND b.loc1 LIKE 'D%'
       *
ERROR at line 6:
ORA-00904: "B"."LOC1": invalid identifier


SQL> ed
Wrote file afiedt.buf

  1  SELECT a.*, b.*
  2    FROM emp a, dept b
  3   WHERE  a.ename1 LIKE '%E%'  ----invalid identifier
  4     AND a.mgr = '7839'
  5     AND b.loc1 LIKE 'D%'  ----invalid identifier
  6*    and a.deptno = b.deptno1  ----invalid identifier
SQL> /
   and a.deptno = b.deptno1
                  *
ERROR at line 6:
ORA-00904: "B"."DEPTNO1": invalid identifier


SQL>



As I know that where condition is evaluate from begining to end .

Please Clarify me ?

Regards
Halim
Re: which execution sequence is maintained in where clause condition ? [message #438919 is a reply to message #438908] Wed, 13 January 2010 01:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
As I know that where condition is evaluate from begining to end .

Based on what information do you know this?
Re: which execution sequence is maintained in where clause condition ? [message #438920 is a reply to message #438908] Wed, 13 January 2010 01:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You are evaluating the PARSING process, not the execution order.
Re: which execution sequence is maintained in where clause condition ? [message #438924 is a reply to message #438919] Wed, 13 January 2010 01:26 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

pablolee wrote on Wed, 13 January 2010 13:12
Quote:
As I know that where condition is evaluate from begining to end .

Based on what information do you know this?


Ok, forget it .

please clarify me actually what happen .


Regards
Halim
Re: which execution sequence is maintained in where clause condition ? [message #438926 is a reply to message #438920] Wed, 13 January 2010 01:27 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Frank wrote on Wed, 13 January 2010 13:16
You are evaluating the PARSING process, not the execution order.


yes Frank, You are write.

regards
Halim
Re: which execution sequence is maintained in where clause condition ? [message #438931 is a reply to message #438926] Wed, 13 January 2010 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The syntax analysis part build a tree representing the query. Then the tree is evaluating from the last leaf to the root.
The last leaf is the last expression in where (in your example, it could be the expression in an ORDER BY clause if there was one...).
The tree is travelled up and down several times during Parsing.

Regards
Michel
Re: which execution sequence is maintained in where clause condition ? [message #438938 is a reply to message #438931] Wed, 13 January 2010 02:52 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Michel Many many thanks.

One more things..
when I write sql queries,
Should I write indexed columns condition or large Filtering columns condition first in where clause?

I mean, Has it any performance matter in queris ?

Again thanks for claring parse tree concept and reference.


Regards
Halim


Re: which execution sequence is maintained in where clause condition ? [message #438953 is a reply to message #438938] Wed, 13 January 2010 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Should I write indexed columns condition or large Filtering columns condition first in where clause?

It does not matter, Oracle CBO (Cost Based Optimizer) does not use the order of expression in WHERE but their estimated cost.

Quote:
I mean, Has it any performance matter in queris ?

The preference is then the one that best explains the purpose of your query (for a humain being).
Comments in queries are welcome

Regards
Michel

[Updated on: Wed, 13 January 2010 03:30]

Report message to a moderator

Re: which execution sequence is maintained in where clause condition ? [message #441558 is a reply to message #438953] Mon, 01 February 2010 22:45 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
which execution sequence is maintained in where clause condition ? (Gracle, 10.2.0.1 , windows )


I am unable to find this version....Any one? Wink

sriram Smile
Re: which execution sequence is maintained in where clause condition ? [message #441562 is a reply to message #441558] Mon, 01 February 2010 23:06 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

ramoradba wrote on Tue, 02 February 2010 10:45
Quote:
which execution sequence is maintained in where clause condition ? (Gracle, 10.2.0.1 , windows )


I am unable to find this version....Any one? Wink

sriram Smile


Its Oracle 10.2.0.1


SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 2 10:54:38 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> select * from v$version
  2  ;

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

SQL>



Its just mistake of typing.
As you know this is an Oracle forum and there is nothing like "Gracle" .
This is a sily matter, as I think.


Regards
Halim

Re: which execution sequence is maintained in where clause condition ? [message #441882 is a reply to message #438904] Wed, 03 February 2010 10:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Do you know what EXPALIN PLAN is?

If what you want to know what oracle thinks it will do (or is doing) then dump the plan for the query and it will show you the sequence of events it decided to use.

Understand however that oracle is a very very smart database compared to other databases. It has lots of different ways it can execute a query. This means that Oracle can and will change the way it executes the query at any time.

If you are asking us to tell you "how does it do it" then that is a discussion of the Oracle Optimizer in general and way beyond the scope of a simple post. You will have to do lots of reading on your own for that. Do a google on Oracle Cost Based Optimizer.

Good luck, Kevin
Re: which execution sequence is maintained in where clause condition ? [message #442680 is a reply to message #441882] Tue, 09 February 2010 23:23 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Thanks Kevin Meade.


SQL> SELECT a.*, b.*
  2    FROM emp a, dept b
  3   WHERE a.deptno = b.deptno
  4     AND a.ename LIKE '%E%'
  5     AND a.mgr = '7839'
  6     AND b.loc LIKE 'D%';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO     DEPTNO DNAME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -----
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20         20 RESEA


Execution Plan
----------------------------------------------------------
Plan hash value: 351108634

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    57 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    57 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | EMP     |     1 |    37 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."MGR"=7839 AND "A"."ENAME" LIKE '%E%')
   3 - filter("B"."LOC" LIKE 'D%')
   4 - access("A"."DEPTNO"="B"."DEPTNO")

SQL>



Have you any recommended link for this purpose?

Regards
Halim
Re: which execution sequence is maintained in where clause condition ? [message #442736 is a reply to message #438904] Wed, 10 February 2010 05:23 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Sorry, I do not have any favorite links for teaching database tuning. It is a big topic and takes a lot of effort to learn. The only thing I can suggest is you start with the oracle documentation and/or do some googling.

Maybe others have some input on where you can get started?

Kevin
Re: which execution sequence is maintained in where clause condition ? [message #442837 is a reply to message #442680] Wed, 10 February 2010 22:45 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>AND a.ename LIKE '%E%'
Precludes index use & forces a Full Table Scan.
In other words on large tables, this does not scale well.
Previous Topic: Count
Next Topic: How to fetch data from different database using single select statement
Goto Forum:
  


Current Time: Thu Dec 08 01:53:08 CST 2016

Total time taken to generate the page: 0.25064 seconds