Re: My 10 Commandments of Database Administration...

Whatever the optimiser may do with 31 dec 2099, it will be a darn long shot better than a NULL value that can't be indexed...

Still, the correct design is to add a "status" column where the "current" can be indicated. The high date is a poor substitute. But still better than having to do range or full table scans to find the current row of a particular category.

Cheers
Nuno Souto
nsouto_at_bizmail.com.au
----- Original Message -----
From: "Stephane Faroult" <sfaroult_at_roughsea.com>
>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
>date in 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. It goes 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