oracle 数据库分区访问权限,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个分区就可以了，但是从执行中我们还是很容易看出访问了所有的

分区.