Re: My 10 Commandments of Database Administration...
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Sep 2004 06:58:21 -0600
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@xxxxxxxxxxxx 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'.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
