Re: Index hints are not working

From: <markp7832_at_my-deja.com>
Date: 2000/03/09
Message-ID: <8a8llf$ote$1@nnrp1.deja.com>#1/1

Well I notice that your first couple of explain plans say RULE. You also have OR clauses and the OR disables the use of an index. The optimizer should try to convert this into the union of multiple queries each of which uses the index for one value for both the RULE and CBO.

Try select /*+ INDEX(a IDX_DS001A) */ a.*

      from  ds001a a
      where  ......

I have had a few problems getting Oracle to use an index hint and it seems like using a label instead of the table_name is the way to go. I would also update the statistics with a large sample size or a compute.

I do not know your data but are you sure a full table scan is not the best way to go on this?

One last thing. I read on Metalink about a bug in the CBO with one release of ver 8.0.5 something where the CBO would not use an index. Oracle posted the bug number and said there was a patch. I believe that if you are at 8.0.5.2 you are past the bug.

In article <8a7siq$4to1m$1_at_reader2.wxs.nl>,   "Klaas Oosterhuis" <K.Oosterhuis_at_asysco.com> wrote:
>
> Hi
>
> I am using Oracle 8.0.5 evaluation version.
>
> I want to read records from a TABLE USING IN ALL SITUATIONS an
 selected
> index. To force this I use hints but nothing happens. Its ignored for
 some
> reason by the ORACLE database and a full table scan is executed!!!!!!
> How can I force the INDEX ??????????????????
>
> EXPLAIN PLAN FOR SELECT /*+ INDEX(DS001 IDX_DS001A) */ * FROM DS001
> WHERE (SW_DS001A = 'J' And DS001FD1 > 18) Or
> (SW_DS001A = 'J' And DS001FD1 = 18 And DS001FD2 > 0) Or
> (SW_DS001A = 'J' And DS001FD1 = 18 And DS001FD2 = 0 And DS001FD3 > 0)
 Or
> (SW_DS001A = 'J' And DS001FD1 = 18 And DS001FD2 = 0 And DS001FD3 = 0
 And
> DS001FD4 >= 0)
>
> Operation Options Owner Obj-Name
> Optimizer Search Id/par.id/pos./cost/cardi./bytes
>
> SELECT STATEMENT

 RULE 0.00
> 0 1 1 2 200
> TABLE ACCESS FULL PCSBASE DS001 1
 0.00 1
> 0 1 1 2 200
>
> Same statement but with an ORDER BY:
> ORDER BY SW_DS001A, DS001FD1, DS001FD2, DS001FD3, DS001FD4;
>
> SELECT STATEMENT

 RULE 0.00
> 0 3 3 2 200
> SORT ORDER BY
> 0.00 1 0 1 3 2 200
> TABLE ACCESS FULL PCSBASE DS001 1
 0.00 2
> 1 1 1 2 200
>
> And still it uses an FULL scan.
> When i turn the session back:
> ALTER SESSION SET OPTIMIZER_MODE=CHOOSE
> There is no change.
> SELECT STATEMENT CHOOSE
> 0.00 0 3 3 1 100
> SORT ORDER

 BY                                           0.00


> 1 0 1 3 1 100

> TABLE ACCESS         FULL           PCSBASE    DS001  1

 0.00 2 1
> 1 1 1 100
>
> CREATE TABLE "PCSBASE"."DS001" (DS001FD1 NUMBER NULL, DS001FD2 NUMBER
 NULL,
> DS001FD3 NUMBER NULL, DS001FD4 NUMBER NULL, DS001FD5 CHAR(80) NULL,
> SW_DS001A CHAR(1) NULL, SW_DS001C CHAR(1) NULL, SW_DS001D CHAR(1)
 NULL,
> SW_DS001M CHAR(1) NULL, RECNO NUMBER NOT NULL, CHECK (RECNO IS NOT
 NULL),
> PRIMARY KEY (RECNO)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 STORAGE
> ( INITIAL 10K NEXT 3196K MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 50)
> TABLESPACE "USER_DATA";
>
> CREATE INDEX "PCSBASE"."IDX_DS001A" ON "PCSBASE"."DS001"
 ("SW_DS001A",
> "DS001FD1", "DS001FD2", "DS001FD3", "DS001FD4")
 TABLESPACE "USER_DATA"
> PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 10K NEXT 946K
> MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 50 FREELISTS 1);
>
> Kind regards Klaas
>
>

--
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 Thu Mar 09 2000 - 00:00:00 CST