Home » SQL & PL/SQL » SQL & PL/SQL » which "AND" get priority when statement is executed?
which "AND" get priority when statement is executed? [message #276854] Fri, 26 October 2007 08:19 Go to next message
samrat563n
Messages: 1
Registered: October 2007
Location: india
Junior Member
Suppose there are 3 "AND" operator in WHERE condition. Then tell me which "AND" will get prioritized first.


STATEMENT:---
SELECT *
FROM employees
WHERE salary>10000 AND job_id LIKE '%MAN' AND commission_pct>=.3;

Normally we says it will executes from LEFT to RIGHT, and TOP to BOTTOM When operators on same priority.

BUT experts told me that last "AND" get first priority and not the first "AND". And they have told me that they can prove this.

Please tell me how right "AND" get priority first.(with proof)
Re: which "AND" get priority when statement is executed? [message #276855 is a reply to message #276854] Fri, 26 October 2007 08:26 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
samrat563n wrote on Fri, 26 October 2007 09:19


Please tell me how right "AND" get priority first.(with proof)



For proof, I would run an explain plan, an autotrace or tkprof and you'll see the execution plan of the query.

If the Cost-Based Optimizer, Oracle will decide which condition to use first.
Re: which "AND" get priority when statement is executed? [message #276856 is a reply to message #276854] Fri, 26 October 2007 08:27 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

And they have told me that they can prove this.
Then tell them to do so. Feel free to post that 'proof' here.
Re: which "AND" get priority when statement is executed? [message #276861 is a reply to message #276854] Fri, 26 October 2007 08:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
samrat563n wrote on Fri, 26 October 2007 15:19


Normally we says it will executes from LEFT to RIGHT, and TOP to BOTTOM When operators on same priority.


Normally we say that we don't care. We let the optimizer choose what's best.
Re: which "AND" get priority when statement is executed? [message #276863 is a reply to message #276861] Fri, 26 October 2007 09:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I think long long ago Razz , in RBO's time, it used to do that.
1. Considering last table in FROM clause as the driving table.
2. Considering filters in where clause from LEFT to RIGHT and TOP to BOTTOM.

Some link....

Now a days in CBO, as Frank's post, it depends.

By
Vamsi

[Updated on: Fri, 26 October 2007 09:35]

Report message to a moderator

Re: which "AND" get priority when statement is executed? [message #276870 is a reply to message #276863] Fri, 26 October 2007 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And this was already wrong in RBO.

Regards
Michel
Re: which "AND" get priority when statement is executed? [message #276906 is a reply to message #276854] Fri, 26 October 2007 14:12 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
your experts are using old rules.

It was true in the old days of the RBO that there were basic rules of thumb that could be applied to this question. But...

These were rules of thumb. That means each rule of thumb always had exceptions. The basics that I remember went like this:

At any given moment, only some subset of conditions in the where clause are available for evaluation. Thus this question can can only be considered in the context of each instantaneous state of an executing query. overall, the issues to consider were:

row selection first
---------------------------
1) order of table accesses
2) index usage and join method to each table

row filtering second
---------------------------
3) given what ever is left to evaluate after the above, from bottom up

There was even a guy who presented at an oracle conference I attended (in the late 80's I think it was) who showed how one could drastically alter the cost of a query by changing the order of predicates in the where clause such that row filtering (not row selection), was done more effciently by allowing for a minimum number of condition evaluations.

But these days the CBO will figure it out if you give him the stats he needs. This question was at one time a neat footnote, nothing more, and is now pretty much immaterial. It would have to be some real obscure situation I would think where you would care. I can't think of such a situation. Maybe you have found one? I am curious, why do you ask?

Hope this helps.

Kevin
Previous Topic: Getting the SCN as a variable
Next Topic: Replacing script in oracle database
Goto Forum:
  


Current Time: Sun Dec 04 20:53:56 CST 2016

Total time taken to generate the page: 0.09993 seconds