Re: Oracle using indexes

What optimizer goal do you have set?

CHOOSE and ALL_ROWS will tend to do table scans. FIRST_ROWS will tend to use indexes.

CHOOSE will use the rule based optimizer if there are no statistics. If there are statistics, it will use cost based optimizer as if ALL_ROWS was set. We had similar issues and set the optimizer goal to FIRST_ROWS and it made a big difference.

Good Luck,

DeVerne

Daniel wrote in message ...
>If your default optimization is set to COST BASED and it is doing this, you
>are seeing exactly the same problems we were having just recently. Even
>with indexes in place, the optimizer is not using them.
>
>We basically had to use hints for most of our complex queries. We did
>analyze tables and this seemed to fix some of it, but there were cases
where
>it did not.
>
>When I posted this here, I was told that it was the way Oracle worked... I
>have been using Oracle since version 6. This is not the way it works. It
>is a bug in the optimizer. Try analyze first and then use hints for the
>ones that don't work after that.
>
>Daniel
>Austin, TX
>
>"Christ Follower" <christ_follower_at_my-deja.com> wrote in message
>news:87n4qb$c8$1_at_nnrp1.deja.com...
>> I use explain plan to verify how Oracle utilizes indexes and found an
>> interesting scenario.
>> CREATE TBL1 (
>> KEY1 VARCHAR2(10) PRIMARY KEY,
>> FLD2 VARCHAR2(5));
>> CREATE TBL2 (
>> KEY1 VARCHAR2(10) PRIMARY KEY,
>> FLD2 VARCHAR2(5));
>>
>> EXPLAIN PLAN SET STATEMENT_ID='TEST' INTO PLAN_TABLE FOR
>> SELECT A.KEY1
>> FROM TBL1 A, TBL2 B
>> WHERE A.KEY1 = B.KEY1
>>
>> It reveals Oracle is performing a full table scan on TBL2. I wonder why
>> Oracle not using the primary key index since the values in TBL2.KEY1 is
>> already in the index (smaller than scanning the entire table).
>>
>> Comment?
>>
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>
>
Received on Mon Feb 07 2000 - 20:34:36 CST