On 5 mrt, 06:49, Geoff Muldoon <geoff.muld..._at_trap.gmail.com> wrote:
> 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
One thing on GTT's: once you have statistics, save these, or never
let them be reconstructed; nine out of ten times, the optimizer
will regard your GTT as empty, changing your execution plan
dramatically.
Received on Mon Mar 05 2007 - 03:56:23 CST
