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就得动态的了。还是不好。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/564597/viewspace-981260/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/564597/viewspace-981260/