Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index hints are not working

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.

  1. SELECT /*+ INDEX(DS001 IDX_DS001A) */ * FROM DS001 is supposed to do a FULL table scan because no WHERE/ORDER BY clause exists.
  2. 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US