RE: Using 12/31/9999 in a date field
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Jay.Miller@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Dec 2013 18:46:40 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Friday, December 20, 2013 2:42 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Thursday, December 19, 2013 2:58 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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=//www.freelists.org/webpage/
oracle-l&k=%2FJMyfAnQZOhZ4dnr8BYv6w%3D%3D%0A&r=DUCASJ0xPpeOmtQ4idyytbAD3iZK7
MOOurZhynw7YaA%3D%0A&m=NrNs4jma5vdpv2gORcG72CIsvHQaPpYTaKAkSjBn7PE%3D%0A&s=b
312cdf25945ead97a8b3b9e3466e8755e8b353679fbb9aa9ad405051749e74d
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
