Oracle Optimizer versus the Logical OR

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 ?!?!?