Re: Index hints are not working

Try the /*+ First_rows */ hint. This is more appropriate if you want the apparent response time to the end-user to be quicker, rather than (nominally) minimising resource usage at the database.

Also, there is a bug somewhere (though
possibly in PL/SQL rather than SQL
that loses the + is there isn't a space
after it, making the hint a comment.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Klaas Oosterhuis wrote in message <8a7siq$4to1m$1_at_reader2.wxs.nl>...


>

>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

>

>