Re: SQL performance - optimizer question

From: Magnus Lonnroth <mloennro_at_se.oracle.com>
Date: Sat, 29 Jan 1994 11:57:29 GMT
Message-ID: <MLOENNRO.94Jan29115730_at_demo1.se.oracle.com>

>>>>> "Kevin" == Kevin Neel <k-neel_at_nwu.edu> writes:

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

Oh man, isn't super-cite great. Anybody know to prevent it from collapsing lines ??

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

You are right. My apologies. I thought only one of the keys would be used at a time. The original WHERE-clause should be added to the outer query-block, and will be executed as a filter on the rows returned by "where rowid in...".

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

  Kevin> It seems to me that if you add a union select rowid from

  Kevin> MYTABLE where :key1 is null and :key2 is null and :key3 is   Kevin> null

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

I still haven't seen any reason for not doing the simplest thing:

strcpy ( sql, "select val1, val2 from mytable where 1=1" );

if ( *key1 ) strcat ( sql, " and key1 = :key1 );
if ( *key2 ) strcat ( sql, " and key2 = :key2 );
if ( *key3 ) strcat ( sql, " and key3 = :key3 );
if ( oopen ( cda, lda, (char*)0, -1, (char*)0, -1 ) ||
     oparse ( cda, sql, -1, 1, 1 )) handleError ( lda, cda );

if ( *key1 )
{

   if ( obndrv ( cda, "key1", -1, key1, 11, CHARZ, -1,

                 &ikey1, (char*)0, -1, -1 )) handleError ( lda, cda );

}
etc etc

--

Magnus Lonnroth
Tech.Sales & Consultant
Oracle Sweden
Mail: mloennro_at_se.oracle.com

Received on Sat Jan 29 1994 - 12:57:29 CET