Re: different SQL approaches

AK wrote:

>>Lets say, for example, you have a resport that returns the names of all
>>active accounts. Is it reasonable to write:
>>
>>WHERE account_status = 'A'?
>>
>>I would argue that it is not. Because even if your specific query does
>>not look at inactive accounts there is undoubtedly some other query that
>>does or will be in the not too distant future.
>>
>>Thus the clause:
>>
>>WHERE account_status = x;
>>
>>Makes more sense unless you feel somehow imposed upon by being forced
>>to declare a variable.

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

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