Re: this is a candidate for oracle WTF but radio silence

From: Mladen Gogala <>
Date: Wed, 10 Apr 2013 07:29:25 +0000 (UTC)
Message-ID: <>

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
Received on Wed Apr 10 2013 - 09:29:25 CEST

Original text of this message