Which of these selects is faster?

I'm curious which of these two selects is faster?:

1. select * from dictators where murders > 100 and description like '%Hitler%'
2. select * from dictators where description like '%Hitler%' and murders > 100

Assuming no indexing, the issues here are a bit subtle...

1. Does the SQL compiler guarantee a certain order of evaluation?
2. Do compound boolean expressions stop evaluating once the outcome can't be changed?

For example, in expression 1 above, if the dictator in question has only murdered 95 people, there is no need to continue with the evaluation of the description field since we know at this point that the where clause can't possibly come up true for that particular tyrant. If the SQL implementation/server is smart enough to stop evaluation at that time, it saves having to execute the relatively slow "like" operation for that row.

In C/C++, the answers questions to a and b are "yes"... the ANSI standard guarantees that you can order your boolean expressions so that slow terms don't get evaluated once the expression's value is determined.

But is this necessarily the case for Oracle SQL? I use Oracle 7 and will be moving to 8i soon (probably), so these are the relevant systems for me. What I need to know is, if compound boolean expressions CAN be ordered in an optimized fashion by the programmer, is this a public contract or standard that can be relied upon from version to version?

Thanks a lot.

Scott

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Mar 30 1999 - 11:40:47 CST