RE: Using 12/31/9999 in a date field
Date: Wed, 18 Dec 2013 19:04:55 +0000
Message-ID: <0D8F4CAC0F9D3C4AACC63F50FD9957F72791C284_at_PRDCTWPEMLMB31.prod-am.ameritrade.com>
Yes, it is of type date.
Thank you for the advice!
Jay Miller
Sr. Oracle Database Administrator
201.369.8355
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Wednesday, December 18, 2013 12:45 PM
To: oracle-l_at_freelists.org
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
https://urldefense.proofpoint.com/v1/url?u=http://jonathanlewis.wordpress.com/&k=%2FJMyfAnQZOhZ4dnr8BYv6w%3D%3D%0A&r=DUCASJ0xPpeOmtQ4idyytbAD3iZK7MOOurZhynw7YaA%3D%0A&m=7AQH0CgUAWoDZoB1REkGDWJgBv3G1UFJ2FOPlugHoEY%3D%0A&s=53b3320c0172901d37c7aa4da3e51b274a4957d099e95a68144b94b81fe73359
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jay.Miller_at_tdameritrade.com [Jay.Miller_at_tdameritrade.com] Sent: 18 December 2013 17:33
To: Michael.Coll-Barth_at_VerizonWireless.com; oracle-l_at_freelists.org 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Coll-Barth, Michael
Sent: Wednesday, December 18, 2013 11:16 AM
To: oracle-l_at_freelists.org
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jay.Miller_at_tdameritrade.com
Sent: Wednesday, December 18, 2013 10:53 AM
To: oracle-l_at_freelists.org
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
--
https://urldefense.proofpoint.com/v1/url?u=http://www.freelists.org/webpage/oracle-l&k=%2FJMyfAnQZOhZ4dnr8BYv6w%3D%3D%0A&r=DUCASJ0xPpeOmtQ4idyytbAD3iZK7MOOurZhynw7YaA%3D%0A&m=7AQH0CgUAWoDZoB1REkGDWJgBv3G1UFJ2FOPlugHoEY%3D%0A&s=802504705b8bfd16199bf7eb285013e7f1d2f20f2b26023838e7633f9b8102db
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 18 2013 - 20:04:55 CET