Re: Oracle using indexes

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 - 13:23:01 CST