Re: Improving performance of a query

On Tue, 6 Sep 2005, Merlin Moncure wrote:

> Carlos wrote:
> SELECT * FROM SSIRRA where
> (YEAR = 2004 and CUSTOMER = 0000000004 and CODE = 00 and PART >= 00) or
> (YEAR = 2004 and CUSTOMER = 0000000004 and CODE > 00) or
> (YEAR = 2004 and CUSTOMER > 0000000004) or
> (YEAR > 2004)
> [snip]
>
> ah, the positional query.  You can always rewrite this query in the
> following form:
>
> (YEAR >= 2004) and
> (YEAR = 2004 or CUSTOMER >= 0000000004) and
> (YEAR = 2004 or CUSTOMER = 0000000004 or CODE >= 00) and
> (YEAR = 2004 or CUSTOMER = 0000000004 or CODE = 00 or PART > 00)

Unless I'm not seeing something, I don't think that's a correct
reformulation in general. If customer < 4 and year > 2004 the original
clause would return true but the reformulation would return false since
(year=2004 or customer >= 4) would be false.