Oracle在进行值的大小比较时, 不是我们人脑所想象的那种值比较, 而是将这些值的物理存贮进行字典式的字节比较. 我们来看一下数值1和2的物理存贮.

SQL> SELECT DUMP(1) FROM DUAL;

Typ=2 Len=2: 193,2

SQL> SELECT DUMP(2) FROM DUAL;

Typ=2 Len=2: 193,3

    可以看到"1<2"就是象"AB<AC"这样比较的. 对于Timestamp类型也是这样的.

SQL> select dump(current_timestamp) as col1 from dual;

Typ=188 Len=20: 7,215,12,4,2,15,5,0,7,7,2,224,249,0,5,49,0,0,0,1

SQL> select dump(current_timestamp) as col1 from dual;

Typ=188 Len=20: 7,215,12,4,2,15,9,0,3,1,11,0,249,0,5,49,0,0,0,1

    对于Timestamp with time zone类型, 就不能靠这样直接来比较了, 而需要转换到UTC(+00:00)的值来进行比较, 因此在这种类型上建索引时, 实际是上建了函数索引.

SQL> CREATE TABLE T_TIMESTAMP(COL1 TIMESTAMP WITH TIME ZONE);
SQL> CREATE INDEX IDX_T_TIMESTAMP ON T_TIMESTAMP (COL1);
SQL> SELECT INDEX_TYPE, INDEX_NAME FROM USER_INDEXES
   2 WHERE TABLE_NAME='T_TIMESTAMP';

INDEX_TYPE                  INDEX_NAME
--------------------------- -----------------------
FUNCTION-BASED NORMAL       IDX_T_TIMESTAMP

    我们可以从SQL的执行计划中看到这一点, 使用了SYS_EXTRACT_UTC函数.

SQL> SELECT /*+ INDEX(T) */ * FROM T_TIMESTAMP T
   2 WHERE COL1 > CURRENT_TIMESTAMP;
......
2 - access(SYS_EXTRACT_UTC("COL1") > SYS_EXTRACT_UTC(CURRENT_TIMESTAMP(6)))

    对于Timestamp with local time zone类型, 都被转换成了和DBTIMEZONE一致的时区, 但在9i和10g中看执行计划时却有区别, 在10g中仍然被建成了函数索引, 而9i中则是普通索引.

9i : 2 - access ("T_TIMESTAMP"."COL2" > CURRENT_TIMESTAMP(6))
10g: 2 - access (SYS_EXTRACT_UTC(INTERNAL_FUNCTION("COL2"))>...)

    因此在9i中建的TIMESTAMP WITH LOCAL TIME ZONE上的索引, 在升级成10g后不能被用到.