oracle10G的表分割槽與繫結變數

最近公司的一個決策系統使用的查詢，總是無法完成分割槽消除，這樣在效能上會受一點影響。

不過在好些資料上說，分割槽與繫結變數二者不可兼得，但是想oracle都發展到10G了，既然使用了分割槽技術，而人們的查詢也不會繫結在全部分割槽上去掃描，因此一定有辦法對於繫結變數來消除分割槽，以提高效能。

現在做測試如下：

HR 08-11月-07 >CREATE TABLE t
2 (
3 dt date,
4 x int
5 )
6 PARTITION BY RANGE (dt)
7 (
8 PARTITION part1 VALUES LESS THAN (to_date('06-11-2007','dd-mm-yyyy'))
9 tablespace example,
10 PARTITION part2 VALUES LESS THAN (to_date('07-11-2007','dd-mm-yyyy'))
11 tablespace example,
12 PARTITION junk VALUES LESS THAN (MAXVALUE)
13 tablespace example
14 )
15 /

表已建立。

HR 08-11月-07 >delete from plan_table;

已刪除0行。

HR 08-11月-07 >explain plan for
2 select * from t where dt = to_date('8-11-2007','dd-mm-yyyy');

已解釋。

HR 08-11月-07 >select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1062289284

--------------------------------------------------------------------------------
---------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
Pstart| Pstop |

--------------------------------------------------------------------------------
---------------

| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 |
| |

| 1 | PARTITION RANGE SINGLE| | 1 | 22 | 2 (0)| 00:00:01 |
3 | 3 |

|* 2 | TABLE ACCESS FULL | T | 1 | 22 | 2 (0)| 00:00:01 |
3 | 3 |

--------------------------------------------------------------------------------
---------------

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

2 - filter("DT"=TO_DATE('2007-11-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

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

已選擇18行。

HR 08-11月-07 >variable x varchar2(30)
HR 08-11月-07 >delete from plan_table;

已刪除3行。

HR 08-11月-07 >explain plan for
2 select * from t where dt = to_date(:x,'dd-mm-yyyy');

已解釋。

HR 08-11月-07 >select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 313852716

--------------------------------------------------------------------------------
---------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
Pstart| Pstop |

--------------------------------------------------------------------------------
---------------

| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 |
| |

| 1 | PARTITION RANGE SINGLE| | 1 | 22 | 2 (0)| 00:00:01 |
KEY | KEY |

|* 2 | TABLE ACCESS FULL | T | 1 | 22 | 2 (0)| 00:00:01 |
KEY | KEY |

--------------------------------------------------------------------------------
---------------

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

2 - filter("DT"=TO_DATE(:X,'dd-mm-yyyy'))

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

已選擇18行。

HR 08-11月-07 >create index t_ind on t(dt);

索引已建立。

HR 08-11月-07 >exec dbms_stats.gather_table_stats(user,'T');

PL/SQL 過程已成功完成。

HR 08-11月-07 >exec dbms_stats.gather_index_stats(user,'t_ind');

PL/SQL 過程已成功完成。

HR 08-11月-07 >variable x date;
用法: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE ] ]
HR 08-11月-07 >variable x varchar2(20);
HR 08-11月-07 >delete from plan_table;

已刪除3行。

HR 08-11月-07 >explain plan for
2 select * from t where dt = to_date(:x,'dd-mm-yyyy');

已解釋。

"$ORACLE_HOME/rdbms/admin/utlxpls.sql"
HR 08-11月-07 >select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 866773112

--------------------------------------------------------------------------------
----------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | Pstart| Pstop |

--------------------------------------------------------------------------------
----------------------------

| 0 | SELECT STATEMENT | | 1 | 22 | 1 (0)
| 00:00:01 | | |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 22 | 1 (0)
| 00:00:01 | ROWID | ROWID |

|* 2 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)
| 00:00:01 | | |

--------------------------------------------------------------------------------
----------------------------

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

2 - access("DT"=TO_DATE(:X,'dd-mm-yyyy'))

已選擇14行。
多少說明繫結變數時能完成分割槽消除。

[@more@]

現在不明白我們的系統為什麼總是在12個分割槽中range scan。

這個問題困擾我好些時候了。由於水平有限，目前還不得知。

其實之前曾經想到另一個好辦法，就是使用滑動視窗技術，將一週的資料都放在一個表上，使用job自動建有日期字尾的表，將資料也放入，這樣最近一週的查詢都是透過此表來完成。

在合適的時機將此表與已經建立好的表做交換。這樣能順利完成資料的更新與老化。

不過現在有另一個問題，如果這樣做，效能是最佳化了，唯一缺點是在查詢時，如果只查詢本週最近資料會是從新表中查詢，如果查詢舊的，from後面就得跟舊錶了。這樣sql就得動態的了。還是不好。