Re: Avoiding full table scan

Another thought. You can not avoid table scan on invdetail. The reason is there are no additional conditions which can get a subset of invdetail. So it has to do a full table scan.

raghuvir

Frank Calfo <fcalfo_at_psateam.com> wrote in article <01bd900a$6cd7da00$0a010180_at_frank>...
> The following update statement is producing a full table scan
> on one of our largest tables: invdetail
>
> 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 ) AND
> ( "CERTLOG"."INVDETAILKEY" = "INVDETAIL"."INVDETAILKEY" )
AND
>
> ( "CERTLOG"."INVDETAIL_SEGMENT" = "INVDETAIL"."SEGMENT" )
> )
>
> The primary key of the invdetail table is composed of two
> columns: invdetailkey and segment
>
> The subquery provides a list of the key values of all the rows in
> the invdetail table to update
>
> We want to access the invdetail table by its primary key index:
> xpkinvdetail
>
> We cannot avoid the full table scan even after adding the
> optimizer hint to the update statement as follows:
> update invdetail /*+ index(xpkinvdetail) */
>
> Any ideas how to avoid the full table scan ?
>
>
Received on Fri Jun 05 1998 - 09:34:40 CDT