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

From: TJ Kiernan <tkiernan_at_pti-nps.com>
Date: Fri, 20 Dec 2013 21:20:51 +0000
Message-ID: <196DB2D4BDE5804EAF3158CCC1C698BC09B3D8C8_at_lopez.pti-nps.com>

That's a far cry from "Tom Kyte says it's ok." I'd be inclined to force the developers to account for nulls for their insolence, but that's me. It sound like they're saying, "We suck at writing code." Not a great standpoint for people who write code for a living.

Most of the code we have to deal with null termination dates goes like, WHERE <context_date> between EFFECTIVE_DATE and nvl(TERMINATION_DATE, <context_date> + 1).

You're probably not going to index exclusively on the termination/expiration date (range-based predicates typically go towards the end of an index), and as long as the index entry is not completely null, the termination date will be indexed.

I suppose the real question is whether your development staff turns over enough for this to be an ongoing problem, or can enough of them learn and teach the others.

Good luck!
-T. J.
 

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jay.Miller_at_tdameritrade.com Sent: Friday, December 20, 2013 1:42 PM
To: oracle-l_at_freelists.org
Subject: RE: Using 12/31/9999 in a date field

I got the use case. Basically, the developer says that developers don't know how to code for null values and that's why this is preferable.

"The answer fails to recognize the realities of real-world usage.

In a scenario in which Point-In-Time ("PIT") queries are common, and the database is being used as a read-mostly repository of client master records, all of the queries will expect a context of "for what date do you want to see the record of the client's info?". This means that the WHERE clause will always contain

Where <context date> is between Effective_Date and Expiration_Date

If the Expiration_Date is Null instead of containing an "infinite date" like 12-31-9999, the query needs to have

Where (<context date> is between Effective_Date and Expiration_Date) Or (<context date> >= Effective_Date and Expiration_Date is null)

Which is unintuitive and has a high rate of developer errors across all of the applications/tools that write queries against the data.

Also, since b-tree indexes do not index Nulls, an index combine cannot be used to satisfy the queries."

I'm thinking an NVL function in a function based index would allow decent performance for the latter query but I'm not sure if this is worth fighting over since the column will only be accessed for reporting purposes.

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: Thursday, December 19, 2013 2:58 AM To: oracle-l_at_freelists.org
Subject: Re: Using 12/31/9999 in a date field

That's a very good point, of course.

There are a number of side effects that could follow from (a) indexing on just a date-only column, and (b) indexing a column where a single value can have a large number of rows (especially with a constant stream of new rows appearing and old rows disappearing). You may be heading for the worst possible combination - though how much that matters does depend to a very large extent on the actual pattern of use of the column.

Regards

Jonathan Lewis
https://urldefense.proofpoint.com/v1/url?u=http://jonathanlewis.wordpress.com/all-postings&k=%2FJMyfAnQZOhZ4dnr8BYv6w%3D%3D%0A&r=DUCASJ0xPpeOmtQ4idyytbAD3iZK7MOOurZhynw7YaA%3D%0A&m=NrNs4jma5vdpv2gORcG72CIsvHQaPpYTaKAkSjBn7PE%3D%0A&s=17cbd7411182cdf3f0efbc53ab3aa0e01acf67d5a15b896fed397d63e42875fc

Author: Oracle Core (Apress 2011)
https://urldefense.proofpoint.com/v1/url?u=http://www.apress.com/9781430239543&k=%2FJMyfAnQZOhZ4dnr8BYv6w%3D%3D%0A&r=DUCASJ0xPpeOmtQ4idyytbAD3iZK7MOOurZhynw7YaA%3D%0A&m=NrNs4jma5vdpv2gORcG72CIsvHQaPpYTaKAkSjBn7PE%3D%0A&s=10b73123c1fedb0b71610aea45bd1aec304ee4c8447005d3f51acadd1506c31d

• Original Message ----- From: "Michael Haddon" <m.haddon_at_tx.rr.com> To: <oracle-l_at_freelists.org> Sent: Thursday, December 19, 2013 12:54 AM Subject: Re: Using 12/31/9999 in a date field

| This is a perfect case of a lopsided index if the column is indexed. It
| would result in a huge number of gaps in the leaf blocks as the records
| were updated to a valid date value. I would never recommend this.
|

--
https://urldefense.proofpoint.com/v1/url?u=http://www.freelists.org/webpage/oracle-l&k=%2FJMyfAnQZOhZ4dnr8BYv6w%3D%3D%0A&r=DUCASJ0xPpeOmtQ4idyytbAD3iZK7MOOurZhynw7YaA%3D%0A&m=NrNs4jma5vdpv2gORcG72CIsvHQaPpYTaKAkSjBn7PE%3D%0A&s=b312cdf25945ead97a8b3b9e3466e8755e8b353679fbb9aa9ad405051749e74d


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 20 2013 - 22:20:51 CET