Timestamp 与 Date 变量绑定与Oracle的自动分区

好久没有更新博客了，其实是工作中遇到的很多问题在Google上都能找到答案，也就没有记录下来的必要了。今天主要想聊一下在实际的系统中遇到的Oracle数据库的问题，希望对大家有一点点帮助就好。

我首先描述一下我所遇到的场景：我们的数据库用的是Oracle 11g，我想大家立马就对它的自动分区（Interval）有了基本的认识了，这是一个非常棒的功能，免除了在建表时弄一大堆建Range分区的代码，也免除了以后对数据库进行分区扩充的麻烦。当然利用JOB也是可以完成分区扩展的，但是既然Oracle提供了这么好的工具，何必跟自己过不去呢，其实是我自己太懒不想写。那么我再说一下我们具体遇到的问题，我们有一张类似这样的分区表：

CREATE TABLE TICKET(
    TCT_ID    NUMBER,
    TCT_DATE    DATE,    
    TCT_code VARCHAR2(5),
    TCT_DEPARTURE VARCHAR2(5),
    TCT_DESTINATION VARCHAR2(5)
) partition by range
 (TCT_DATE)
    interval
 (NUMTODSINTERVAL(1,‘DAY‘))
 (partition
         PT_TCT_DAY_01
        values less than (TO_DATE(‘2014-07-27‘,‘YYYY-MM-DD‘))
         nocompress);

看着挺复杂，其实就是一个简单的自动分区表，分区键为TCT_DATE（订票日期）。那么为什么要用自动分区表呢，其实就是因为数据太多了，比如有10亿条，用一张表即使有再好的索引，查询速度也会降下来的。这个分区表每天都会生成一个分区，那么只要在这个表上建一个local index，这里建了一个前缀本地索引：

CREATE INDEX IDX_COMBINE 
ON TICKET(TCT_DATE ,TCT_code ,TCT_DEPARTURE ,TCT_DESTINATION) LOCAL

就实现很不错的查询速度了，在10亿条数据的情况下，查询速度基本可以控制在10ms之内，这也基本是极限值了。我们的项目在我接手之前已经运行了1年多了，一开始速度跟预期的一样，查询的速度基本也就在10ms之内，这两天项目经理突然跟我说我们的系统插入极慢，让我去调优一下。

接到任务，跟我们的DBA要AWR，很快就发现系统的瓶颈出现在一条查询语句上：

SELECT * FROM TICKET T 
WHERE T.DATE=:D1 
AND T.TCT_code=:D2 
AND T.TCT_DEPATURE=:D3 
AND T.TCT_DESTINATION=:D4;

乍一看觉得挺好，进行了变量绑定，但是查询的速度飙升到200ms附近，同时物理读非常高，逻辑读也不是很低，并且db sequence file 读的频率也是非常高，这就说明两点：

1. 这个查询语句有可能进行了全表扫，没有分区消除；

2. 这个查询语句读取了大量的索引数据，有可能遍历了所有的分区的本地索引；

综合这条sql的SQL AWR REPORT基本可以得出一个结论，没有实现分区消除。

但是这个语句明明把分区条件都带上了，是一个前缀索引，那为什么Oracle要进行全表扫，这个让人非常的费解，我进行了一下的分析：

1. 这个查询语句效率底下的原因就是因为没有分区消除，遍历了所有的索引，这也是为什么刚开始程序效率还不错，但是随着数据越来越多，程序越来越慢；

2. 但是为什么这里明明有分区条件，但就是不走分区呢？映入脑海的第一个原因就是传进来的分区条件值无法匹配；

3. 遵循以上的思考，我查看了一下该项目的源代码，发现问题出现在这样的一个语句上，这个错误十分隐蔽，实在是很难找：

ps.setTimestamp(1, new Timestamp(date.getTime()));

数据库中book_flt_date是date类型，该语句实际上就是往sql里面注入java.sql.Date类型的值，也就是给book_flt_date赋值，这个地方把它换成了Timestamp,好了问题就出现了，由于timestamp是要精确到毫秒的，而Date只精确到秒，这就造成这两者无法匹配，造成的结果就是无法进行分区消除；

4. 如何解决这个问题呢？我进行了一下的改造：

ps.setDate(1, new java.sql.Date(date.getTime()));

这里利用了java.sql.Date,问题解决，查询语句立马进入10ms以内，看来是乖乖地走分区条件了！

只能感叹千里之堤毁于蚁穴啊，后来者且行且仔细吧。其实这里反映了一个之前的程序员对java.util.Date 和java.sql.Date的区别不是很清楚，此外对Timestap与Date 之间的关系也很模糊，其实这里完全可以利用ojdbc中oracle.sql.Date来代替。这一点小小的盲点，带来的后果也是非常严重的，以后学东西还是要多消除这些盲点。

Timestamp 与 Date 变量绑定与Oracle的自动分区,布布扣,bubuko.com