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