oracle左裁剪原理,深入原理：分区剪裁特性剖析

小鱼(邓秋爽)

云和恩墨专家，有超过5年超大型数据库专业服务经验，擅长oracle 数据库优化,SQL优化和troubleshooting

oracle增加分区sql，编辑手记：深入学习分区表的特性，更好地设计分区表的表结构

做表结构设计时我们经常会将大表做分区或者分表规划，oracle数据库中由于非常强大的分区功能可以不用分表的办法而直接使用分区表来规划，而我们使用分区表一个很重要的特性就是分区裁剪，这里将对分区表的分区裁剪简单的分析和探究：

分区剪裁就是对于分区表或者分区索引来说，优化器可以自动从from和where中根据分区键直接提取出需要访问的分区，从而避免扫描所有的分区，降低了IO请求。分区剪裁可以细分为静态分区剪裁和动态分区剪裁，其中静态分区剪裁发生在sql语句编译阶段，而动态分区剪裁则发生在sql语句执行阶段，对于分区键是常量值优化器在会走静态分区剪裁的，如果分区键是变量形式优化器只会走动态分区剪裁。

oracle分区表本地索引和全局索引。静态分区剪裁：

执行计划

统计信息：Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size       1665  bytes sent via SQL*Net to client        509  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          0  rows processed

oracle新增分区。静态分区剪裁中pstart和pstop都是具体的分区编号值，比如这里分区剪裁就是走编号31的分区

动态分区剪裁：

我们来看执行计划

动态分区剪裁执行计划中pstart、pstop都是key的形式，这里扫描的分区无法确定，因为有变量的因素导致无法确定recdate分区键的范围，所以这里的pstart和pstop都是key形式

上面动态分区剪裁中：表CS_CUST_ORDER是以region+recdate的range分区，sql语句中由于只有region条件是静态值，而recdate是绑定变量的形式，优化器这里走的分区剪裁分为两部分：通过region条件的谓词走静态分区剪裁；通过recdate条件的谓词走动态分区剪裁。

静态分区剪裁和动态分区剪裁在IO性能上是否有差异：

执行计划

统计信息如下：Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size       1665  bytes sent via SQL*Net to client        509  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          0  rows processed

这里看出来静态和动态分区剪裁在IO性能上并没有差异。

如果分区键被用作表达式或者函数运算，将导致优化器无法走分区剪裁，这个跟索引列被用作表达式或者函数运算一样：SQL> create table t_local01 partition by range(object_id)  2      (partition p1 values less than(10000),  3      partition p2 values less than(20000),  4      partition p3 values less than(30000),  5      partition p4 values less than(40000),  6      partition p5 values less than(maxvalue))  7      as select * from dba_objects;Table created.SQL> create index ind_dataobjid

on t_local01(data_object_id) local;Index created.SQL> select * from t_local01

where object_id<10000 and data_object_id=110;

我们来看执行计划：

(1)

(2)

(3)

(4)

------The end如何加入"云和恩墨大讲堂"微信群