Re: Index hints are not working

From: <michael_bialik_at_my-deja.com>
Date: 2000/03/10
Message-ID: <8aap8d$9rg$1@nnrp1.deja.com>

Hi.

SELECT /*+ INDEX(DS001 IDX_DS001A) */ * FROM DS001 is supposed to do a FULL table scan because no WHERE/ORDER BY clause exists.
Make an EXPLAIN for: SELECT /*+ INDEX(DS001 IDX_DS001A) */ * FROM DS001 ORDER BY SW_DS001A , DS001FD1
- It is supposed to use index. Don't worry if that select takes a lot of time, because it accesses index AND data parts of table ( vs. FULL scan that accesses only data and using SORT ). As you wrote yourself the application program will stop after N first records.

HTH. Michael.

In article <8aamdh$50un8$1_at_reader2.wxs.nl>, "Klaas Oosterhuis" <K.Oosterhuis_at_asysco.com> wrote:
> Hi Mark
>
> Thank you for your answer.
>
> I have tried to use the label. No change there.
> I have searched: http/technet.oracle.com for the bug or a patch on my
> 8.0.5.0.0 release. I can't seem to find any.
> The only thing I found to download was a complete Oracle EE server
8.1 400
> Mb or something like that. !!!
>
> I have changed the statement to "UNION ALL'. For the query specified
its now
> working well. But I am afraid that some other querys will not read
the data
> using the INDEX.
> The simple:
> SELECT /*+ INDEX(DS001 IDX_DS001A) */ * FROM DS001
> Does a table scan. I want to have the records returned in the
index
> order. If I add an ORDER BY clause the query takes to long.
> Over the fact that a full table scan would be the best way:
> 1) I ** MUST ** have the records returned on the index order
in all
> situations
> 2) The program can decide that no more results are needed.
> So instead of reading all 100.000 records only the
first 50
> are read that the statement is canceled. In that case reading 100.000
> records using a table scan. Than sorting those records and using the
first
> 50 is a LOT slower than reading 50 records by INDEX.
>
> I hope you or another one have some comment on this.
>
> Kind regards Klaas Oosterhuis
>
> markp7832_at_my-deja.com wrote in message
<8a8llf$ote$1_at_nnrp1.deja.com>...
> >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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 10 2000 - 00:00:00 CST