数据类型转换和PARTITION PURGE

                           数据类型转换和PARTITION PURGE

      在oracle数据库使用分区很重要的一个性能好处就是当在where条件有
partition key的过滤时，oracle会根据partition key来决定只访问某些特
定的分区来满足返回的结果,从而减少访问的数据块，提高性能，这种
功能叫作partition purge。

    当where条件中的数据类型和表的partition key的类型不一样时，partition
purge就会失效，这种情况下只能做全表扫描了。来看一个例子，先创建表

SQL> create table t1(a date,b int)
  2  partition by range(a)
  3  (partition p1 values less than (to_date('2001-01-01 00:00:01','YYYY-MM-DD HH24:MI:SS')),
  4   partition p2 values less than (to_date('2002-01-01 00:00:01','YYYY-MM-DD HH24:MI:SS')),
  5   partition p3 values less than (to_date('2003-01-01 00:00:01','YYYY-MM-DD HH24:MI:SS')),
  6   partition p4 values less than (to_date('2004-01-01 00:00:01','YYYY-MM-DD HH24:MI:SS')),
  7   partition p5 values less than (to_date('2005-01-01 00:00:01','YYYY-MM-DD HH24:MI:SS')));

Table created.

 插入五行测试数据，每个分区中都有一条记录
insert into t1 values(to_date('2001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),1);
insert into t1 values(to_date('2002-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),2);
insert into t1 values(to_date('2003-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),3);
insert into t1 values(to_date('2004-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),4);
insert into t1 values(to_date('2005-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),5);

1，显示的转换,在sql中明显的类型不一样，这种比较好发现

SQL> set autotrace traceonly exp;

SQL> select *
  2  from t1
  3  where to_char(a,'YYYY-MM-DD HH24:MI:SS')='2001-01-01 00:00:01';

Execution Plan
----------------------------------------------------------
Plan hash value: 589593414

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    22 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|      |     1 |    22 |     3   (0)| 00:00:01 |     1 |     5 |
|*  2 |   TABLE ACCESS FULL | T1   |     1 |    22 |     3   (0)| 00:00:01 |     1 |     5 |
--------------------------------------------------------------------------------------------

从执行计划来看是走的分区全扫描，正常的执行应该是单个分区就可以满足查询的条件了.

对where条件的右边转换成不一样的类型.

SQL> select *
  2   from t1
  3   where a=to_timestamp('2001-01-01 00:00:01','YYYY-MM-DD HH24:MI:SS');

Execution Plan
----------------------------------------------------------
Plan hash value: 589593414

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    22 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|      |     1 |    22 |     3   (0)| 00:00:01 |     1 |     5 |
|*  2 |   TABLE ACCESS FULL | T1   |     1 |    22 |     3   (0)| 00:00:01 |     1 |     5 |
--------------------------------------------------------------------------------------------

类型不一致，导致走全表扫描，但是这种语句的结果是正确的，但是没有达到性能最好.

2,隐式的数据类型转换,不像显示类型转换那样通过函数一目了然。比较难以发现,比如
隐函的数据类型,日期格式不一样的转换

SQL> select *
  2   from t1
  3   where a='2001-01-01 00:00:01';

Execution Plan
----------------------------------------------------------
Plan hash value: 1258445941

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    22 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |    22 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | T1   |     1 |    22 |     3   (0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------

SQL> select *
  2   from t1
  3   where a='01-JAN-01 00:00:01';

Execution Plan
----------------------------------------------------------
Plan hash value: 1258445941

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    22 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |    22 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | T1   |     1 |    22 |     3   (0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------

从pstart和pstop来看，oracle并没有很好的使用静态分区剪裁,而是使用了动态分区剪裁,动态的效率没有静态
的效率高.