Re: Avoiding full table scan

I was hoping that the where clause on the update statement (update invdetail where (invdetailkey, segment) in ...) would cause the optimizer to use the primary key index since invdetailkey and segment are the primary key columns. The subquery, while querying from another table, provides the data for the where clause since it selects invdetailkey and segment.

It is my understanding that the where clause of the update statement is the key for how Oracle will execute the statement. If the where clause includes columns that are indexed, it will use the index. If it does not include indexed columns, then it must do a full table scan. And yet, I can't get it to use the index.

If I construct the where clause to be 'where invdetailkey = 1 and segment = 0' then it uses the index.
But when it reads 'where (invdetailkey, segment) in ' then it does not use the index.
Perhaps it is the concatenation that renders it unable to use the index.

rok wrote:
>
> 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 - 22:47:53 CDT