Systems Engineering and RDBMS

Conditional Where Clause

Posted by decipherinfosys on April 10, 2007

This is a very frequent occurence: We have seen clients using conditional case statements (isnull(), coalesce(), mathematical operators etc.).  Remember that the moment you start applying functions on the indexed columns, you are rendering the index useless – because the values stored in the index are not stored after the application of that function.  In Oracle, one can use the FBI (Function Based Index – search this site for FBI and you will get our other blog posts on the topic), in SQL Server and DB2 LUW, one can play with and get around this by using a computed/generated column and indexing it.  The one time that a conditional case statement in the where clause might be fine is if there is other selective filter criteria so that the optimizer can formulate the plan based on those selective indexes and then use this conditional case statement to further narrow down the result-set.

Here is an example of such a conditional where clause (SQL Server Syntax):

create table demo_case
(
col1    int                not null,
col2    int                not null
)
go

–populate the table now

declare @i int
set @i = 1

while (@i <=10000)
begin
insert into demo_case values (@i, @i * 10)
set @i = @i + 1
end

Let’s create the two unique indexes and gather stats on those:

–Use the index filegroup clauses in real scripts – this is just for a quick demo

create unique index demo_case_ind_1 on demo_case (col1);
create unique index demo_case_ind_2 on demo_case (col2);

update statistics demo_case with sample 100 percent, all;

–Now, do the conditional select:

select *
from demo_case
where (case when col1 in (1, 100, 1000, 100000) then 1
when col2 in (1000, 100000, 1000000) then 1
else 0
end) = 1

And here is the execution plan:

StmtText
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
|–Table Scan(OBJECT:([SVPROD20].[dbo].[demo_case]), WHERE:(CASE WHEN [SVPROD20].[dbo].[demo_case].[col1]=(100000) OR [SVPROD20].[dbo].[demo_case].[col1]=(1000) OR [SVPROD20].[dbo].[demo_case].[col1]=(100) OR [SVPROD20].[dbo].[demo_case].[col1]=(1) THEN (1) ELSE CASE WHEN [SVPROD20].[dbo].[demo_case].[col2]=(1000000) OR [SVPROD20].[dbo].[demo_case].[col2]=(100000) OR [SVPROD20].[dbo].[demo_case].[col2]=(1000) THEN (1) ELSE (0) END END=(1)))
And here is the non-conditional clause without the case statement in it…notice, how it uses index intersection by making an effective use of both the indexes.

select *
from demo_case
where col1 in (1, 100, 1000, 100000) or col2 in (1000, 100000, 1000000)

StmtText
—————————————————————————————————————————————————————————————————————————————————————————————————
|–Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|–Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|    |–Concatenation
|         |–Index Seek(OBJECT:([SVPROD20].[dbo].[demo_case].[demo_case_ind_1]), SEEK:([SVPROD20].[dbo].[demo_case].[col1]=(1) OR [SVPROD20].[dbo].[demo_case].[col1]=(100) OR [SVPROD20].[dbo].[demo_case].[col1]=(1000) OR [SVPROD20].[dbo].[demo_case].[col1]=(100000)) ORDERED FORWARD)
|         |–Index Seek(OBJECT:([SVPROD20].[dbo].[demo_case].[demo_case_ind_2]), SEEK:([SVPROD20].[dbo].[demo_case].[col2]=(1000) OR [SVPROD20].[dbo].[demo_case].[col2]=(100000) OR [SVPROD20].[dbo].[demo_case].[col2]=(1000000)) ORDERED FORWARD)
|–RID Lookup(OBJECT:([SVPROD20].[dbo].[demo_case]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Sorry, the comment form is closed at this time.

 
%d bloggers like this: