Oracle分布式查询处理与优化方案，了解基于代价的优化器CBO如何对查询计划进行优化

---

前言

以分布式数据库管理系统Oracle为例，通过查询计划的执行了解Oracle产品的优化程序基于代价的优化器(Cost-Based Optimizer, CBO)，明确Oracle CBO查询优化器架构及其使用方法。

---

0 软件版本

OS：一台Windows10的电脑和在其上搭建一台CentOS7的虚拟机
数据库：Oracle19c
图形化工具：Navicat for Oracle
（建议直接从官网下载，也可以从我之前的博客中获取，里面讲述如何安装和使用这些软件）

---

1 在集中式环境下，了解CBO优化器的优化策略执行过程

在集中式环境下，构建相应的示例表，通过发起对不同场景下的查询，实际了解CBO优化器的优化策略执行过程。

1.1 建立示例表

在集中式环境下建立示例表较为简单，可以随意创建，没有过多要求，在这里就不再过多堆砌SQL语句，最终建好的示例表如下：

1.2 在未建立索引（除数据库自动为主键建立过索引）的情况下，查询全表数据

SELECT
	*
FROM
	"students";

点击解释，查看查询的执行计划：

可以看出此查询是采用全表扫描方式进行查找的，且执行代价cost为3。

1.3 对s_name建立索引，再查询全表数据

查询SQL：

SELECT
	*
FROM
	"students";

点击解释，查看查询的执行计划：

此时可以看出此查询是采用索引扫描方式来进行查找的，扫描的对象变为了s_name且执行代价cost为1。

---

2 在分布式环境下，实际理解CBO如何确定局部执行代价

在分布式环境下，构建相应的示例表，通过发起分布式查询，实际理解CBO如何确定局部执行代价，并利用相关优化技术的理论方法得到最优的执行计划。

注：以上的数据来源是通过调用高德地图API爬取下来，并是经过清洗过的前三万条数据。
如果需要源数据可以在C站私信我！

2.1 在本地Oracle数据库建立品牌维度总部表

其中存储店铺id、店名、类别这3个字段
以下是对表的设计：

之后将从本地excel表导入数据到Oracle中。

2.2 在远地Oracle数据库建立品牌维度分部表

其中存储店铺id、店名名称、省、市、区/县、地址、经度、纬度这八个字段。

此时需要先打开虚拟机，启动Oracle数据库及监听服务：

lsnrctl status
sqlplus / as sysdba
SQL> startup

使用Navicat连接上数据库并建立表：

2.3 将数据excel表导入本地Oracle数据库

导入步骤：
（1）右键选中的数据表，选择导入向导

（2）选择导入文件类型

（3）选择本地需要导入的excel作为数据源
注意：需要将导入的excel表打开，不然会显示“无法打开文件”

（4）选择导入的数据，一般默认

（5）选择导入的目的表，注意将新建表的勾去掉

（6）选择字段名

（7）后续按默认即可，点击开始导入，等待一分钟即可导入成功

点开表，查看是否导入成功（如有没有出现空值及乱码等情况）

查询导入了多少条数据：

SELECT
	COUNT (*)
FROM
	"品牌维度总部表";

2.4 将excel表导入远程Oracle数据库

导入步骤与前者大致相同，在此不再赘述，仅展示导入完成的结果及数据。
查看具体数据：

查询数据条数：（可以看到数据条数与总部的相同）

2.5 进行分布式查询

查询的字段要求分别在两分表中，查看运行时间及执行计划。

查询店铺的id、店铺名称、地址 这三个字段：

SELECT
	T1."id",
	T1."店铺名称",
	T2."地址"
FROM
	"品牌维度总部表" t1,
	"品牌维度分部表" @link1 t2
WHERE
	T1."店铺名称" = T2."店铺名称";

查看运行时间及执行计划：

分析：CBO自动判断，将执行计划查询分解成本地和远程两个分查询。前两字段只需在本地oracle中获取，最后一个字段在远地oracle中获取，然后通过哈希连接两个结果集，形成我们最后所需的数据行。

优点：将全局数据查询转换为针对每个局部数据库的查询，降低连接网络操作网络中数据的传输量，提高了执行效率，减小了查询时间。

2.6 在分布式环境下，了解CBO是如何利用分区视图减少查询代价

查看分区视图：（采用的是上个相关博客中的数据表）

前三行数据位于本地Oracle数据库中，后三行数据位于远程Oracle数据库中。

注意：需要设置视图参数partition_view_enabled=true
partition_view_enabled 优化程序，如果将 PARTITION_VIEW_ENABLED 设置为 TRUE，该优化程序将剪除 (或跳过) 分区视图中不必要的表访问。该参数还能更改基于成本的优化程序从基础表统计信息计算分区视图统计信息的方式。

接下来，在本地数据库用户下，查询部门为“CentOS”（位于远程数据库中）的全部员工eno、ename、salary字段信息：

SELECT
	*
FROM
	EMP
WHERE
	DEPTNO = 'CentOS';

查看执行计划：

CBO对查询场地个数进行了约简，由于需要在数据全部位于远程数据库中，故可以将其余场地的数据过滤掉，只在远程数据库中执行查询。

---

总结：分别在集中式环境和分布式环境下，构建了相应的示例表，通过发起查询，实际理解了CBO如何确定局部执行代价，并利用相关优化技术的理论方法得到最优的执行计划。

---

后续会继续更新有关Oracle分布式数据库的内容!
（注：第18次发文，如有错误和疑问，欢迎在评论区指出!）
——2021.10.26