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

From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Fri, 20 Dec 2013 15:32:57 -0800
Message-ID: <52B4D3A9.6090000_at_oracle.com>

Sounds like a job for valid time support in Oracle Database 12c:

http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14

With a valid time period enabled on a table, the application code merely needs to maintain the right "start time" and "end time" values for each row in the table, and then query the table using syntax like this:

select * from foo as of period for <valid_time_period> systimestamp;

-KJ

Kevin Jernigan
+1 650 607.0392 (office) | +1 415 710.8828 (mobile) Senior Director, Product Management, Data Layer Compression (ACO & HCC) | Resource Management (DBRM & IORM) Database Smart Flash Cache | Temporal SQL and CQN SecureFiles and filesystems (dNFS & DBFS & CloneDB) Database Development, Oracle

On 12/20/2013 11:42 AM, Jay.Miller_at_tdameritrade.com wrote:
> 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 Sat Dec 21 2013 - 00:32:57 CET