Re: Avoiding full table scan

> > > ( "CERTLOG"."INVDETAILKEY" = "INVDETAIL"."INVDETAILKEY"
)
> > AND
> > >
> > > ( "CERTLOG"."INVDETAIL_SEGMENT" = "INVDETAIL"."SEGMENT"
)
Above clause makes it a correlated subquery. As a result it fetches each row of invdetail
and checks whether the subquery becomes true. It is faster to fetch the row directly because
row may need an update anyway. Also the columns needing update are not key columns(i.e. index entry does not change). So fetching the row via index is an overhead.
As I thought thru, removing the above clause from the subquery may force primary key index usage. Then the subquery will fetch all invdetail_key, segment pairs from the certlog table. The subquery is not dependent on the main table anymore. Then update statement will choose only those invdetail rows whose key values are present in the subquery result set. So removing the above 2 conditions from the subquery may do the trick.

raghuvir

Frank Calfo <TFIAM_at_worldnet.att.net> wrote in article <6lad6k$ofm_at_bgtnsc03.worldnet.att.net>...
> 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 Sat Jun 06 1998 - 01:27:15 CDT