Странное поведение метки времени Oracle

Я застрял.

Oracle показывает странное поведение при обработке меток времени, позвольте мне объяснить:

У меня есть простая таблица с первичным ключом и индексом. AUDIT_FROM_TS является частью первичного ключа. Он разделен с использованием AUDIT_FROM_TS с месячным интервалом.

Соответствующий 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')) 
...

При выполнении этого запроса:

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

я получил

ORA-01841: (полный) год должен быть между -4713 и +9999, а не 0 01841. 00000 - "(полный) год должен быть между -4713 и +9999, а не 0" * Причина: введен неверный год *Действие: введите год в указанном диапазоне

Но этот работает нормально:

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

Я сделал небольшую отладку, и если увеличить дату до 16 декабря 9999 года, выдается та же ошибка.

Теперь больше отладки...

SELECT DBTIMEZONE from dual;

возвращает +00:00

SELECT SESSIONTIMEZONE FROM dual;

возвращает Europe/London

Может ли кто-нибудь помочь с этим, пожалуйста? Я не на 100% уверен, что это проблема часового пояса, так как это сместит дату на 2 недели...

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';

все это похоже на правду...


sql oracle timestamp oracle12c timestamp-with-timezone
person Palcente    schedule 29.07.2015    source источник
comment
Можете ли вы попробовать добавить часовой пояс UTC в литерал временной метки: timestamp '9999-12-31 00:00:00 00:00'   -  person dnoeth    schedule 29.07.2015
comment
Привет, dnoeth, спасибо за вклад... Я добавил запросы, которые работают (включая часовой пояс). К сожалению, это не решает мою проблему, у меня есть множество таблиц, определенных таким образом, и общее задание, которое записывает в них. Это единственная таблица, показывающая такое поведение....   -  person Palcente    schedule 29.07.2015
comment
Каков тип данных AUDIT_FROM_TS? (Вы указали тип данных для VALID_FROM_DT, но это не то, что вы используете в запросе). Не могли бы вы обновить оператор создания таблицы, чтобы включить этот столбец, пожалуйста?   -  person Boneist    schedule 29.07.2015
comment
мой плохой, извините, скопировал не тот столбец... обновлено сейчас, у другого столбца такие же проблемы   -  person Palcente    schedule 29.07.2015
comment
Играя с этим, если дата перехода увеличивается с первого числа месяца на второе число месяца, ORA-01841 не попадает до 9999-12-17. Так что, похоже, это как-то связано с диапазоном разделов, предположительно, с некоторыми внутренними расчетами, прошедшими конец этого месяца.   -  person Alex Poole    schedule 30.07.2015
comment
Вы также не можете вставить запись ›= 9999-12-01, потому что верхнее значение раздела будет 10000-01-01 (который упоминается в документации). Так что, если у вас все равно нет даты в этом месяце, возможно, наличие вашего фильтра ‹ 9999-12-15 не имеет никакого практического значения?   -  person Alex Poole    schedule 30.07.2015
comment
Привет Алекс, спасибо за ответ. У меня есть тонны записей с датой 9999-12-31, так как это означает «навсегда» для временных шкал битемпоральных транзакций.   -  person Palcente    schedule 30.07.2015
comment
Вы используете эту дату в столбце, который используется для разделения с месячными интервалами? Это нормально, если значение не используется в качестве ключа раздела.   -  person Alex Poole    schedule 30.07.2015
comment
Похоже, вы правы, Алекс, я не храню это значение в столбце раздела. Также проверено, я не могу сохранить это значение в этом столбце.   -  person Palcente    schedule 30.07.2015

Ответы (1)


arrow_upward
1
arrow_downward

Кажется, что Oracle использует округление даты при поиске раздела, в который вписывается дата вашего фильтра, и проверка достоверности с высокой ценностью после 16 декабря округляется до 10000 года.

Когда вы меняете тип данных вашего фильтра, добавляя компонент часового пояса, запрос работает, потому что вы принудительно выполняете преобразование значений столбца, что предотвращает использование диапазонов секций; указание GMT ​​изменяет план с:

----------------------------------------------------------------------------------------------------------                                                                                              
| 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')

Неявный SYS_EXTRACT_UTC заставляет его использовать PARTITION RANGE ALL, что не имеет значения, если вы только используете этот высокий фильтр (хотя в любом случае это немного избыточно); но это могло бы иметь большее влияние, если бы вы также выполняли поиск с низким значением.

Но если у вас есть интервальное разбиение с датой перехода в качестве первого числа месяца, что, по-видимому, имеет место в данном случае, вы все равно не сможете вставить записи со значением audit_from_ts в декабре 9999 года, потому что для этого потребуется раздел с высоким значением 10000-01-01, что не является официальной датой. Это упоминается в документации:

Например, если вы создаете секционированную таблицу интервалов с месячными интервалами и точкой перехода является 1 января 2010 г., то нижняя граница интервала января 2010 г. — 1 января 2010 г. Нижняя граница интервала июля 2010 г. — 1 июля. , 2010, независимо от того, был ли ранее создан раздел за июнь 2010 года. Обратите внимание, однако, что использование даты, когда верхняя или нижняя граница раздела выходит за пределы диапазона, установленного для хранилища, вызывает ошибку. Например, TO_DATE('9999-12-01', 'YYYY-MM-DD') приводит к тому, что верхняя граница равна 10000-01-01, что невозможно сохранить, если 10000 выходит за допустимый диапазон.

Поэтому, если вы не можете получить значения в этом месяце, на самом деле не имеет никакого логического значения, используете ли вы 9999-12-31, 9999-12-15 или даже 9999-12-01 для своего фильтра. (Вы могли бы заставить запрос работать с 9999-12-31, установив дату перехода 18-го числа месяца, но это было бы немного странно, и вы все равно не могли бы вставить запись после 9999-12-17).

Oracle не считает это ошибкой. Подробнее об этом можно прочитать в документе My Oracle Support 1507993.1.

person Alex Poole    schedule 30.07.2015
comment
Честно говоря, это похоже на ошибку. Большое спасибо за ваш ответ. Это очень помогло! - person Palcente; 30.07.2015