标签归档:maxvalue

Range分区多个分区键时maxvalue的使用

假设range分区使用多个分区键, 比如这个示例中的(month_id, status_id). 如果常用查询条件为month_id=201407, 我们会期望分区裁剪之后, 只有201407开头分区会被扫描. 实际情况如何, 我们做个简单的实验:

创建两个结构相同的表t1和t2, 只是每个月最后的一个分区第二个分区键的high value不同, t1为’V40′, t2为maxvalue.

sid@ORA12C> create table t1
  2  (
  3      id             number,
  4      month_id       number,
  5      status_id      varchar2(5)
  6  )
  7  partition by range(month_id, status_id)
  8  (
  9      partition p_201407_V10 values less than (201407,'V10'),
 10      partition p_201407_V20 values less than (201407,'V20'),
 11      partition p_201407_V30 values less than (201407,'V30'),
 12      partition p_201407_V40 values less than (201407,'V40'),
 13      partition p_201408_V10 values less than (201408,'V10'),
 14      partition p_201408_V20 values less than (201408,'V20'),
 15      partition p_201408_V30 values less than (201408,'V30'),
 16      partition p_201408_V40 values less than (201408,'V40')
 17  );

Table created.

sid@ORA12C> create table t2
  2  (
  3      id             number,
  4      month_id       number,
  5      status_id      varchar2(5)
  6  )
  7  partition by range(month_id, status_id)
  8  (
  9      partition p_201407_V10 values less than (201407,'V10'),
 10      partition p_201407_V20 values less than (201407,'V20'),
 11      partition p_201407_V30 values less than (201407,'V30'),
 12      partition p_201407_V40 values less than (201407,maxvalue),
 13      partition p_201408_V10 values less than (201408,'V10'),
 14      partition p_201408_V20 values less than (201408,'V20'),
 15      partition p_201408_V30 values less than (201408,'V30'),
 16      partition p_201408_V40 values less than (201408,maxvalue)
 17  );

Table created.

使用条件month_id=201407扫描数据, 观察执行计划中的pstart, pstop.
对于表t1, 因为201407的最后一个分区p_201407_V40的high value为(201407,’V40′), 那可能出现(201407,’ZZZ’)的数据大于(201407,’V40′), 需要放到201408的第一个分区p_201408_V10. 所以使用条件month_id=201407时, 必须扫描p_201408_V10这个分区, 此时执行计划中pstat=1, pstop=5.
对于表t2, 因为201407的最后一个分区p_201407_V40的high value为(201407,maxvalue), 201407所有大于等于(201407,’V30′)的数据, 都会放在7月份的最后一个分区. 使用条件month_id时, 分区裁剪之后, 只需要扫描201407开头的分区. 此时执行计划中pstart=1, pstop=4. 避免对201408第一个分区不必要的扫描.

sid@ORA12C> explain plan for
  2  select * from t1 where month_id = 201407;

Explained.

sid@ORA12C> select * from table(dbms_xplan.display('plan_table',null,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 277861402

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

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

   2 - storage("month_id"=201407)
       filter("month_id"=201407)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

sid@ORA12C> explain plan for
  2  select * from t2 where month_id = 201407;

Explained.

sid@ORA12C> select * from table(dbms_xplan.display('plan_table',null,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 462758725

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    30 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR  |      |     1 |    30 |     2   (0)| 00:00:01 |     1 |     4 |
|*  2 |   TABLE ACCESS STORAGE FULL| T2   |     1 |    30 |     2   (0)| 00:00:01 |     1 |     4 |
---------------------------------------------------------------------------------------------------

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

   2 - storage("month_id"=201407)
       filter("month_id"=201407)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.