База данных 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, все может пойти не так, как показано в этом вопросе переполнения стека . Предположим, у вас есть предикат диапазона, например:
// execute_at is of type DATE and there's an index
PreparedStatement stmt = connection.prepareStatement(
"SELECT * " +
"FROM my_table " +
"WHERE execute_at > ? AND execute_at < ?");
Теперь, естественно, мы ожидаем, что любой индекс execute_atбудет разумным выбором для фильтрации записей my_table, и это также то, что происходит, когда мы связываемjava.sql.Date
stmt.setDate(1, start); stmt.setDate(2, end);
План исполнения оптимален:
----------------------------------------------------- | 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
stmt.setTimestamp(1, start); stmt.setTimestamp(2, end);
и план выполнения вдруг становится очень плохим
-----------------------------------------------------
| 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снова выбрать элемент . Следующее невозможно:
CREATE INDEX oracle_why_oh_why ON my_table(INTERNAL_FUNCTION(execute_at));
Нет. Что функция действительно делает, она расширяет менее точный DATEтип execute_atстолбца до более точного TIMESTAMPтипа переменной связывания. Так, на всякий случай.
Зачем? Поскольку с исключительными границами диапазона (> и <), есть вероятность, что дробные секунды в вашем Timestampмогут привести к тому, что временная метка будет строго больше нижней границы диапазона, которая будет включать его, когда то же самое Timestampбез дробных сечений (т.е. Oracle DATE) был бы исключен.
Duh. Но нам все равно, мы используем Timestampв качестве глупого обходного пути! Теперь, когда мы знаем это, вы можете подумать, что добавление индекса на основе функции при явном преобразовании будет работать, но это не так:
CREATE INDEX nope ON my_table(CAST(execute_at AS TIMESTAMP));
Возможно, если вы волшебным образом нашли точную правильную точность неявно используемого TIMESTAMP(n)типа, он может работать, но все это кажется шатким, и, кроме того, я не хочу второй индекс для того же столбца!
Решение
Решение, данное пользователем APC, на самом деле очень простое (и отстой). Опять же, вы можете связать java.sql.Date, но это приведет к потере всей информации о часах / минутах / секундах. Нет, вы должны явно привести переменную bind к DATEбазе данных. Точно!
PreparedStatement stmt = connection.prepareStatement(
"SELECT * " +
"FROM my_table " +
"WHERE execute_at > CAST(? AS DATE) " +
"AND execute_at < CAST(? AS DATE)");
Это необходимо делать каждый раз, когда вы привязываете java.sql.Timestampпеременную к DATEзначению Oracle , по крайней мере, при использовании в предикатах.
Как это реализовать?
Если вы используете JDBC напрямую, вы в значительной степени обречены. Конечно, вы можете запускать отчеты AWR, чтобы находить худшие заявления в работе и исправлять только их, но есть вероятность, что вы не сможете так легко исправить ваши заявления и развернуть их так быстро, что, возможно, вы захотите получить их правильно. заранее, авансом. И, конечно же, это производство. Завтра в отчетах вашего администратора вдруг появится другое заявление.
Если вы используете JPA / Hibernate, вы можете только надеяться, что они поняли это правильно, потому что вы, вероятно, не сможете исправить эти запросы, в противном случае.
Если вы используете jOOQ 3.5 или более позднюю версию , вы можете воспользоваться новой функцией привязки пользовательских типов jOOQ , которая работает из коробки с Oracle и прозрачно отображает CAST(? AS DATE)ее только для тех столбцов, которые действительно актуальны.
Другие базы данных
Если вы думаете, что это проблема Oracle, подумайте еще раз. Oracle на самом деле очень мягок и приятен в использовании, когда дело касается связывания переменных. Oracle может вывести множество типов для ваших переменных связывания, так что приведение почти никогда не требуется. С другими базами данных это другая история. Прочитайте нашу статью о безумии RDBMS привязки переменных для получения дополнительной информации.