Re: Which of these selects is faster?

In article <7dr2au$r9r$1_at_nnrp1.dejanews.com>,   silveys_at_my-dejanews.com wrote:
>
>
> 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...
>
> a) Does the SQL compiler guarantee a certain order of evaluation?

No. Too many factors play into this, especially since the cost based optimizer is now the default.

> b) Do compound boolean expressions stop evaluating once the outcome
> can't be changed?

Probably.

>
> 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.

The slowness of the "like" operator isn't what will kill performance on these queries. Without indices, Oracle must read EVERY row from the table, a FULL SCAN. The cost of pattern matching once in memory is minimal compared to the cost of a disc read.

>
> 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.

regarding C/C++, true, as long as no side effects are involved you can rearrange terms fairly freely.

>
> 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?

Not that I know of.

But like I said, your worries of performance are focussed on the wrong thing. The problem with the "like" operator is not that it is slower than other operators. Its problem is that searching for patterns that begin with wildcards '%hitler%' force a full table scan, even when an index exists on that column.

Finally, while Oracle does not guarantee order of evaluation, it does seem to be consistant. But watch out, the order is not as you'd expect. 8^)

>
> Thanks a lot.
>
> Scott
>

Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 01 1999 - 08:39:00 CST