Expensive update - advice please

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Mon, 5 Mar 2007 16:49:48 +1100
Message-ID: <MPG.20565a83362262399898ec@news.readfreenews.net>

Hi all,

Oracle 10.0.1.x RAC on RH Linux cluster ..

I have a set of data which I spasmodically acquire from a vendor application which I need to "massage" to check against values loaded into our data warehouse.

I have a global temp table:

CREATE GLOBAL TEMPORARY TABLE TEMP_SSP_STTS_HIST
(

  SSP_NO          NUMBER         NOT NULL,
  SSP_STTS_NO     NUMBER         NOT NULL,
  AVAIL_KEY_NO    NUMBER,

  EFFCT_START_DT DATE,
  EFFCT_END_DT DATE,
  <other columns>
)
ON COMMIT PRESERVE ROWS
NOCACHE;
CREATE UNIQUE INDEX PK_TEMP_SSP_STTS_HIST ON TEMP_SSP_STTS_HIST
(SSP_NO, SSP_STTS_NO); -- primary key

CREATE INDEX NUK_TEMP_SSP_STTS_HIST_AVAIL ON TEMP_SSP_STTS_HIST
(SSP_NO, AVAIL_KEY_NO);

CREATE INDEX NUK_TEMP_SSP_STTS_HIST_START ON TEMP_SSP_STTS_HIST
(EFFCT_START_DT);

CREATE INDEX NUK_TEMP_SSP_STTS_HIST_END ON TEMP_SSP_STTS_HIST
(EFFCT_END_DT);
Data is usually about 4 million rows, each SSP_NO has an avg of 6 SSP_STTS_NOs, 80% of SSP_NOs have only 1 AVAIL_KEY_NO but can have up to 4 or 5.

I then need to set the EFFCT_END_DT for the maximum SSP_STTS_NO for each combination of SSP_NO/AVAIL_KEY_NO to a set (future) date, and need to set the EFFCT_END_DATE for every other record for that combination to the day before the EFFCT_START_DATE of following SSP_STTS_NO for the combination. Unfortunately the SSP_STTS_NO's for a SSP_NO are not an unbroken sequence
(so no good for LAG function ??).

The following brute-force-ish statement does the job, but takes a long time to execute, and I would appreciate any hints on optimizing.

update TEMP_SSP_STTS_HIST A

    set A.EFFCT_END_DT =

        nvl((select min(B.EFFCT_START_DT) - 1
             from TEMP_SSP_STTS_HIST B
             where B.SSP_NO = A.SSP_NO
             and B.AVAIL_KEY_NO = A.AVAIL_KEY_NO
             and B.SSP_STTS_NO > A.SSP_STTS_NO), v_future_date));

Will post explain plan if requested, have to load data into global temp table for it to be valid I guess.

TIA, Geoff M Received on Sun Mar 04 2007 - 23:49:48 CST