Re: SQL performance - optimizer question

From: Magnus Lonnroth <mloennro_at_se.oracle.com>
Date: 28 Jan 94 02:52:20 GMT
Message-ID: <MLOENNRO.94Jan27215220_at_pisa.se.oracle.com>

>>>>> "Jason" == Jason May <jmay_at_ctp.com> writes:

  Jason> I have a table of the form

  Jason> 	create table MYTABLE ( key1 varchar2(10), key2
  Jason> varchar2(10), key3 varchar2(10), val1 varchar2(30), val2 int
  Jason> );

  Jason> There table could be large (perhaps a few million rows).

  Jason> Indexes exist for all the key fields. Any of the key fields   Jason> can be NULL.

  Jason> I'd like to write a -single- parametrized SQL statement
  Jason> (we're using OCI) that would allow for searches on this table
  Jason> where values can be provided for any number of parameters (at
  Jason> least one, up to as many keys as exist on the table.

Any particular reason for not constructing the most efficient SQL at runtime ? I mean, you're using OCI, so what's the problem ?

  Jason> What I'd like:

  Jason> 	select val1, val2 from MYTABLE where (key1 = :key1 or
  Jason> :key1 is null) and (key2 = :key2 or :key2 is null) and (key3
  Jason> = :key3 or :key3 is null);

  Jason> The following is legal syntax, but it's reeeal slow.  It
  Jason> appears that the optimizer isn't clever enough to figure
  Jason> things out in advance and decides to do table scans.  The
  Jason> Sybase optimizer can deal with this sort of thing; I'm trying

  Jason> to figure out if any trick will allow this to be done   Jason> efficiently in Oracle.

Well, to my knowledge we don't have execute-time optimization yet (we decide on a query-path during parse-time). In an OR branch, Oracle will choose the highest ranking access-method that *both* OR-clauses can use. In this case, it would be a full table-scan, since NULL values are not stored in an index. Since ":key is null" is true or false for all rows, a full table scan is the fastest way to retreive them.

You should really just construct your SQL dynamically. But if you insist, try this:

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 )

I haven't tried it, but it should be fast enough provided your keys are reasonably distributed.  

  Jason> I'd really like to avoid having to do this (pseudocode):

  Jason> 	if (:key1 is null) if (:key2 is null) if (:key3 is
  Jason> null) ERROR ("Must provide at least one key") else select ...
  Jason> where key3 = :key3; else if (:key3 is null) select ...  where
  Jason> key2 = :key2; else select ...  where key2 = :key2 and key3 =
  Jason> :key3; else etc.

  Jason> I tried the following, which requires the key fields to be   Jason> made NOT NULL:

  Jason> select ... where key1 like :key1||'%' and key2 like   Jason> :key2||'%' and key3 like :key3||'%';

  Jason> but this also causes table scans.

No it doesn't. But Oracle can only use *one* index in this case. Which one it chosen depends on your configuration (cost- or rule-based optimization, or if you supply any hints).

  Jason> I couldn't find anything in the O'Reilly Performance Tuning   Jason> book that would help out in this case.

  Jason> Any help is appreciated, -Jason

  Jason> =======================================================================
  Jason> Jason W. May jmay_at_ctp.com CTP, Inc.  304 Vassar St.

  Jason> Cambridge MA 02139 (617)374-8223

  Jason> =======================================================================

Received on Fri Jan 28 1994 - 03:52:20 CET