Re: My 10 Commandments of Database Administration...
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 24 Sep 2004 20:38:44 -0600
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@xxxxxxxxxxxxxx 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.
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
