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.

Nuno Souto
----- Original Message ----- 
From: "Stephane Faroult" <sfaroult@xxxxxxxxxxxx>
>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'.


Other related posts: