База данных Oracle имеет свои способы. В своих выступлениях по SQL на конференциях я люблю путать людей со следующими фактами Oracle:
… и ответ, конечно:
Разве не ужасно сделать пустую строку такой же, как NULL ? Пожалуйста, Оракул …
Единственный действительно разумный слайд, который следует за предыдущими двумя, это следующий:
Но тип DATE гораздо более тонкий
Так ты думаешь VARCHAR2 странный?
Хорошо, мы все знаем, что Oracle DATE самом деле не дата, как в стандарте SQL, или как во всех других базах данных, или как в java.sql.Date . Тип DATE Oracle на самом деле является TIMESTAMP(0) , то есть отметкой времени с точностью до секунды.
Большинство устаревших баз данных фактически используют DATE именно для этого, чтобы хранить временные метки без дробных секунд, например:
- 1970-01-01 00:00:00
- 2000-02-20 20:00:20
- 1337-01-01 13:37:00
Таким образом, всегда безопасно использовать типы java.sql.Timestamp в Java при работе с Oracle DATE .
Но когда вы связываете такие переменные через JDBC, все может пойти не так, как показано в этом вопросе переполнения стека . Предположим, у вас есть предикат диапазона, например:
|
1
2
3
4
5
|
// execute_at is of type DATE and there's an indexPreparedStatement stmt = connection.prepareStatement( "SELECT * " + "FROM my_table " + "WHERE execute_at > ? AND execute_at < ?"); |
Теперь, естественно, мы ожидаем, что любой индекс на execute_at будет разумным выбором для фильтрации записей из my_table , и это также то, что происходит, когда мы связываем java.sql.Date
|
1
2
|
stmt.setDate(1, start);stmt.setDate(2, end); |
План исполнения оптимален:
|
01
02
03
04
05
06
07
08
09
10
11
12
|
-----------------------------------------------------| Id | Operation | Name |-----------------------------------------------------| 0 | SELECT STATEMENT | ||* 1 | FILTER | || 2 | TABLE ACCESS BY INDEX ROWID| my_table ||* 3 | INDEX RANGE SCAN | my_index |-----------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(:1:1 AND ""EXECUTE_AT""<:2) |
Но давайте посмотрим, что произойдет, если мы предположим, что execute_at будет датой с часами / минутами / секундами, то есть Oracle DATE . Мы будем связывать java.sql.Timestamp
|
1
2
|
stmt.setTimestamp(1, start);stmt.setTimestamp(2, end); |
и план выполнения вдруг становится очень плохим
|
01
02
03
04
05
06
07
08
09
10
11
12
13
|
-----------------------------------------------------| Id | Operation | Name |-----------------------------------------------------| 0 | SELECT STATEMENT | ||* 1 | FILTER | || 2 | TABLE ACCESS BY INDEX ROWID| my_table ||* 3 | INDEX FULL SCAN | my_index |-----------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(:1:1 AND INTERNAL_FUNCTION(""EXECUTE_AT"")<:2)) |
Что это за INTERNAL_FUNCTION ()
INTERNAL_FUNCTION() — это способ Oracle для бесшумного преобразования значений в другие значения полностью непрозрачными способами. Фактически, вы не можете даже поместить индекс на основе функции в эту псевдофункцию, чтобы помочь базе данных снова выбрать для нее RANGE SCAN . Следующее невозможно:
|
1
2
|
CREATE INDEX oracle_why_oh_why ON my_table(INTERNAL_FUNCTION(execute_at)); |
Нет. На самом деле функция расширяет менее точный тип DATE столбца execute_at до более точного типа TIMESTAMP переменной bind. Так, на всякий случай.
Почему? Поскольку с исключительными границами диапазона (> и <), есть вероятность, что дробные секунды в вашей отметке Timestamp могут привести к тому, что отметка времени будет значительно больше, чем нижняя граница диапазона, которая будет включать его, когда одна и та же Timestamp без дробных сечений ( то есть Oracle DATE ) были бы исключены.
Duh. Но нам все равно, мы используем Timestamp как глупый обходной путь! Теперь, когда мы знаем это, вы можете подумать, что добавление индекса на основе функции при явном преобразовании будет работать, но это не так:
|
1
2
|
CREATE INDEX nope ON my_table(CAST(execute_at AS TIMESTAMP)); |
Возможно, если вы волшебным образом нашли точную правильную точность неявно используемого типа TIMESTAMP(n) он может сработать, но это кажется шатким, и, кроме того, я не хочу второй индекс для того же столбца!
Решение
Решение, данное пользователем APC, на самом деле очень простое (и отстой). Опять же, вы можете связать java.sql.Date , но это приведет к потере всей информации о часах / минутах / java.sql.Date . Нет, вы должны явно привести переменную связывания к DATE в базе данных. Именно!
|
1
2
3
4
5
|
PreparedStatement stmt = connection.prepareStatement( "SELECT * " + "FROM my_table " + "WHERE execute_at > CAST(? AS DATE) " + "AND execute_at < CAST(? AS DATE)"); |
Это необходимо делать каждый раз, когда вы привязываете переменную java.sql.Timestamp к значению Oracle DATE , по крайней мере при использовании в предикатах.
Как это реализовать?
Если вы используете JDBC напрямую, вы в значительной степени обречены. Конечно, вы можете запускать отчеты AWR, чтобы находить худшие заявления в работе и исправлять только их, но есть вероятность, что вы не сможете так легко исправить ваши заявления и развернуть их так быстро, что, возможно, вы захотите получить их правильно. заблаговременно. И, конечно же, это производство. Завтра в отчетах вашего администратора вдруг появится другое заявление.
Если вы используете JPA / Hibernate, вы можете только надеяться, что они поняли это правильно, потому что вы, вероятно, не сможете исправить эти запросы, в противном случае.
Если вы используете jOOQ 3.5 или более позднюю версию , вы можете воспользоваться новой функцией привязки пользовательских типов jOOQ , которая работает из коробки с Oracle и прозрачно отображает этот CAST(? AS DATE) для вас только в тех столбцах, которые действительно актуально.
Другие базы данных
Если вы думаете, что это проблема Oracle, подумайте еще раз. Oracle на самом деле очень мягок и приятен в использовании, когда дело касается связывания переменных. Oracle может вывести множество типов для ваших переменных связывания, так что приведение почти никогда не требуется. С другими базами данных это другая история. Прочитайте нашу статью о безумии RDBMS привязки переменных для получения дополнительной информации
| Ссылка: | Правильно ли вы связываете свои даты Oracle? Бьюсь об заклад, вы не от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ . |