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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 23 Dec 2013 21:34:11 -0500
Message-ID: <040301cf0050$a228a4b0$e679ee10$_at_rsiz.com>

I am available at as high a rate you need me to charge to be taken seriously.

Since I am in fact a tdameritrade customer....

-----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: Monday, December 23, 2013 5:02 PM
To: mwf_at_rsiz.com; oracle-l_at_freelists.org Subject: RE: Using 12/31/9999 in a date field

In the "real world" developers don't know how to do this.

This is an expensive consultant brought in for a project so he knows best and I'm causing problems.

Jay Miller
Sr. Oracle Database Administrator
201.369.8355
-----Original Message-----

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Friday, December 20, 2013 6:47 PM
To: Miller, Jay; oracle-l_at_freelists.org Subject: RE: Using 12/31/9999 in a date field

Hmm.

where (<context date> is <= nvl(end_date,to_date('99991231','YYYYMMDD') and <context date> >= start_date)

does not involve an OR and seems pretty clear to me.

mwf

-----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 2: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.co m/all-postings&k=%2FJMyfAnQZOhZ4dnr8BYv6w%3D%3D%0A&r=DUCASJ0xPpeOmtQ4idyytbA D3iZK7MOOurZhynw7YaA%3D%0A&m=NrNs4jma5vdpv2gORcG72CIsvHQaPpYTaKAkSjBn7PE%3D% 0A&s=17cbd7411182cdf3f0efbc53ab3aa0e01acf67d5a15b896fed397d63e42875fc

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

• 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=DUCASJ0xPpeOmtQ4idyytbAD3iZK7 MOOurZhynw7YaA%3D%0A&m=NrNs4jma5vdpv2gORcG72CIsvHQaPpYTaKAkSjBn7PE%3D%0A&s=b 312cdf25945ead97a8b3b9e3466e8755e8b353679fbb9aa9ad405051749e74d

--

https://urldefense.proofpoint.com/v1/url?u=http://www.freelists.org/webpage/ oracle-l&k=%2FJMyfAnQZOhZ4dnr8BYv6w%3D%3D%0A&r=DUCASJ0xPpeOmtQ4idyytbAD3iZK7 MOOurZhynw7YaA%3D%0A&m=24R%2BJrcJy4M%2BKy9ksVsUA5DMqV19XU7cgPn8zufPdYU%3D%0A &s=b7183bab2548a58cfc060bfd0987735cc95a95eb9096e51e9a6da7fcfd86ca97

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Dec 24 2013 - 03:34:11 CET