Не могу найти объяснение работы sql запросов

#sql #oracle

Есть некоторые таблицы в 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$           |
-------------------------------------------------------------

    

Ответы

Ответ 1

В таких случаях важно рассматривать весь запрос в целом, а не его отдельные элементы.
Потому как изменение предпочтений оптимизатора по одной таблице может приводить к кардинальному
изменению его подхода к другим таблицам в этом же запросе.

В данном, конкретном, случае индекс по полю даты был вообще не причем. Oracle и не
пытался его использовать. Применение функции to_date к полю приводило к тому, что оптимизатор,
скорее всего, не использовал статистику по данному полю и предполагал наличие большого
количества записей в таблице по заданному условию. Что, в свою очередь, заставляло
оптимизатор выбирать более разумный план подклейки других двух таблиц и идти по тем,
другим таблицам по индексу.

А в варианте без to_date оптимизатор использовал статистику по полю даты, считал,
что по конкретной дате он выберет мало данных. А для малого количества данных выбираемых
на первом шаге он считал более оптимальным выполнить полное сканирование, без использования
индексов по второй таблице, что приводило к росту времени выполнения, т.к. данных оказывалось
больше, чем предполагал оптимизатор.

P.S. Из за наличия OR в полном запросе оптимизатор шел по пути выполнения двух разных
запросов для первой части OR и для второй (которая и была не оптимальной). Ситуация
была исправлена заменой условия OR таким образом, что бы в нем участвовали только поля
одной таблицы.