RE: Using 12/31/9999 in a date field
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Dec 2013 14:02:42 -0500
What JL said about the optimizer if you do use a high value.
I'm curious about what the context of your use in fact is.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Wednesday, December 18, 2013 12:45 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Using 12/31/9999 in a date field
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.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Jay.Miller@xxxxxxxxxxxxxxxx [Jay.Miller@xxxxxxxxxxxxxxxx]
Sent: 18 December 2013 17:33
To: Michael.Coll-Barth@xxxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
201.369.8355
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Coll-Barth, Michael
Sent: Wednesday, December 18, 2013 11:16 AM
To: oracle-l@xxxxxxxxxxxxx
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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Wednesday, December 18, 2013 10:53 AM
To: oracle-l@xxxxxxxxxxxxx
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?
Thanks!
Jay Miller
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
