Re: Avoiding full table scan

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Jun 1998 14:56:01 GMT
Message-ID: <35795476.4677586@192.86.155.100>

A copy of this was sent to Frank Calfo <TFIAM_at_worldnet.att.net> (if that email address didn't require changing) On Fri, 05 Jun 1998 20:47:53 -0700, you wrote:

>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:
>>

[snip]

Its not the IN, its the fact that the SUBQUERY is a corelated subquery and that the subquery refers to the table INVDETAIL. The suquery is identical to the query:

( SELECT "CERTLOG"."INVDETAILKEY",
         "CERTLOG"."INVDETAIL_SEGMENT"
    FROM "CERTLOG", "INVDETAIL"
   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" )

And there are no predicates whatsoever on INVDETAIL. Therefore EVERY row in INVDETAIL must be inspected.

if you were to procedurally write your update statement, the psuedo code would look like:

   for every row in invdetail

      generate the set of rows in certlog such that the where clause is met
      if the invdetail key is in that set from certlog update the row.

   end loop  

What will work better is simply:

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 )
      )

Why even reference INVDETAIL in the subquery? It is redudant and is preventing the use of the index... The "where ( ... ) in" will be statisfied in the same way for both queries (if it was in the corelated subquery it will in the non-corelated one. if it was not in the corelated subquery, it will not be in the non-corelate one)

also, you might want to look at:

                 ( "CERTLOG"."DATECERTEND" = SYSDATE )

sysdate contains a date/time -- are you sure you want the time component? you might need to use trunc(sysdate) to get just the date portion.
>
>> > 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 ?
>> >
>> >
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jun 06 1998 - 09:56:01 CDT