Есть некоторые таблицы в oracle.
Так вот собственно попрос: почему иногда конструкция типа
select * from TABLE t where to_char(t.field) = 'somevalue'
работает лучше(т.е. быстрее отрабатывает, план запроса лучше, меньше стоимость запроса) чем
select * from TABLE t where t.field = 'somevalue'
Данное поле проиндексировано. То самое с to_date.
Такое чувство что индекс по неведомым причинам не подхватывается при "прямых" запросах.
Особенно такое проявляется в сложных запросах, где объединяется несколько таблиц.
Версия oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Количество данных при выборе не меняется.
План ДО использования to_date на TABLE2
select *
from TABLE1, TABLE2, TABLE3
where TABLE1.id = TABLE2.TABLEID
and TABLE1.id = TABLE3.TABLEID
and TABLE3.DATAFIELD + 1 > trunc(sysdate)
and TABLE1.DATAFIELD + 1 > trunc(sysdate)
and ((TABLE2.DATAFIELD = trunc(sysdate)) or (TABLE1.ID = nvl(0, 0)))
-------------------------------------------------------------
Plan hash value: 2464832983
-------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
| 5 | TABLE ACCESS BY INDEX ROWID| TABLE1$ |
| 6 | INDEX UNIQUE SCAN | PK_TABLE1$ |
| 7 | TABLE ACCESS BY INDEX ROWID| TABLE2$ |
| 8 | INDEX RANGE SCAN | TABLE2$TABLEID |
| 9 | INDEX RANGE SCAN | TABLE3$TABLEID |
| 10 | TABLE ACCESS BY INDEX ROWID | TABLE3$ |
| 11 | HASH JOIN | |
| 12 | TABLE ACCESS FULL | TABLE2$ |
| 13 | NESTED LOOPS | |
| 14 | NESTED LOOPS | |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
| 15 | TABLE ACCESS FULL | TABLE3$ |
| 16 | INDEX UNIQUE SCAN | PK_TABLE1$ |
| 17 | TABLE ACCESS BY INDEX ROWID | TABLE1$ |
-------------------------------------------------------------
План ПОСЛЕ использования to_date на TABLE2
select *
from TABLE1, TABLE2, TABLE3
where TABLE1.id = TABLE2.TABLEID
and TABLE1.id = TABLE3.TABLEID
and TABLE3.DATAFIELD + 1 > trunc(sysdate)
and TABLE1.DATAFIELD + 1 > trunc(sysdate)
and ((to_date(TABLE2.DATAFIELD) = trunc(sysdate)) or (TABLE1.ID = nvl(0, 0)))
-------------------------------------------------------------
Plan hash value: 1081346625
-------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
| 5 | TABLE ACCESS BY INDEX ROWID| TABLE1$ |
| 6 | INDEX UNIQUE SCAN | PK_TABLE1$ |
| 7 | TABLE ACCESS BY INDEX ROWID| TABLE2$ |
| 8 | INDEX RANGE SCAN | TABLE2$TABLEID |
| 9 | INDEX RANGE SCAN | TABLE3$TABLEID |
| 10 | TABLE ACCESS BY INDEX ROWID | TABLE3$ |
| 11 | NESTED LOOPS | |
| 12 | NESTED LOOPS | |
| 13 | NESTED LOOPS | |
| 14 | TABLE ACCESS FULL | TABLE2$ |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
| 15 | TABLE ACCESS BY INDEX ROWID| TABLE1$ |
| 16 | INDEX UNIQUE SCAN | PK_TABLE1$ |
| 17 | INDEX RANGE SCAN | TABLE3$TABLEID |
| 18 | TABLE ACCESS BY INDEX ROWID | TABLE3$ |
-------------------------------------------------------------
Ответ
В таких случаях важно рассматривать весь запрос в целом, а не его отдельные элементы. Потому как изменение предпочтений оптимизатора по одной таблице может приводить к кардинальному изменению его подхода к другим таблицам в этом же запросе.
В данном, конкретном, случае индекс по полю даты был вообще не причем. Oracle и не пытался его использовать. Применение функции to_date к полю приводило к тому, что оптимизатор, скорее всего, не использовал статистику по данному полю и предполагал наличие большого количества записей в таблице по заданному условию. Что, в свою очередь, заставляло оптимизатор выбирать более разумный план подклейки других двух таблиц и идти по тем, другим таблицам по индексу.
А в варианте без to_date оптимизатор использовал статистику по полю даты, считал, что по конкретной дате он выберет мало данных. А для малого количества данных выбираемых на первом шаге он считал более оптимальным выполнить полное сканирование, без использования индексов по второй таблице, что приводило к росту времени выполнения, т.к. данных оказывалось больше, чем предполагал оптимизатор.
P.S. Из за наличия OR в полном запросе оптимизатор шел по пути выполнения двух разных запросов для первой части OR и для второй (которая и была не оптимальной). Ситуация была исправлена заменой условия OR таким образом, что бы в нем участвовали только поля одной таблицы.
Комментариев нет:
Отправить комментарий