Re: Index hints are not working

Jonathan Lewis wrote:
>
> 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
> >
> >

or when all else fails...

select /*+ RULE */

Ugh !

:-)

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse