Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Which of these selects is faster?

Re: Which of these selects is faster?

From: Mark Griffiths <mark.griffiths_at_greendown.demon.co.uk>
Date: Wed, 05 May 1999 11:01:08 GMT
Message-ID: <373021af.448527308@news.demon.co.uk>


On Mon, 05 Apr 1999 18:07:47 GMT, silveys_at_my-dejanews.com wrote:

I don't know whether anyone has already commented on this aspect earlier in this post, BUT, here goes anyway...

I believe that what you are looking for is a way to affect the way in which the query is executed to achieve faster response times. The way you do this is by using the optimiser to assist you. You must decide whether you want to use the Rule-based optimiser or the newer Cost-based optimiser.

What you are refering to in your discussion of how Oracle evaluates the SQL statements is Rule-based optimisation. I admit that documentation on the Rule-based optimiser is pretty scant (although Chapter 6 of Oracle Performance Tuning by Gurry and Corrigan from O'Reilly books does give some insight).

However, if you go the Cost-based route, by keeping statistics on the base-tables and using hints, you can affect the way in which the query plan is constructed and the statement executed. If you know more about the table than the optimiser, you may as well tell it how to do its job!! That way, you get the result you want. Again, information on exactly how to use hints is spread thinly through the Oracle documentation set. All you can do is to prototype and benchmark the alternatives, learning from your experiences.

I hope this helps.

Mark Griffiths

>Thanks for the response... though inconclusive, it is helpful in narrowing
>down some of the issues. Since I have had so much trouble getting this
>question answered (nobody seems to really know), I suspect only an Oracle
>engine architect would be able to give a solid explanation. It seems that
>Oracle hasn't attempted to contractualize this matter in documentation and
>nothing can be assumed without a public contract.
>
> Ed Prochak <prochak_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
>...
>> 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.
>
>Thanks for the insight. However, no matter what precisely makes the like
>operator slow, the issue is that some terms in a boolean expression really
>SHOULD be evaluated last... "like" operators often being the culprits here.
>The fact that like operators usually nullify indexing makes it even
>MORE important to avoid evaluating them when possible.
>
>In C and C++, there is a deterministic way of ordering compound boolean
>expressions so that performance can be optimized. But it sounds like
>(surprisingly!) Oracle has made no effort to aid the developer by defining
>a means of such optimization (whether automatic or manual).
>
>> 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^)
>
>The one other guy who responded to me said that he found Oracle 7 seems to
>always evaluate boolean expressions backwards (not ass-backwards, just the
>reverse-order kind of backwards). You also claim that Oracle is consistent
>in the order of evaluation but without documentation? Ugh, that's the worst
>possibility... That implies that there may be a simple deterministic order
>but that Oracle isn't bound to it because they haven't documented it
>anywhere. So if your select calls are ordered the wrong way, then they'd
>be slow every time. So without documentation, the programmer falls victim
>to the whimsy of software versioning... where every new upgrade might change
>the underlying engine so that your once optimized selects would suddenly
>become 10 times slower!
>
>Anyway, thanks for the help.
>
>I guess I'll just have to pass this issue on to Oracle as a suggestion for
>an improvement of their handy software ;^) I'm sure we'll see it in the
>next version... Maybe by Summer... perhaps.
>
>Scott
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed May 05 1999 - 06:01:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US