海量数据库及分区3——《12年资深DBA教你Oracle开发与优化——性能优化部分》

本文链接：https://blog.csdn.net/jdnha/article/details/8271699

分区的性能

分区剪切

分区剪切功能能极大的提高SQL性能，原因是Oracle优化器分析SQL的FROM语句和WHERE子句时，在构建分区访问列表时会首先排除掉不需要的分区，因而确保SQL只在相关的分区上操作，这就必然会带来数据范围的缩减，降低了I/O，中众所周知I/O是性能的瓶颈。

1.环境准备

创建一个与表ACCOUNT_TRADE_CLL结构完全想相同的表ACCOUNT_TRADE_CLL_NP，但该表不分区，然后写入数据，且确保二者均没有建立索引。

详见附件脚本1

2.PL/SQL Developer执行计划设置

3.对比两个表的执行计划

此时发生了分区剪切

此时未发生了分区剪切

结论：采用了分区的表的耗费为没采用的：41/240=17.08%

时间为:1/3=33.33%,I/O耗费为：41/238=17.23%，CPU成本为：

3748539/34875284=10.74%

分区智能连接

当连接并行执行时，分区智能连接通过最小化并行执行服务器之间的数据交换从而能显著降低查询的响应时间。分区智能连接引人关注的特征就是 减少响应时间、 提高CPU和内存资源的使用效率。

在RAC(实时应用集群)环境中，分区智能连接同样能最大程度的减少数据互连（相互连接）之间的数据通讯，甚至能避免这种互连的数据通讯。

分区智能连接分为全分区智能连接和部分分区智能连接，使用那种连接，由Oracle数据库根据不同的情形决定。

1.全分区智能连接

此时，将来自于相连的两个表的一对分区之间的很大的连接分解成小连接。只有当基于连接键的两个表是均衡的，或者参照分区时才可使用本特征。如：

假定表sales和customers通过列customer_id相连，该连接是一个大连接，查询某个1999年第三季度购买次数超过100的客户记录，则会使用全分区智能连接，如下：

SELECT c.customer_name, COUNT(*)

FROM sales s, customers c

WHERE s.customer_id = c.customer_id AND

s.time_id BETWEEN TO_DATE('1999-07-01', 'YYYY-MM-DD') AND

(TO_DATE('1999-10-01', 'YYYY-MM-DD'))

GROUP BY c.customer_name HAVING COUNT(*) > 100;

上述SQL执行时整个customers表与salse表的第三季度数据相连，在一个大数据仓库中，这意味着数以百万计的数据行被连接，此时是一种明显的散列连接，如果此时两个表中的数据是按customer_id均衡分布的，则越发能缩短该散列连接的处理时间，原因是Oracle可以使用全分区智能连接。

在并行操作中要使用全分区智能连接时，最小的并行粒度也是分区，因此并行度也被限制为分区的数量。如，请求至少16个分区，则会导致查询的并行度也被设置为16。

下面是三种均衡化两个连接表的分区方法：

如右图，通常，在一个大事物中，由于customer表是字典表，因而数据量少，为了降低I/O和CPU时间，该表会被选择为驱动表。但在并行查询中，由于可以 使用并行查询且一般 最终只有几行会被选中以及 对小表的限制条件(Where条件)少，所以大表一般被选择为驱动表。如果此时连接的两个表的列均建立了 主键索引或唯一索引，则对于并行查询，效率会更高。

（1）.单级分区-单级分区的全分区智能连接

适合于R-R、H-H、L-L的单级分区连接的两个表，且分区键是建立在连接的两个列上。

使用执行计划查看并行查询的选项包括：

A.P->P，即PARALLEL_TO_PARALLEL，输出步骤重新分配到并行服务器的下一个集合，此时性能最好。

B.P->S，即PARALLEL_FROM_SERIAL，输出步骤返回至序列化的查询协调器(QC)进程。

C.PCWP（PWP）， PARALLEL_COMBINED_WITH_PARENT，输出步骤在同一个并行进程内转向下一步，此时没有与父进程间通讯

D.PCWC（PWC）， PARALLEL_COMBINED_WITH_CHILD，输入步骤来自在同一个并行进程的前一步，此时也没有与父进程间通讯

例子如下：创建两个表account_trade_shq_fpw和account_trade_shq_fpw ，写入数据然后设定并行访问，再通过执行计划查看：

针对上述执行计划的主要解释如下：

A. 第14行PX BLOCK ITERATOR，表明将第15行的表ACCOUNT_TRADE_SHQ_FPW分割成小块，以便在并发从属之间分摊扫描的负载

B. 第11行的PX SEND BROADCAST和第10行的PX RECEIVE对，第6行的PX SEND HASH和第5行的PX RECEIVE对，描述了两个并发从属集之间的管道，该管道中流动的是从并发扫描中获得的行

C. 第2行PX SEND QC (RANDOM)描述的是按照随机顺序发给查询协调器汇总聚合的值

D. 第1行PX COORDINATOR描述的是查询协调器，其负责控制和调度下面树图中展示的并行执行计划

