Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Avoiding full table scan
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