Re: Oracle not using Index
From: Jim Kennedy <kennedy-down_with_spammers_at_no_spam.comcast.net>
Date: Fri, 25 Jul 2003 14:32:24 GMT
Message-ID: <YbbUa.127211$GL4.33712_at_rwcrnsc53>
Date: Fri, 25 Jul 2003 14:32:24 GMT
Message-ID: <YbbUa.127211$GL4.33712_at_rwcrnsc53>
No, it is not always better to do a full table scan via an index vs just doing it. If I do it via the index then I have to scan 2 things (the index and the table) and that is more IO than just scanning 1 thing(the table). Jim
-- "Steffen Stellwag" <ststellwag_at_aol.com> wrote in message news:15d2966b.0307250250.1c4c98e_at_posting.google.com...Received on Fri Jul 25 2003 - 16:32:24 CEST
> Truely is often better to scan a table in full passing by an index ,
> but if you can force the optimizer to use an index via a hint for
> testing and comparing the results.
>
> But the index in the above example is not used , because the hint is
> malformed,
> if tables in a Select statment are named by aliases you have to
> specify the alias name in the hint statment , not the table name
> /*+ INDEX (ICWOIMP PK_ICWOIMP) */ change to /*+ INDEX (A PK_ICWOIMP)
> */
>
>
>
>
> "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in
message news:<AX%Ta.124377$sY2.56301_at_rwcrnsc51.ops.asp.att.net>...
> > A hint is a hint not a requirement. A full table scan might actually be
> > faster than using an index which is what the optimizer might be
thinking.(eg
> > if the whole table or most of it is going to be retrieved then a full
table
> > scan would be faster than using an index)
> > Jim
> >
> > "Mahesh Hardikar" <hardikarm_at_yahoo.com> wrote in message
> > news:4a1c57c2.0307240345.3c53f50b_at_posting.google.com...
> > > Hi ,
> > >
> > > Oracle 8.1.7.0.0 on HP-UX 11.0
> > >
> > > We have following query .
> > > /************
> > > SELECT
> > > A.CANNO "INVOICE NO",
> > > A.CANDATE "INVOICE DATE",
> > > --B.NAME "CUSTOMER NAME",
> > > A.CANAMT "REVENUE AMOUNT",
> > > C.RCPTDOCNO "RECEIPT NO.",
> > > D.RECEIPTAMOUNT "RECEIPT AMOUNT",
> > > C.RCPTDATE "RECEIPT DATE"
> > > FROM
> > > ICWOIMP A,
> > > ICADDDRESSDTLS B,
> > > AFAS_RCPT_HDR C,
> > > AFAS_RCPT_DTLS D
> > > WHERE
> > > A.WOKEY = B.WOKEY
> > > AND D.RECEIPTHDRID = C.RECEIPTHDRID
> > > AND ADDTYPE ='SHPR'
> > > --AFAS_RCPT_DTLS.RECEIPTHDRID = AFAS_RCPT_HDR.RECEIPTHDRID
> > > AND D.DOCLINKREFNUM = A.CANNO
> > > AND D.DOCLINKNUM = A.WONO
> > > ********/
> > >
> > > Execution Plan :
> > > -----------------------------------------------------
> > > SELECT STATEMENT Optimizer=CHOOSE (Cost=1178 Card=1 Bytes=12
> > > 1)
> > >
> > > 0 NESTED LOOPS (Cost=1178 Card=1 Bytes=121)
> > > 1 NESTED LOOPS (Cost=1175 Card=1 Bytes=109)
> > > 2 HASH JOIN (Cost=1174 Card=1 Bytes=81)
> > > 3 TABLE ACCESS (FULL) OF 'AFAS_RCPT_DTLS' (Cost=70 Car
> > > d=11603 Bytes=440914)
> > >
> > > 3 TABLE ACCESS (FULL) OF 'ICWOIMP' (Cost=830 Card=3733
> > > 9 Bytes=1605577)
> > >
> > > 2 TABLE ACCESS (BY INDEX ROWID) OF 'AFAS_RCPT_HDR' (Cost
> > > =1 Card=8343 Bytes=233604)
> > >
> > > 6 INDEX (UNIQUE SCAN) OF 'PK_AFAS_RCPT_HDR' (UNIQUE)
> > > 1 TABLE ACCESS (BY INDEX ROWID) OF 'ICADDDRESSDTLS' (Cost=
> > > 3 Card=12018 Bytes=144216)
> > >
> > > 8 INDEX (RANGE SCAN) OF 'INDX_ICADDRESSDTLS_WOKEY' (NON-
> > > UNIQUE) (Cost=2 Card=12018)
> > >
> > > This plan shows that ICWOIMP is accessed FULL . Actually this table
> > > has a Primary Key on WOKEY & this is used in JOIN condition . WHy is
> > > it not using that index
> > >
> > > I tried to force this index
> > >
> > > SELECT /*+ INDEX (ICWOIMP PK_ICWOIMP) */
> > > A.CANNO "INVOICE NO",
> > > A.CANDATE "INVOICE DATE",
> > > --B.NAME "CUSTOMER NAME",
> > > A.CANAMT "REVENUE AMOUNT",
> > > C.RCPTDOCNO "RECEIPT NO.",
> > > D.RECEIPTAMOUNT "RECEIPT AMOUNT",
> > > C.RCPTDATE "RECEIPT DATE"
> > > FROM
> > > ICWOIMP A,
> > > ICADDDRESSDTLS B,
> > > AFAS_RCPT_HDR C,
> > > AFAS_RCPT_DTLS D
> > > WHERE
> > > A.WOKEY = B.WOKEY
> > > AND D.RECEIPTHDRID = C.RECEIPTHDRID
> > > AND ADDTYPE ='SHPR'
> > > --AFAS_RCPT_DTLS.RECEIPTHDRID = AFAS_RCPT_HDR.RECEIPTHDRID
> > > AND D.DOCLINKREFNUM = A.CANNO
> > > AND D.DOCLINKNUM = A.WONO
> > >
> > > But still with this , execution plan remained the same.
> > > AM I missing something ? Can Oracle ignore the hint although provided
> > > ?
> > >
> > > P.S. Statistics are Up-To-Date for all tables.
> > >
> > > Regards,
> > > Mahesh Hardikar