Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: different SQL approaches
> 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 Received on Sat Oct 16 2004 - 20:02:01 CDT