RE: Using 12/31/9999 in a date field

From: Jonathan Lewis <>
Date: Wed, 18 Dec 2013 17:44:30 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DD2212_at_exmbx05.thus.corp>

I assume this is actually a column of type date.

For date-RANGE predicates the optimizer will generally underestimate the cardinality by a huge factor - e.g. if you have 10 years of real data and ask for one year the optimizer will believe that you are after 1 year in 8,000 (i.e the range of ca. 2,000 CE to 10,000 CE) rather than 1 year in 10.

For damage limitation you can create a histogram on the column with as many buckets as possible (254 in 11g) so that the optimizer will get some idea of how much data is in the real range, and how much data is outside the real range. This is one of the few cases where an automatically gathered height-balanced histogram is a fairly safe bet.

Jonathan Lewis

From: [] on behalf of [] Sent: 18 December 2013 17:33
To:; Subject: RE: Using 12/31/9999 in a date field

My main concern is how the optimizer will deal with it. Certainly functionally it can work fine.

Jay Miller
Sr. Oracle Database Administrator
From: [] On Behalf Of Coll-Barth, Michael Sent: Wednesday, December 18, 2013 11:16 AM To:
Subject: RE: Using 12/31/9999 in a date field

Iíve always used this as an end date as opposed to nulls. While I can deal with nulls just fine ( I prefer them, especially for statistics ), but when dealing with users, I find too often that code fails to produce the desired result set due to not properly taking nulls into account.

For me, it becomes a question of which do you prefer; a little ugly or crap results?

From: [] On Behalf Of Sent: Wednesday, December 18, 2013 10:53 AM To:
Subject: Using 12/31/9999 in a date field

Iíve always told people to avoid this like the plague but Iím now on a call with a developer who says Oracle in general and Tom Kyte specifically have changed their position on this and they now recommend it.

Has anyone heard anything about this or have a white paper or link that I can review? Or if it still isnít a good idea (I have a hard time imagining it is but maybe the optimizer has gotten a lot smarter) have a link showing the opposite more recent than the 2008 Tom Kyte article?


Jay Miller

Received on Wed Dec 18 2013 - 18:44:30 CET

Original text of this message