Re: SQL performance - optimizer question

In article <CKG7E3.Dro_at_uk.ac.brookes>, p0070621_at_oxford-brookes.ac.uk (Tommy Wareing) writes:
>
>
> Because it's in an OR construction! It's that simple.
>
> I'm probably way off track here, but it the optimizer stupid enough to
> speed things up if you write
> where (:key1 is null or :key1 = key1)
> etc?
>

I have tried that as well, the optomizer does not care when it comes to or construnctions. Here is a possible workaround:

select ... from ...
where key1=:key1 and key2=:key2 and key3=:key3 union select ... from ...
where key1=:key1 and key2=:key2 and :key3 is null union select ... from ...
where key1=:key1 and :key2 is null and key3=:key3 union select ... from ...
where :key1 is null and key2=:key2 and key3=:key3 union select ... from ...
where key1=:key1 and :key2 is null and :key3 is null union select ... from ...
where :key1 is null and :key2 is null and key3=:key3 union select ... from ...
where :key1 is null and :key2=key2 and :key3 is null union select ... from ...
where :key1 is null and :key2 is null and :key3 is null;

I tested it with number of keys=1 and it performed quickly. The size of the select statement grows exponentially compared to the number of columns being matched. Received on Sun Jan 30 1994 - 20:42:54 CET