CODEWITHSUNDEEP
sqloracle-databasetimestamporacle12ctimestamp-with-timezone
Palcente
Palcente

Reputation: 635

Strange Oracle Timestamp Behaviour

I'm stuck.

Oracle shows weird behaviour when handling timestamps, let me explain:

I have a simple table with primary key and an index. AUDIT_FROM_TS is a part of a primary key. It's partitioned using AUDIT_FROM_TS on a monthly interval.

Relevant DDL

CREATE TABLE "SDR"."TRADE_DEAL_F"(
...
"AUDIT_FROM_TS" TIMESTAMP (9) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE,
...
CONSTRAINT "PK_TRADE_DEAL" PRIMARY KEY ("TRADE_DEAL_ID", "VALID_FROM_DT", "AUDIT_FROM_TS")
...
PARTITION BY RANGE ("AUDIT_FROM_TS") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
...

When running this query:

select count(*) from trade_deal_f where AUDIT_FROM_TS < timestamp '9999-12-31 00:00:00';

I get

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0" *Cause: Illegal year entered *Action: Input year in the specified range

But this one works just fine:

select count(*) from trade_deal_f where AUDIT_FROM_TS < timestamp '9999-12-15 00:00:00';

I've done a little debugging and if increment the date to the 16th of December 9999, same error is thrown.

Now more debugging...

SELECT DBTIMEZONE from dual;

returns +00:00

SELECT SESSIONTIMEZONE FROM dual;

returns Europe/London

Can anyone help with this please ? I am not 100% sure it's the timezone issue, as it would be offsetting the date by 2 weeks...

select count(*) from trade_deal_f where AUDIT_FROM_TS = timestamp '9999-12-31 00:00:00 Europe/London';
select count(*) from trade_deal_f where AUDIT_FROM_TS = timestamp '9999-12-31 00:00:00 GMT';
select count(*) from trade_deal_f where AUDIT_FROM_TS = timestamp '9999-12-31 00:00:00 UTC';
select count(*) from trade_deal_f where AUDIT_FROM_TS = timestamp '9999-12-31 00:00:00 +00:00';

all these seem to be valid...

Upvotes: 1

Views: 1136

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

It seems Oracle is using date rounding when looking for the partition your filter date fits in, and a high-value validity check after December 16th is being rounded up past year 10000.

When you change the data type of your filter by adding a time zone component, the query works because you're forcing a conversion on the column values, which prevents the partition ranges being used; specifying GMT changes the plan from:

----------------------------------------------------------------------------------------------------------                                                                                              
| Id  | Operation                 | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                              
----------------------------------------------------------------------------------------------------------                                                                                              
|   0 | SELECT STATEMENT          |              |     1 |    13 |   120   (2)| 00:00:02 |       |       |                                                                                              
|   1 |  SORT AGGREGATE           |              |     1 |    13 |            |          |       |       |                                                                                              
|   2 |   PARTITION RANGE ITERATOR|              |   112K|  1430K|   120   (2)| 00:00:02 |     1 |     3 |                                                                                              
|*  3 |    TABLE ACCESS FULL      | TRADE_DEAL_F |   112K|  1430K|   120   (2)| 00:00:02 |     1 |     3 |                                                                                              
----------------------------------------------------------------------------------------------------------                                                                                              


PLAN_TABLE_OUTPUT                                                                                                                                                                                      
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     

   3 - filter("AUDIT_FROM_TS"<TIMESTAMP' 9999-12-15 00:00:00.000000000')

to

-----------------------------------------------------------------------------------------------------                                                                                                   
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                   
-----------------------------------------------------------------------------------------------------                                                                                                   
|   0 | SELECT STATEMENT     |              |     1 |    13 |   124   (5)| 00:00:02 |       |       |                                                                                                   
|   1 |  SORT AGGREGATE      |              |     1 |    13 |            |          |       |       |                                                                                                   
|   2 |   PARTITION RANGE ALL|              |   112K|  1430K|   124   (5)| 00:00:02 |     1 |1048575|                                                                                                   
|*  3 |    TABLE ACCESS FULL | TRADE_DEAL_F |   112K|  1430K|   124   (5)| 00:00:02 |     1 |1048575|                                                                                                   
-----------------------------------------------------------------------------------------------------                                                                                                   


PLAN_TABLE_OUTPUT                                                                                                                                                                                      
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     

   3 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("AUDIT_FROM_TS"))<TIMESTAMP' 9999-12-15                                                                                                                 
              00:00:00.000000000')

The implicit SYS_EXTRACT_UTC causes it to use PARTITION RANGE ALL, which doesn't matter if you're only using that high filter (though it's a bit redundant anyway); but might have more of an impact if you were search from a low value too.

But if you have interval partitioning with the transition date as the first of a month, which seems to be the case here, you cannot insert any records with an audit_from_ts value in December 9999 anyway, because that would require a partition with a high value of 10000-01-01, which is not a legal date. This is mentioned in the documentation:

For example, if you create an interval partitioned table with monthly intervals and the transition point is at January 1, 2010, then the lower boundary for the January 2010 interval is January 1, 2010. The lower boundary for the July 2010 interval is July 1, 2010, regardless of whether the June 2010 partition was previously created. Note, however, that using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.

So if you can't have values in that month, it doesn't really make any logical difference whether you use 9999-12-31, 9999-12-15, or even 9999-12-01 for your filter. (You could make the query work with 9999-12-31 by making the transition date the 18th of the month, but that would be a bit weird, and you still couldn't insert a record after 9999-12-17).

Oracle don't consider this to be a bug. You can read more about it in My Oracle Support document 1507993.1.

Upvotes: 1

Related Questions