which execution sequence is maintained in where clause condition ? [message #438904] |
Tue, 12 January 2010 23:33  |
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 #438908 is a reply to message #438905] |
Wed, 13 January 2010 00:06   |
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 #438953 is a reply to message #438938] |
Wed, 13 January 2010 03:30   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
|
|
|