Function Based Index mit Trunc-Funktion für Date-Spalten

Franck Pachot erwähnt eine interessante Verbesserung in der Behandlung von Indizes in jüngeren Oracle-Versionen: seit 11.2.0.2 können funktionsbasierte Indizes, die die Trunc-Funktion für ein Datum beinhalten, auch verwendet werden, um eine Query zu unterstützen, in der auf das Datum ohne die Funktion eingeschränkt wird (also z.B. über einen Datums-Range). Das ist in der Beschreibung einmal mehr unhandlich, sollte aber durch ein kleines Beispiel leicht zu verdeutlichen sein:

drop table t;

create table t
as
select sysdate + interval '3' hour ts
  from dual
connect by level <= 1000;

create index t_idx_ts_trunc on t(trunc(ts));

explain plan for
select *
  from t
 where ts < sysdate - 10;

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T              |     1 |     8 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T_IDX_TS_TRUNC |     9 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TS"<SYSDATE@!-10)
   2 - access(TRUNC(INTERNAL_FUNCTION("TS"))<=TRUNC(SYSDATE@!-10))

In älteren Oracle-Releases wäre der Zugriff in dieser Query nicht über den FBI möglich gewesen, da die Einschränkung für die Spalte TS nicht der Index-Definition mit der Trunc-Funktion entspricht. Da der Index die Uhrzeitangaben des Zeitstempels nicht enthält, ist hier allerdings noch ein table access notwendig, den man sich sparen könnte, wenn man den Index noch um die TS-Spalte erweitert, also: 

create index t_idx_ts on t(trunc(ts), ts);

Im gegebenen Beispiel ist das natürlich recht zweckfrei (weil der Index dadurch größer wird als die Tabelle), aber Franck Pachots Artikel liefert da Plausibleres - und auch umfassendere Erklärungen zum Verhalten.

Nachtrag 31.03.2015: in einem Folgeartikel hat Franck Pachot darauf hingewiesen, dass ähnliche Optimierungen auch für FBI-s mit der substr-Funktion existieren - allerdings werden LIKE-Prädikate in diesem Zusammenhang nicht unterstützt.