Re: this is a candidate for oracle WTF but radio silence
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 10 Apr 2013 07:29:25 +0000 (UTC)
Message-ID: <pan.2013.04.10.07.29.25_at_gmail.com>
On Tue, 09 Apr 2013 15:56:11 -0700, joel garry wrote:
>
> That one is perhaps reasonable, if there are degenerate sku's (prefixes
> with no numbers attached, I also see that on my systems with
> "intelligent" overlays of generic vendor keys). But of course it all
> becomes silly with an exact match predicate. Even so, and including the
> order by, I've seen it be reasonable in a query generator, just some
> subset of the generator output is silly. More often I see "and 1=1 and
> 1=1 and 1=1..."
Date: Wed, 10 Apr 2013 07:29:25 +0000 (UTC)
Message-ID: <pan.2013.04.10.07.29.25_at_gmail.com>
On Tue, 09 Apr 2013 15:56:11 -0700, joel garry wrote:
>> >> AND sku > 'WLD-'
>
> That one is perhaps reasonable, if there are degenerate sku's (prefixes
> with no numbers attached, I also see that on my systems with
> "intelligent" overlays of generic vendor keys). But of course it all
> becomes silly with an exact match predicate. Even so, and including the
> order by, I've seen it be reasonable in a query generator, just some
> subset of the generator output is silly. More often I see "and 1=1 and
> 1=1 and 1=1..."
The difference between "1 = 1" and this is in the fact that CBO ignores
"1=1" expression, since there is nothing to do. This is completely
redundant, valid condition which will cause CBO to use range scan or the
full scan, depending on statistics and the availability of histograms.
Furthermore, the rationale for "WHERE 1 = 1" is to generate the query
conditions prefixed by AND or OR operators. In John's query, the
expression you mentioned is in the middle of the query, without any
formatting purpose.
The ORDER BY SKU clause is very useful having in mind the
AND sku = 'WLD-559-5415'
condition. I wonder how many different values of sku will that return, to sort routine?
-- Mladen Gogala The Oracle Whisperer http://mgogala.byethost5.comReceived on Wed Apr 10 2013 - 09:29:25 CEST