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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
