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

Avoiding full table scan

From: Frank Calfo <fcalfo_at_psateam.com>
Date: 4 Jun 1998 22:36:53 GMT
Message-ID: <01bd900a$6cd7da00$0a010180@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 - 17:36:53 CDT

Original text of this message

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