False condition in WHERE clause

[Former Member]

Hi

Recently I have faced with a strange problem. Developers of one of my customers came with a query like this:

SELECT *

FROM packages

WHERE packages.item_number = 427909 OR 427909 = 0

go

"packages" is a table, there is an index on item_number column and "packages.item_number = 427909" is a very much selective condition. However, adding of an apparently false condition "427909 = 0" causes the optimizer not to see the first condition at all, which results in an expensive table scan. The behavior is observed in both version 15.7 SP51 (via set option show_lio_costing) and 12.5.3 (dbcc traceon(302)). Removing of the apparently false condition solves the problem. My questions:

1. Is it an expected behavior? Why the optimizer stops seeing the first condition?

2. Is there any workaround that doesn't involve changing of a query? Developers will change the query, of course, but releasing of a new application version may take some time.

To illustrate the problem better, I pasted outputs of "set showplan on" and "set option show_lio_costing on" below.

Thanks in advance

Leonid Gvirtz

Beginning selection of qualifying indexes for table 'packages',

Estimating selectivity of index 'packages.idx_packages_item', indid 3

    scan selectivity 1, filter selectivity 1

    56533 rows, 330 pages

    Data Row Cluster Ratio 0.2954172

    Index Page Cluster Ratio 0.7847222

    Data Page Cluster Ratio 0.2359819

    using index prefetch (size 16K I/O)

    Large IO selected: The number of leaf pages qualified is > MIN_PREFETCH pages

    in index cache 'default data cache' (cacheid 0) with LRU replacement

    using table prefetch (size 16K I/O)

    Large IO selected: The number of leaf pages qualified is > MIN_PREFETCH pages

    in data cache 'default data cache' (cacheid 0) with LRU replacement

    Data Page LIO for 'idx_packages_item' on table 'packages' = 41797

Estimating selectivity for table 'packages'

    Table scan cost is 56533 rows, 6651 pages,

The table (Datarows) has 56533 rows, 6651 pages,

Data Page Cluster Ratio 0.9943289

    Search argument selectivity is 0.330035.

    using table prefetch (size 16K I/O)

    Large IO selected: The number of leaf pages qualified is > MIN_PREFETCH pages

    in data cache 'default data cache' (cacheid 0) with LRU replacement

The Cost Summary for best global plan:

FINAL PLAN ( total cost = 52661.9 😞

        lio=6976 pio=870 cpu=169599 tempdb=0

QUERY PLAN FOR STATEMENT 1 (at line 1).

Optimized using Serial Mode

    STEP 1

        The type of query is SELECT.

        2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)

       |

       |   |RESTRICT Operator (VA = 1)(0)(0)(0)(7)(0)

       |   |

       |   |   |SCAN Operator (VA = 0)

       |   |   |  FROM TABLE

       |   |   |  packages

       |   |   |  Table Scan.

       |   |   |  Forward Scan.

       |   |   |  Positioning at start of table.

       |   |   |  Using I/O Size 16 Kbytes for data pages.

       |   |   |  With LRU Buffer Replacement Strategy for data pages.

[Former Member]

Looks very odd...

What's the index idx_packages_item ?

It seems to think a scan of this index is expensive

Data Page LIO for 'idx_packages_item' on table 'packages' = 41797

Is item_number unique ?

I've tried it on a table and don't get the same results.

Perhaps worth looking at "sp_options show" ?

[Former Member]

Hi Mike

I have just succeeded to reproduce the problem on another customer site on ASE 15.7 SP60. See the output of "sp_options show" attached. You mentioned that you receive different results in your tests - could you share some details?

The index scan appears so expensive because the optimizer doesn't recognize the first condition and therefore assumes that a full index scan will be required, which is expensive. If I omit the second, always false condition then an index scan on item_number is used and the query returns very fast (4 LIO), as expected.

Also, if I replace the always false condition with another false condition related to the indexed column, such as " and packages.item_number = -1" (there are no such records for sure), then the optimizer recognizes both conditions, applies an OR optimization strategy and the query returns very fast.

Thanks

Leonid Gvirtz

sp_options_show.txt.zip

[Former Member]

What's the definition of the table ? And the number of rows.

Have you tried updating all the stats on the table (don't assume update statistics will work - I've had tables which required update all statistics)

[Former Member]

See full reproduction of the problem attached, you can try the same on your environment. You can use a regular table instead the temporary one, the result will be the same. Statistics on the table are up-to-date.

My test looks like this (with and without the second condition):

select id, name into #sysobjects from sysobjects

go

create unique index idx1 on #sysobjects(id)

go

dbcc traceon(3604)

go

set statement_cache off

go

set showplan on

go

set statistics io on

go

set statistics plancost on

go

set option show_lio_costing on

go

select * from #sysobjects

where id = 7 or 7 = 0

go

set showplan off

go

set option show_lio_costing off

go

set statistics io off

go

set statistics plancost off

go

Thanks

Leonid Gvirtz

sysobjects_reproduction.txt.zip

[Former Member]

Very interesting - I agree the optimiser is .... less than optimal.

I haven't seen this but then those of us from the Sybase 10, 11 & 12 days generally avoid ORs 😉

Hopefully SAP/Sybase will pick this up and improve the optimiser in the next release.