RE: Stubborn Table Resolution

From: kkennedy <kkennedy_at_firstpoint.com>
Date: Mon, 03 Jun 2002 10:03:25 -0800
Message-ID: <F001.004727F0.20020603100325@fatcity.com>

Thanks for all the helpful suggestions. Here is what worked:

Query:
SELECT Record_Type, Archive_Input_File
FROM MDMA_Input_File
GROUP BY Record_Type, Archive_Input_File

I modified the record_type column to not null. This allowed an index FFS which the optimizer wisely selected.

The update statement was more difficult. Here is the original and my workaround:

UPDATE MDMA_INPUT_FILE
 SET Partial_Day_Hold = :b1
   , OK_To_Process = :b2
   , VEE_Usage_End = to_date(:b3)
WHERE Rowid IN ( SELECT MDMA_Rowid

                 FROM ST_VEE_Input_File );

Replaced by (declarations omitted):

SELECT MDMA_Rowid
  BULK COLLECT INTO T_Rowids

  FROM ST_VEE_Input_File;
FORALL V_Rowid IN T_Rowids.FIRST..T_Rowids.LAST
  UPDATE MDMA_INPUT_FILE
   SET Partial_Day_Hold = :b1
     , OK_To_Process = :b2
     , VEE_Usage_End = to_date(:b3)

  WHERE Rowid = T_Rowids(V_Rowid);

Note that the original statement I show here is what I originally coded and not the one I sent to the list. The IN construction and the correlated subquery yielded identical performance (bad). The bulk collect followed by the forall update accomplished what I had hoped the optimizer would do. Readability suffers, but I need performance to dig out of the hole my predecessors and damagement have buried me in.

Kevin Kennedy
First Point Energy Corporation
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: kkennedy
  INET: kkennedy_at_firstpoint.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Jun 03 2002 - 13:03:25 CDT