Re: Avoiding full table scan

What happens if you try the following way?

UPDATE "INVDETAIL"

       SET "SAILED_VESSEL_ID" = 'BB',   
           "SAILED_VOYAGE_ID" = '1'
     WHERE 
     exists (select 0 
                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" )

 	   )

I am not sure it will avoid table scan on invdetail. But it may avoid table scan on certlog which may reduce the update time. Update time is directly proportional to the number of rows which qualify for update. Even if the invdetail is scanned completely if the number rows qualifying for the update is a small number you should be okay. That is my reasoning.

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 Thu Jun 04 1998 - 22:33:25 CDT