this is a candidate for oracle WTF but radio silence

[John Hurley]

One of my developers has this coded in a single table select they are
using thru sql developer:

SKU is ( inventory part number ... VARCHAR2(20) )

SELECT sku,
site_id,
site_and_sku,
qty_oh,
qty_demand
FROM schema_name.table_name
WHERE qty_oh > 0
AND (qty_oh - qty_demand < 5)
AND qty_demand < qty_oh
AND sku > 'WLD-'
AND SKU < 'WLE-'
AND sku = 'WLD-559-5415'
ORDER BY SKU

[Mladen Gogala]

On Tue, 09 Apr 2013 08:48:56 -0700, John Hurley wrote:

> AND sku > 'WLD-'
> AND SKU < 'WLE-'
> AND sku = 'WLD-559-5415'

These conditions are very interesting. I love the way your developer is
thinking. Of course, he should also learn how to use the formatting tool
available within SQL*Developer, so that the case of the column names is
consistent within the SQL.



--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

[Mladen Gogala]

On Tue, 09 Apr 2013 17:18:53 +0000, Mladen Gogala wrote:

> On Tue, 09 Apr 2013 08:48:56 -0700, John Hurley wrote:
>
>> AND sku > 'WLD-'
>> AND SKU < 'WLE-'
>> AND sku = 'WLD-559-5415'
>
> These conditions are very interesting. I love the way your developer is
> thinking. Of course, he should also learn how to use the formatting tool
> available within SQL*Developer, so that the case of the column names is
> consistent within the SQL.

This can be compared with artillery: one shell falls short, the other
goes over the target and the third one hits the target. You, as the DBA,
must be delighted by such a spectacular barrage.

[Mladen Gogala]

On Tue, 09 Apr 2013 17:18:53 +0000, Mladen Gogala wrote:

> On Tue, 09 Apr 2013 08:48:56 -0700, John Hurley wrote:
>
>> AND sku > 'WLD-'
>> AND SKU < 'WLE-'
>> AND sku = 'WLD-559-5415'
>
> These conditions are very interesting. I love the way your developer is
> thinking. Of course, he should also learn how to use the formatting tool
> available within SQL*Developer, so that the case of the column names is
> consistent within the SQL.

Of course, the "ORDER BY" is very useful, too. Just in case.

[joel garry]

On Apr 9, 10:28 am, Mladen Gogala <gogala.mla...@gmail.com> wrote:
> On Tue, 09 Apr 2013 17:18:53 +0000, Mladen Gogala wrote:
> > On Tue, 09 Apr 2013 08:48:56 -0700, John Hurley 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..."

jg
--
@home.com is bogus.
Scaled down Big Data.
http://www.pcworld.com/article/2033659/oracle-rolls-out-new-inmemory-applications-scaleddown-big-data-appliance.html

[Mladen Gogala]

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?