Re: Newbie: Index Usage

In article <80fc02$e8b$1_at_nnrp1.deja.com>,   mrslacker_at_my-deja.com wrote:
> I have a select statement as follows:
>
> select stuff
> from table1, table2
> where table1.x1 in (value1, value2) and
> table2.x2 (+) = table1.x2 and
> table2.x3 (+) = table1.x3;
>
> table1.x1 is indexed not primary. The query is
> doing a full table scan on table 1. If the outer
> joins are not present the index is used. Also if
> the in is not present and = value1 is used,and
> the outer joins are present it uses the index so
> a union works. Can anyone explain to me why this
> happens.
>

I noticed that on one had answered you yet so I will take a quick shot.

1. When you code a outer join you basically force Oracle into a particular driving table, namely, you need to drive on the table that you always want a row back from.
2. The use of the 'or' conjunction precludes the use of an index. Oracle will often get around this by transfroming the SQL into a union of same query once per or'ed value. In your case the 'in clause' is just like an 'or' since it consists of constants.

You might try comparing this to what you now have: where ( table1.x1 = value1

   or table1.x1 = value2 )
  and table2.x2 (+) = table1.x2

      table2.x3 (+) = table1.x3;

The combination of items 1 and 2 should more or less answer you question.
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 12 1999 - 12:31:09 CST