ORACLE分区访问常用方式

                                     ORACLE分区访问常用方式

      对于常规的表(HEAP)访问方法主要是TABLE ACCESS FULL(全表扫描)和ROWID的方式(不考虑Exadata).第一个方法扫描
整个表的所有数据块，然后再过滤或是下一步处理;第二种方法主要是通过索引访问得到rowid,再通过rowid的方式得到相应
的行.对于分区表由于数据被分隔在各个分区里面，带来的分区访问方式有稍有不同。

假设有表

dongdongtang>create table t1 (a integer,b varchar2(100))
  2  partition by range(a)
  3  (partition t1_p1 values less than (5),
  4   partition t1_p2 values less than (10),
  5   partition t1_p3 values less than (15),
  6   partition t1_p4 values less than (20));

Table created.

dongdongtang>select * from t1;

         A B
---------- ----------
         2 a
         7 b
        12 c
        17 c

insert 4个值，保证每个分区都不为空

1，先看全表扫描的方式

dongdongtang>set autotrace on;
dongdongtang>select count(a) from t1;

  COUNT(A)
----------
         4
Execution Plan
----------------------------------------------------------
Plan hash value: 2705263620

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    13 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE      |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|      |     4 |    52 |     3   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS FULL | T1   |     4 |    52 |     3   (0)| 00:00:01 |     1 |     4 |
---------------------------------------------------------------------------------------------

从id 2中可以看到,分区访问的方式是PARTITION RANGE ALL,pstart到pstop是1-4,表示是访问该表的所有的
分区.类似于非分区表的全表扫描.

2，分区最重要特性之一就是CBO可以根据where条件中的partition key来访问相应的分区，对于
不需要的分区不会被访问，这种特性称为partition purging,

dongdongtang>select * from t1 where a=2;

         A B
---------- ------------------------------------------------
         2 a

Execution Plan
----------------------------------------------------------
Plan hash value: 1737577267

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

id 1中表示oracle通过PARTITION RANGE SINGLE,只访了一个分区，这种情况下pstart和pstop也是相等的.
pstart和pstop分别对应的起始分区和结束分区.

3,多个连续分区的访问,有时候查询可能需要从两个或两个以上的连续分区中来读取数据，比如:

dongdongtang>select * from t1 where a<13;

         A B
---------- ---------------------------------------------
         2 a
         7 b
        12 c
Execution Plan
----------------------------------------------------------
Plan hash value: 277861402

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     3 |   195 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|      |     3 |   195 |     3   (0)| 00:00:01 |     1 |     3 |
|*  2 |   TABLE ACCESS FULL      | T1   |     3 |   195 |     3   (0)| 00:00:01 |     1 |     3 |
-------------------------------------------------------------------------------------------------
该查询使用的访问方法为PARTITION RANGE ITERATOR,oracle通过在连续的分区中进行迭代来获取数据。pstart
和pstop分别对应了1-3个分区.

4,满足where条件的partition key的数据分布在不连续中的分区中.比如，通过第1，2，4个分区来读数据

dongdongtang>select * from t1 where a<8 or a>13;

         A B
---------- -----------------------------------------------------
         2 a
         7 b
        17 c
Execution Plan
----------------------------------------------------------
Plan hash value: 2818491992

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   195 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE OR|      |     3 |   195 |     3   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
|*  2 |   TABLE ACCESS FULL| T1   |     3 |   195 |     3   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
-------------------------------------------------------------------------------------------
PARTITION RANGE OR表明where条件是or的,该查询使用了不连续的分区访问法，pstart和pstop没有对应的
分区位置信息.

5,in操作的访问方法

dongdongtang>select * from t1 where a  in (2,7,12);

         A B
---------- --------------------------------------------
         2 a
         7 b
        12 c
Execution Plan
----------------------------------------------------------
Plan hash value: 594219520

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     3 |   195 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE INLIST|      |     3 |   195 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 |   TABLE ACCESS FULL    | T1   |     3 |   195 |     3   (0)| 00:00:01 |KEY(I) |KEY(I) |
-----------------------------------------------------------------------------------------------
该执行计划也是需要访问1,2,4个分区，但是执行计划中分区的访问不一样,PARTITION RANGE INLIST也无法通
过pstart和pstop一眼看出分区的起始和结束位置。但是低层也是通过or来转换的.

6,对于where条件中的partition key不存在的情况，这时候CBO直接判定条件无效或是最后一个分区的连界值
来决定结果.

dongdongtang>select * from t1 where a<6 and a>16;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1006818538

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    65 |     0   (0)|          |       |       |
|*  1 |  FILTER                |      |       |       |            |          |       |       |
|   2 |   PARTITION RANGE EMPTY|      |     1 |    65 |     2   (0)| 00:00:01 |INVALID|INVALID|
|*  3 |    TABLE ACCESS FULL   | T1   |     1 |    65 |     2   (0)| 00:00:01 |INVALID|INVALID|
-----------------------------------------------------------------------------------------------
pstart和pstop都是invalid表示分区无效，PARTITION RANGE EMPTY不需要访问分区.对于非分区表也采用类似的
方法的话需要走索或是全表扫描，而在分区表中，oracle可以直接通过partition的特性可以判断出不存在该条件
，可以大大增加类似查询的速度.

7，对于not in和!=(<>)这种运算很小心，这种运算操作无法使用partition purging
select * from t1 where  a not in (2,7)
select * from t1 where  a!=7

dongdongtang>select * from t1 where  a not in (2,7);

         A B
---------- ----------------------------------------------
        12 c
        17 c
Execution Plan
----------------------------------------------------------
Plan hash value: 589593414

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

人工可能很容易看出该语句只要访问3，4个分区就可以了，但是从执行中我们还是很容易看出访问了所有的
分区.