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: Avoiding full table scan

Re: Avoiding full table scan

From: Frank Calfo <TFIAM_at_worldnet.att.net>
Date: Mon, 08 Jun 1998 21:29:31 -0700
Message-ID: <6licoh$3ee@bgtnsc02.worldnet.att.net>


Thomas,
Thanks for your help. I completely overlooked the join to invdetail in the subquery. Yes, that join really serves no purpose. When I took out the join, the full table scan disappeared. Invdetail is now being accessed via the indexes.

Thomas Kyte wrote:
>
> What will work better is simply:
>
> UPDATE "INVDETAIL"
> SET "SAILED_VESSEL_ID" = 'BB',
> "SAILED_VOYAGE_ID" = '1'
> WHERE (INVDETAILKEY, SEGMENT) IN
> ( SELECT "CERTLOG"."INVDETAILKEY",
> "CERTLOG"."INVDETAIL_SEGMENT"
> FROM "CERTLOG"
> WHERE ( "CERTLOG"."COID" = 'TEST' ) AND
> ( "CERTLOG"."BURID" = 'PSSB' ) AND
> ( "CERTLOG"."CERTID" = 'TEST' ) AND
> ( "CERTLOG"."DATECERTEND" = SYSDATE )
> )
>
> Why even reference INVDETAIL in the subquery? It is redudant and is preventing
> the use of the index... The "where ( ... ) in" will be statisfied in the same
> way for both queries (if it was in the corelated subquery it will in the
> non-corelated one. if it was not in the corelated subquery, it will not be in
> the non-corelate one)

> Received on Mon Jun 08 1998 - 23:29:31 CDT

Original text of this message

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