Re: different SQL approaches

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 21 Oct 2004 18:47:53 -0700
Message-ID: <1098409620.276960@yasure>

Andy Hassall wrote:

> On Sat, 16 Oct 2004 22:07:50 -0700, DA Morgan <damorgan_at_x.washington.edu>
> wrote:
>
>

>>AK wrote:
>>
>>
>>>not nesessarily true if there is data skew or range queries.
>>>for instance, considering a table of Illinois customers,
>>>WHERE CITY = 'CHICAGO' would require scanning a table (>50% ROWS),
>>>while
>>>WHERE CITY = 'LISLE' is better off using an index (<0.2% rows)
>>>a generic plan WHERE CITY = :city, will use an index (there are
>>>hundreds or even thousands of cities), the price of running it for
>>>Chicago is quite high, as scanning the whole table with prefetching is
>>>way more efficient than accessing the whole table via the index.
>>>
>>>I'm not discussing cluster factors here just to keep thing simple
>>
>>While your example is correct if you think the solution is to use string
>>literals rather than histograms I would suggest you are using a hammer
>>as a screw driver.

>
>
> ... and missing the point of the thread, of course. One of the few times when
> you would prefer literals over bind variables is precisely _because_ you have
> histograms, and you want CBO to be able to make decisions based on the value
> being queried, and its distribution within the data set to work out its
> selectivity - and where between two invocations with different values, it's
> actually appropriate to have two very different execution plans, as in the demo
> in the asktom URL posted in my original reply and what AK wrote.

You are correct.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Thu Oct 21 2004 - 20:47:53 CDT