Re: Index hints are not working

HI:

I had same porblem on Oracle 8i (8.1.5) with CBO. I also tried to use rule base and did not make differenece. I contacted support and no good answers. Finally, I run out of time. I changed my sql by adding the where to use the key value. See following example:

Assumption: a is the index column.

Orginal sql:select a,b,c from (select a,b,c, from tab1 order by a)

              where rownum < 20

New sql: select a,b,c form (select /* ImayHaveHitInHere */ a,b,c from tab1 where a < some_reasonable_value order by a) where rownum < 20.

The performace of the sql improved about 100 times for 4M(?) rows table and the execution plan indicates to use the index.

I hope people can call support to raise this issue. Oracle should fix this problem (bug).

Good luck

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
>
>

--
Zosen Wang (wangz_at_rocketmail.com)


Sent via Deja.com http://www.deja.com/
Before you buy.