Re: SQL performance - optimizer question

In article <MLOENNRO.94Jan28105342_at_demo1.se.oracle.com> Magnus Lonnroth, mloennro_at_se.oracle.com writes:
:: :select val1, val2
:: :from MYTABLE
:: :where rowid in ( select rowid from MYTABLE where key1 = :key1
:: : union
:: : select rowid from MYTABLE where key2 = :key2
:: : union
:: : select rowid from MYTABLE where key3 = :key3 )
::
:: Of course, you still need to add the original conditions as AND clauses
:: to the outermost SELECT. And if all three can be null at the same
 time,
:: then you'll need to UNION in that case.
:
:The first statement is not true. If at least one of the bind-variables
:is not null, the query should return the same rows as:
:
: select val1, val2
: from MYTABLE
: where (key1 = :key1 or :key1 is null)
: and (key2 = :key2 or :key2 is null)
: and (key3 = :key3 or :key3 is null);

Actually, no they aren't equivalent, since the UNION solution will essentially be an OR of the individual conditions, rather than an AND. In the original statement, if multiple keys are non-null, all of them must match. In the UNION-based one, only one must match. Adding the original clauses fixes that problem.

In article <MLOENNRO.94Jan28105342_at_demo1.se.oracle.com: Magnus Lonnroth, mloennro_at_se.oracle.com writes:
:The second statement is true (all bind-variables are null). Sorry for
:being a bit hasty there. I can't think of any solution for that case
:(that wouldn't imply at least one full table scan).

It seems to me that if you add a
 union
 select rowid from MYTABLE where :key1 is null and :key2 is null and
:key3 is null

to the statement, it might work. I.e. I suspect that it may dynamically recognize that no record can succeed (e.g. select 'x' from table1 where 1=2 is always fast; is it true if they are bind variables?) and decide to do nothing. A bit simpler than dynamically deciding which index to use. Of course, in the case where all fields are actually null, it will do a full table scan, but that is what you want. The trick (if desired!) is to allow that case and not blow up the rest of the time. This might work (unfortunately, I don't have access to a db to check...). Received on Sat Jan 29 1994 - 00:21:28 CET