ASE 15 WHERE Clause / OR / Index

[Former Member]

ASE 15.x

I have a problem maybe someone can address for me.

I have many arguments coming into a procedure from a Powerbuilder 'Search' window. A search field argument that is not searched against sends in a NULL. The archive table that may be searched against has millions of rows.

My WHERE clause looks like this for 15 or so columns...

(@variable = NULL OR column = @variable)

I would expect the first part of the statement to resolve if @variable = NULL, but there are cases where I am getting a full table scan against the column. This is happening even though the column has an index, but I did not want it to be used in the first place.

Any thoughts on why this happens, and how I can prevent it?I know it is related to the NULL piece because if I remove it, the query does not hit the unused fields WHERE clause row. (Does that make sense?)

Jason Lipman

[simon_ogden]

This is a common problem and to resolve it I think you'll need to split your code out to account for the NULL case.

Firstly remember that it isn't an exclusive OR so your logic with regard to the precedence isn't quite right. If @variable were NULL it would still need to evaluate either side of the OR.

The lack of exclusive OR is not the problem though, the problem is the optimizer has to create a single execution plan to satisfy the query. If you rewrote your query to something like:

where <col> = case when @variable is null then <col> else @a end

..then it might be easier to understand as this will behave more like an exclusive OR. This query will still not pick a filtering index scan on <col> though. The reason being that if the @variable were NULL then the query in essence has no search argument for <col> and with no search argument it will not consider a filtering index.

What you are incorrectly expecting is that a single filtering index scan plan can satisfy all these queries:

where <col> = <col> --All rows will qualify

where @variable = NULL --All rows will qualify

where <col> = <a_value> --can lookup using the index key

To get the optimal filtering index scan for the NOT NULL case you need to split your code.

if (@variable is not null) --include other variables if needed.

select...where <col> = @variable

else

select...where

That way you'll get 2 plans. One for the NOT NULL case which will always use the filtering lookup index and another which will have a tablescan or full index scan to satisfy the NULL case.

[former_member89972]

If the stored procedure is under your control then I would suggest what can be called as "sanity checking of parameters" approach.

Look for non null values passed in to minimize the first set of selection into a temp table.

You can make the parameters values that map to index columns mandatory and non-null.

If index columns are not available for scan then you will need to have up-to-date histograms for the search columns -- with not null parameters. These are useful to minimize/narrow down the selection. Note that this needs some maintenance work to run update statistics so seek your DBA onboard 🙂

Once you get first set -- hopefully small -- of rows into a temp table, you can do further search operations on the temp table.

HTH

Avinash