关于oracle中的timestamp和date类型

之前一直认为类似:where timestamp>date 这种子句是不走索引的

下面简单做一个验证:

c:>sqlplus / as sysdba
sys@EOS >create table test as select table_name,to_timestamp(last_analyzed) date_test from dba_tables;

表已创建。

sys@EOS> create index idx_test_date on test (date_test);

索引已创建。

sys@EOS> desc test
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- ----------------
--------------------
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 DATE_TEST                                                      TIMESTAMP(0)

sys@EOS> select date_test from test where date_test > TO_DATE('2007-11-5 00:00:00','yyyy-MM-dd HH24:mi:ss');

执行计划
----------------------------------------------------------
Plan hash value: 944171586

-------------------------------------------------------------------------------- --
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- --
|   0 | SELECT STATEMENT |               |     1 |    22 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_TEST_DATE |     1 |    22 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------- --

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

   1 - access("DATE_TEST">TIMESTAMP'2007-11-05 00:00:00')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        280  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

从上面可以清楚看到,timestamp>date情况下,走索引

纠正我之前的认识。

另外再补充一下,date这个数据类型一般情况下很少用,建议产品里面所有的date数据类型全部改为timestamp

posted on 2007-12-26 00:42 tacy lee 阅读(1894) 评论(0)  编辑  收藏 所属分类: 性能相关数据库


只有注册用户登录后才能发表评论。


网站导航: