Path: news.f.de.plusline.net!news-fra1.dfn.de!news-lei1.dfn.de!newsfeed.freenet.de!feeder.news-service.com!feeder4.cambrium.nl!feed.tweaknews.nl!63.218.45.10.MISMATCH!nx01.iad01.newshosting.com!newshosting.com!204.153.247.120.MISMATCH!teal.octanews.net!news-out.octanews.net!indigo.octanews.net!authen.yellow.readfreenews.net.POSTED!not-for-mail
From: Geoff Muldoon <geoff.muldoon@trap.gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Expensive update - advice please
Date: Mon, 5 Mar 2007 16:49:48 +1100
Message-ID: <MPG.20565a83362262399898ec@news.readfreenews.net>
Organization: Southern Cross University
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.60.2060
Lines: 59
NNTP-Posting-Date: 04 Mar 2007 23:49:19 CST
X-Trace: DXC=HdZa4MA?TDfP15DOXYF6eb]AGWjF5PjPi465Ue@WB_5o1_i=lDJ`<6oI0V42BOHUigbI8A6IiUbTdi@33lC[K34mT3B0[1IE`Fj
Xref: news.f.de.plusline.net comp.databases.oracle.server:194954

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