Re: Oracle Optimizer versus the Logical OR

From: <sstephen_at_us.oracle.com>
Date: 18 Sep 92 06:59:24 GMT
Message-ID: <1992Sep17.225924.1_at_us.oracle.com>


In article <1992Sep17.004345.12002_at_nb.rockwell.com>, usenet%dny.rockwell.com (Usenet Account) writes:
> We are at the point where we have loaded real data into the Oracle database I
> built -- 40,000 rows in one table and 80,000 in another. It is at this
> juncture that indexing is essential for speedy retrieval.
>
> However, it has become apparent that Oracle V6.0's optimizer *BYPASSES* the
> indexes and does a table scan (!) when it encounters a logical OR combined
> with any of the following items in the WHERE clause: Outer join, subselect,
> and possibly others. CONNECT BY and != and NOT xxx also seem to induce
> the optimizer to eschew the indexes which I so carefully put in place for this
> purpose! This of course applies to the "IN (item,item,item)" clause too, since
> this is equivalent to a string of OR's.
>
> My solution was to UNION together SELECT statements. Example:
>
> select eq.make, eq.serialno, emp.lastname
> from equipment eq, employee_roster emp
> where eq.ssn = emp.ssn(+)
> and (eq.make = 'HEWLETT PACKARD' or eq.make = 'BECHTEL');
>
> The foregoing results in a table scan, although "ssn" and "make" are indexed.
>
> select eq.make, eq.serialno, emp.lastname
> from equipment eq, employee_roster emp
> where eq.ssn = emp.ssn(+)
> and (eq.make = 'HEWLETT PACKARD')
> UNION
> select eq.make, eq.serialno, emp.lastname
> from equipment eq, employee_roster emp
> where eq.ssn = emp.ssn(+)
> and (eq.make = 'BECHTEL');
>
> The foregoing USES the indexes.
>
> In conclusion:
>
> "OR" is an awfully common thing to put in a SQL query -- any suggestions
> on other workarounds -- or better yet, a non-kludge way to make Oracle
> use the indexes, even against its will ?!?!?
 

>
> ------------------------------------------------------------------------
> Bruce Toien
> Rockwell International - Space Systems Division, Downey, CA
> (310) 922-2273
> bruce_at_rincon.ema.rockwell.com
> ------------------------------------------------------------------------

According to my "likely stories" list, (which means that this information is based on hearsay and rumors combined with my own experience), Oracle is only able to make use of one index access per "query group". Since indexes are essentially B+-tree structures, it has a choice of locating a specific value or going through an indexed range. Given that is must check 2 values, (either side of the OR), it is not quite smart enough to do BOTH accesses. Therefore, it does neither. It does a full-table scan and picks off the losers. The UNION, on the other hand, is telling it to do exactly that! and making the query "smarter".

The only alternative, (that would use an index), would be something like :

 select eq.make, eq.serialno, emp.lastname  from equipment eq, employee_roster emp
 where eq.ssn = emp.ssn(+)
 and eq.make IN (select 'HEWLETT PACKARD' from dual UNION

                 select 'BECHTEL' from dual); 

In this case, the subquery will give exactly 2 rows, which will drive the outer table using the index. I haven't tested it, but it seems logical that it will do a the same thing as your second query.

Scott Stephens (not representing the official word of my company) Received on Fri Sep 18 1992 - 08:59:24 CEST

Original text of this message