全分区智能连接又包括三种类型：

（1）.单级分区-单级分区的全分区智能连接

适合H-H,L-L和R-R的表连接，且分区键是相连接的列。

（2）.组合分区-单级分区的全分区智能连接

此时一般是大表基于两个维度分区，连接的列作为子分区键

（3）.组合分区-组合分区的全分区智能连接

如果需要的话，连接的两个表均建立组合分区，因而也可以建立分区-分区，分区-子分区，子分区－分区，子分区-子分区的连接。但是这种情况很少见，一般不建议使用，可以采用前面两种两法来替代。

2.部分分区智能连接

区别于全分区智能连接，部分分区智能连接仅仅是两个相连的表中的一个表在连接键上分区，而不是两个表均在连接键上分区。分区表作为参照表，另外一个表可以分区，也可以不分区。部分分区智能连接比全分区智能连接更常用。

Oracle在执行过程中会依据分区的参照表动态对另外一个表进行分区，然后其执行类似于全智能分区。当大表的分区是建立在连接键、外键或主键上时，可以防止任何时候基于该连接的重新分配，因此使用外键是部分智能分区中常见的情形。

右边的例子中customer没分区，sales分为16个散列分区。执行前面全分区智能连接的SQL语句，则表Sales的分区

与customers表的连接中，customers表自动重新分配以便与sales表不同的分区连接。

分区及表压缩技术

分区级压缩支持对单个或几个分区进行压缩， 表级压缩则是对所有分区进行压缩，其存储参数同样也遵循表空间->表->分区的原则，细粒度的属性定义会覆盖从上一级别继承的属性（其他存储参数也是如此，如逻辑存储设置参数）。一般来说，如果数据更新很少，对表的操作主要是只读的且数据量很大一般建议(2000万行或2G大小)压缩。

可以更改任何未压缩的表分区，然后再加入新的压缩或未压缩的分区，也可以改变任何分区管理操作的压缩属性，如分区合并、分割、移动等（MERGE PARTITION、 SPLIT PARTITION、 MOVE PARTITION ）。对部分或全部压缩的分区表的访问和维护操作与未压缩的分区表是一样的。

1.建表时压缩语法

2.修改表时压缩语法

表压缩语句

2.修改表时压缩语法

分区压缩

3.全表压缩示例

此时全部的分区均会被压缩。 参见附件脚本3

3.全表压缩示例

对比存储大小：

压缩后的大小为原来的：2368/3136=75.51%

列重复数据越多，压缩比越高，通常情况下都能到2-4倍!

4.部分分区压缩示例

此时指定要压缩的分区才会被压缩，其他分区不会被压缩：

4.部分分区压缩示例

维护操作	范围分区	散列分区	列表分区	组合分区
增加分区	ADD PARTITION	ADD PARTITION	ADD PARTITION	ADD PARTITION MODIFY PARTITION … ADD SUBPARTITION
接合分区	N/A	COALESCE PARTITION	N/A	MODIFY PARTITION … COALESCE PARTITION
删除分区	DROP PARTITION	N/A	DROP PARTITION	DROP PARTITION
交换分区	EXCHANGE PARTITION	EXCHANGE PARTITION	EXCHANGE PARTITION	EXCHANGE PARTITION EXCHANGE SUBPARTITION
合并分区	MERGE PARTITIONS	N/A	MERGE PARTITIONS	MERGE PARTITIONS
修改分区增加值	N/A	N/A	MODIFY PARTITION ADD VALUES	N/A
修改分区删除值	N/A	N/A	MODIFY PARTITION DROP VALUES	N/A
移动分区	MOVE PARTITION	MOVE PARTITION	MOVE PARTITION	MOVE PARTITION
更名分区	RENAME PARTITION	RENAME PARTITION	RENAME PARTITION	RENAME PARTITION RENAME SUBPARTITION
分割分区	SPLIT PARTITION	N/A	SPLIT PARTITION	SPLIT PARTITION
清空分区	TRUNCATE PARTITION	TRUNCATE PARTITION	TRUNCATE PARTITION	TRUNCATE PARTITION TRUNCATE SUBPARTITION

管理分区

增加表分区

1.按范围分区

使用ALTER TABLE … ADD PARTITION增加一个范围分区在已经存在的最大的分区的后面。如果要增加到头部或中间，使用SPLIT PARTITION语句。增加完成后，本地索引和全局索引依然保持为可用。

2.散列分区

增加散列分区Oracle会自动重新分配数据到新增的分区，有两种方法，第一种是不指定分区的名字，第二种是指定分区的名字。也可以指定表空间。增加完成后，一般来说索引变得不可用，需要重新构建。

3.列表分区

使用ADD PARTITION partition_name values(value1,…,valuen)增加列表分区。但是在任何其他列表分区中已经存在的值不能存在于其它分区中。此外，如果存在默认列表分区(default)则不可以增加分区，但是可以通过分割默认分区来实现。此时，增加完成后，本地索引和全局索引依然保持可用。

(1).表中已经存在default分区，因此不能分区