Re: Avoiding full table scan

From: Keith Boulton <boulkenospam_at_globalnet.co.uk>
Date: Fri, 05 Jun 1998 07:43:03 GMT
Message-ID: <3577a117.648051@read.news.global.net.uk>

On 4 Jun 1998 22:36:53 GMT, "Frank Calfo" <fcalfo_at_psateam.com> wrote:

>The following update statement is producing a full table scan
>on one of our largest tables: invdetail
>

I think the hint has to come before the table name try
UPDATE /*+ index(xpkinvdetail) */
 "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" )
          )

or
try
UPDATE /*+ rule */
 "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" )
          ) Received on Fri Jun 05 1998 - 02:43:03 CDT