select cross multi-partiton(no-binds)

Jonathen lewis在Cost Based Oracle fundermental一书的P.36上写到谓词条件(非绑定变量)跨越多个分区的情况,oracle会采用table-level的统计信息来进行计算。
原始sql语句

explain plan for
select  count(*) 
from    t1
where   part_col between 150 and 250
;

select * from table(dbms_xplan.display);

测试一下在11.2.0.4和12.1.0.2版本上的输出情况
Output(11.2.0.4 and 12.1.0.2)

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |     4 |   461   (1)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |     4 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |   102K|   398K|   461   (1)| 00:00:06 |     1 |     2 |
|*  3 |    TABLE ACCESS FULL      | T1   |   102K|   398K|   461   (1)| 00:00:06 |     1 |     2 |
--------------------------------------------------------------------------------------------------

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

   3 - filter("PART_COL"<=250 AND "PART_COL">=150)

测试结果与CBO书上说的结果还是一样的,Oracle依然是按照table-level statistics来计算。

Jonathan在CBO book Partitioning_Precision中提到,针对这种no-binding的跨分区的SQL语句,可以改写成如下sql来使优化器在parsing的时候知道partition boundaries。

1st method

alter system flush shared_pool;
explain plan for select  count(*)
from    t1
where  (part_col >= 150 and part_col <  200) 
   or  (part_col >= 200 and part_col <= 250);

select * from table(dbms_xplan.display);

plan output(11.2.0.4 and 12.1.0.2)
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     4 |   461   (1)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     4 |            |          |       |       |
|   2 |   PARTITION RANGE OR|      |   100K|   392K|   461   (1)| 00:00:06 |KEY(OR)|KEY(OR)|
|*  3 |    TABLE ACCESS FULL| T1   |   100K|   392K|   461   (1)| 00:00:06 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------

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

   3 - filter("PART_COL"=150 OR "PART_COL"<=250 AND  "PART_COL">=200)

2nd method

alter system flush shared_pool;
explain plan for select count(*)
    from (
        select * from t1 where (part_col >= 150 and part_col <  200) 
     union all 
        select * from t1 where (part_col >= 200 and part_col <= 250));
select * from table(dbms_xplan.display);

plan output(11.2.0.4 and 12.1.0.2)
--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |       |   462   (1)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |       |            |          |       |       |
|   2 |   VIEW                    |      | 40800 |       |   462   (1)| 00:00:06 |       |       |
|   3 |    UNION-ALL              |      |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|      | 10049 | 40196 |   116   (1)| 00:00:02 |     1 |     1 |
|*  5 |      TABLE ACCESS FULL    | T1   | 10049 | 40196 |   116   (1)| 00:00:02 |     1 |     1 |
|   6 |     PARTITION RANGE SINGLE|      | 30751 |   120K|   346   (1)| 00:00:05 |     2 |     2 |
|*  7 |      TABLE ACCESS FULL    | T1   | 30751 |   120K|   346   (1)| 00:00:05 |     2 |     2 |
--------------------------------------------------------------------------------------------------

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

   5 - filter("PART_COL">=150)
   7 - filter("PART_COL"<=250)

可以看到输出中ID=2的部分40800已经是ID=4和ID=6的部分的和,不再像Partitioning_Precision文中在10.2.0.1中在view的过程中出现明显的错误。

此条目发表在CBO分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s