Index hints are not working

From: Klaas Oosterhuis <K.Oosterhuis_at_asysco.com>
Date: 2000/03/09
Message-ID: <8a7siq$4to1m$1@reader2.wxs.nl>#1/1

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