Re: My 10 Commandments of Database Administration...

From: Tim Gorman <tim_at_sagelogix.com>
Date: Fri, 24 Sep 2004 20:38:44 -0600
Message-ID: <BD7A3654.1CE83%tim@sagelogix.com>

You got that right!

It can be the difference between life and death for a type-2 slowly-changing dimension in a data warehouse. Having to perform an UPDATE on the previous row is pure murder for ETL processes. Keeping this mechanism in mind, the ETL process can just INSERT the new rows and ignore about the older rows...

on 9/23/04 8:08 PM, Nuno Pinto do Souto at nsouto_at_bizmail.com.au wrote:

> Quoting from AUTHOR Tim Gorman:

>> With the LEAD() windowing function, it's not really necessary to store
>> END_DATE, as long as it is implied by the next record's START_DATE
>> value. Of course, if periods overlap, then END_DATE would still be
>> necessary...
>> 
>>     select  ...,
>>             start_date,
>>             lead(start_date) over (order by start_date)
>>                         - (1/86400) end_date,
>>             ..., ...
>>     from    ...
>>     order by ...
>> 

>
>
> Cripes! That saves me a bundle of SQL in another problem I was having.
> Thanks a million! I really gotta spend some time reviewing these
> analytical functions: there is a gold mine of reduced DML in them.
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 24 2004 - 21:39:41 CDT