Re: SQL performance - optimizer question

jl34778_at_corp02.d51.lilly.com wrote:
> I'm coming into this discussion late, so I apologize if this is a stupid
> question, but I'm confused.
 

> I'm confused with the clause
 

> where (key1 = :key1 or :key1 is null)
> and (key2 = :key2 or :key2 is null)
> and (key3 = :key3 or :key3 is null);
 

> specifically with the 'or :key1 is null' piece. That 'or' condition
> does not include any reference to a database object, so I would think
> that it is either true for all rows or false for all rows, depending
> on the static value of :key1.

Completely correct.

> I don't see how this would find null values of key1 when :key1 is null.

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?

--
  _________________________   ______________________________________
 /  Tommy Wareing          \ /  Whadda you mean it's all my fault?  \
|  p0070621_at_brookes.ac.uk   X   Deny everything, and if you can't:   |
 \  0865-483389            / \             Pass the Buck            /
  ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~