Re: My 10 Commandments of Database Administration...

From: Tim Gorman <tim_at_sagelogix.com>
Date: Thu, 23 Sep 2004 21:19:26 -0500 (EST)
Message-ID: <BD78248D.1CD30%tim@sagelogix.com>

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 ...

Think of the savings in maintaining time-variant data when you don't have to find and update the "previous" record after you insert a new record.

However, note that the LEAD() function will return a NULL value for the "current" record. Sorry Nuno -- you'll need to wrap the whole thing in an NVL()... on 9/23/04 4:01 AM, Stephane Faroult at sfaroult_at_roughsea.com wrote:

>
> I have had some time ago a private discussion on that same topic with
> somebody from the list, who was pointing that 31st dec 2099 or whichever
> datein the future could totally perturbate the optimizer, even with
> histograms, by giving it a totally distorted view of the actual _range_ of
> values. I would not absolutely condemn the fixed date in the future, though.
> After all, it makes sense to mean 'for the foreseeable future', and it gives
> excellent results when most of your queries only deal with current values.
> Itgoes bad when you want to return both current values and values from the
> recent past, because the theoretical range scan has nothing to do with the
> actual one. But it is indeed far better to record known facts - dates when
> things start becoming effective. IMHO it depends a lot on the amount of data
> wih an historical component, and I wouldn't take the same approach for share
> valuation and telecoms rates, even when everybody talks about 'valuation'.

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

Received on Thu Sep 23 2004 - 21:13:25 CDT