Re: different SQL approaches

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.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool