Timestamp 與 Date 變數綁定與Oracle的自動分區

標籤：des   style   java   strong   資料   io   2014   art   

好久沒有更新部落格了，其實是工作中遇到的很多問題在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來代替。這一點小小的盲點，帶來的後果也是非常嚴重的，以後學東西還是要多消除這些盲點。