DM8执行计划的详细介绍

一、基本概念
执行计划是一条SQL语句在DM数据库中执行过程或访问路径的描述。

执行计划的查看方式
1、通过EXPLAIN命令查看
EXPLAIN SELECT * FROM SYSOBJECTS;

1 #NSET2: [0, 1711, 396]
2 #PRJT2: [0, 1711, 396]; exp_num(17), is_atom(FALSE)
3 #CSCN2: [0, 1711, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

2、通过管理工具查看

执行计划包含的重要信息
一个执行计划由若干个计划节点组成，如上图中的1、2、3
每个计划节点中包含操作符(CSCN2)和它的代价([0, 1711, 396])等信息
代价由一个三元组组成[代价,记录行数,字节数]
代价的单位是毫秒，记录行数表示该计划节点输出的行数，字节数表示该计划节点输出的字节数
解读一下第三个计划节点：操作符是CSCN2即全表扫描，代价估算是0ms，扫描的记录行数是1711行，输出字节数是396个。

二、执行计划中常用操作符解读
1、准备测试表和数据
DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );
CREATE TABLE T2(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );
INSERT INTO T1
SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,‘TEST’,NULL FROM DUAL
CONNECT BY LEVEL<=10000;
INSERT INTO T2
SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,‘TEST’,NULL FROM DUAL
CONNECT BY LEVEL<=10000;
CREATE INDEX IDX_C1_T1 ON T1(C1);
SP_INDEX_STAT_INIT(USER,‘IDX_C1_T1’);

2、收集结果集：NSET
EXPLAIN SELECT * FROM T1;
1 #NSET2: [1, 10000, 156]
2 #PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)
3 #CSCN2: [1, 10000, 156]; INDEX33556710(T1)
用于结果集收集的操作符，一般是查询计划的顶层节点

3、投影：PRJT
EXPLAIN SELECT * FROM T1;
1 #NSET2: [1, 10000, 156]
2 #PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)
3 #CSCN2: [1, 10000, 156]; INDEX33556710(T1)
关系的“投影”(project)运算，用于选择表达式项的计算；广泛用于查询，排序，函数索引创建等

4、选择：SLCT
EXPLAIN SELECT * FROM T1 WHERE C2=‘TEST’;
1 #NSET2: [1, 250, 156]
2 #PRJT2: [1, 250, 156]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1, 250, 156]; T1.C2 = TEST
4 #CSCN2: [1, 10000, 156]; INDEX33556717(T1)
关系的“选择” 运算，用于查询条件的过滤。

5、简单聚集：AAGR
EXPLAIN SELECT COUNT(*) FROM T1 WHERE C1 = 10;
1 #NSET2: [0, 1, 4]
2 #PRJT2: [0, 1, 4]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [0, 1, 4]; grp_num(0), sfun_num(1)
4 #SSEK2: [0, 1, 4]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]
用于没有group by的count sum age max min等聚集函数的计算

6、快速聚集：FAGR
EXPLAIN SELECT COUNT(*) FROM T1;
1 #NSET2: [1, 1, 0]
2 #PRJT2: [1, 1, 0]; exp_num(1), is_atom(FALSE)
3 #FAGR2: [1, 1, 0]; sfun_num(1),

EXPLAIN SELECT MAX(C1) FROM T1;
1 #NSET2: [1, 1, 0]
2 #PRJT2: [1, 1, 0]; exp_num(1), is_atom(FALSE)
#FAGR2: [1, 1, 0]; sfun_num(1),
用于没有过滤条件时从表或索引快速获取MAX/MIN/COUNT值；DM数据库是世界上单表不带过滤条件下取COUNT值最快的数据库

7、HASH分组聚集：HAGR
EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C2;
1 #NSET2: [1, 100, 48]
2 #PRJT2: [1, 100, 48]; exp_num(1), is_atom(FALSE)
3 #HAGR2: [1, 100, 48]; grp_num(1), sfun_num(1)
4 #CSCN2: [1, 10000, 48]; INDEX33556717(T1)
用于分组列没有索引只能走全表扫描的分组聚集，C2列没有创建索引

8、流分组聚集：SAGR
EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C1;
1 #NSET2: [1, 100, 4]
2 #PRJT2: [1, 100, 4]; exp_num(1), is_atom(FALSE)
3 #SAGR2: [1, 100, 4]; grp_num(1), sfun_num(1)
4 #SSCN: [1, 10000, 4]; IDX_C1_T1(T1)
用于分组列是有序的情况下，可以使用流分组聚集，C1上已经创建了索引，SAGR2性能优于HAGR2

9、二次扫描：BLKUP
EXPLAIN SELECT * FROM T1 WHERE C1=10;
1 #NSET2: [0, 1, 156]
2 #PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)
3 #BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)
4 #SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]
先使用2级别索引定位，再根据表的主键、聚集索引、rowid等信息定位数据行。

10、全表扫描：CSCN
EXPLAIN SELECT * FROM T1;
1 #NSET2: [1, 10000, 156]
2 #PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)
3 #CSCN2: [1, 10000, 156]; INDEX33556710(T1)
CSCN2是CLUSTER INDEX SCAN的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。在一个高并发的系统中应尽量避免全表扫描

11、索引扫描：SSEK CSEK SSCN
EXPLAIN SELECT * FROM T1 WHERE C1=10;
1 #NSET2: [0, 1, 156]
2 #PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)
3 #BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)
#SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

CREATE CLUSTER INDEX IDX_C1_T2 ON T2(C1);
EXPLAIN SELECT * FROM T2 WHERE C1=10;
1 #NSET2: [0, 250, 156]
2 #PRJT2: [0, 250, 156]; exp_num(5), is_atom(FALSE)
#CSEK2: [0, 250, 156]; scan_type(ASC), IDX_C1_T2(T2), scan_range[10,10]

CREATE INDEX IDX_C1_C2_T1 ON T1(C1,C2);
EXPLAIN SELECT C1,C2 FROM T1;
1 #NSET2: [1, 10000, 60]
2 #PRJT2: [1, 10000, 60]; exp_num(3), is_atom(FALSE)
3 #SSCN: [1, 10000, 60]; IDX_C1_C2_T1(T1)
SSEK2是二级索引扫描即先扫描索引，再通过主键、聚集索引、ROWID等信息去扫描表 CSEK2是聚集索引扫描只需要扫描索引，不需要扫描表 SSCN是索引全扫描，不需要扫描表

三、嵌套循环连接
NEST LOOP原理：
两层嵌套循环结构，有驱动表和被驱动表之分。
选定一张表作为驱动表，遍历驱动表中的每一行，根据连接条件去匹配第二张表中的行。驱动表的行数就是循环的次数，这个很大程度影响了执行效率。

需注意：

• 选择小表作为驱动表。统计信息尽量准确，保证优化器选对驱动表。
• 大量的随机读。如果没有索引，随机读很致命，每次循环只能读一块，不能读多块。使用索引可以解决这个问题。

使用场景：

• 驱动表有很好的过滤条件
• 表连接条件能使用索引
• 结果集比较小

过滤列和连接列都没有索引，也可以走nest loop，但是该计划很差。下面的计划代价很大。
select /+use_nl(t1,t2)/*
from t1 inner join t2
on t1.c1=t2.c1
where t1.c2=‘A’;

优化：创建索引并搜集统计信息
create index idx_t1_c2 on t1(c2);
create index idx_t2_c1 on t2(c1);
dbms_stats.gather_index_stats(user,‘IDX_T1_C2’);
dbms_stats.gather_index_stats(user,‘IDX_T2_C1’);

四、哈希连接
HASH JOIN的特点：
一般没索引或用不上索引时会使用该连接方式
选择小的表（或row source）做hash表
只适用等值连接中的情形

原理：
使用较小的Row source 作为Hash table和Bitmap. 而第二个row source被hashed，根据bitmap与第一个row source生成的hash table 相匹配，bitmap查找的速度极快。

Hash连接比较消耗内存，如果系统有很多这种连接时，需调整以下3个参数：
HJ_BUF_GLOBAL_SIZE
HJ_BUF_SIZE
HJ_BLK_SIZE

连接列没有索引
select *
from t1 inner join t2
on t1.c1=t2.c1
where t1.c2=‘A’;

不是等值连接，此时计划走nest loop
select *
from t1 inner join t2
on t1.c1=t2.c1
where t1.c2=‘A’;

五、归并排序连接
MERGE SORT的特点：
无驱动表之分，随机读很少
两个表都需要按照连接列排序，需要消耗大量的cpu和额外的内存

应用场景：
通常情况下，merge sort join需要消耗大量的cpu和内存，效率都不会太高。如果存在相关索引可以消除sort，那么CBO可能会考虑该连接方式。

select /+use_merge(t1 t2)/
t1.c1,t2.c1
from t1 inner join t2 on t1.c1=t2.c1
where t2.c2=‘b’;

–创建如下索引，消除sort
create index idx_t1_c1c2 on t1(c1,c2);
create index idx_t2_c1 on t2(c1);

六、查询转换
查询转换是优化器自动做的，在生成执行计划之前，等价改写查询语句的形式，以便提升效率和产生更好的执行计划。它决定是否重写用户的查询，常见的转换有谓词传递、视图拆分、谓词推进、关联/非关联子查询改写等。

了解优化器查询转换的特性，会帮助我们更好的看懂执行计划，也会对我们优化sql起到指导的作用。优化器的查询转换有很多限制条件，我们可以根据类似的原理举一反三，进行手工的sql改写，从到得到更好的执行计划。

1、谓词传递
原理：根据A=B,B=C,可以推导出A=C
–原始sql
select * from t1 inner join t2
on t1.c2=t2.c2
where t1.c1=100
and t2.c1=t1.c1

–CBO转换后，等价于下面的sql
select * from t1 inner join t2
on t1.c2=t2.c2
where t1.c1=100
and t2.c1=t1.c1
and t2.c1=100 –-谓词传递

2、视图拆分
–视图定义
create or replace view v_t1 as
select t1.c1+t2.c1 as c11,
t2.c2,t1.c1
from t1,t2
where t1.c2=t2.c2;

视图定义sql的计划

–原始sql
select a.c11,b.c2
from v_t1 a,t1 b
where a.c1=b.c1
and a.c1=100;

观察原始sql的执行计划，发现视图部分的子计划已经没有了。说明优化器进行等价改写，将视图的查询拆散了，和其他部分作为一个整体来生成计划。视图拆分有很多限制，如果视图查询中含有distinct、union、group by等操作，优化器就无法进行视图拆分。

Sql中使用过多的视图，会使sql变得复杂，优化器也难以生成最佳的执行计划，不能过度依赖优化器进行视图拆分。开发时应尽量减少视图的使用。

3、非关联子查询的转换
–原始sql
select * from t1
where c1 in (select c1 from t2 )
and c2=‘A’;
观察原始sql，T2的子查询是个非关联的子查询，完全可以把它生成一个独立的子计划。但是计划中TI和T2做了关联，说明优化器进行了如下的等价改写：
–查询转换
select * from t1
where exists (select 1 from t2 where t1.c1=t2.c1)
and c2=‘A’;
相关INI参数: REFED_EXISTS_OPT_FLAG，影响in和exists子查询的转换。

4、外连接转换
–原始sql
select t1.c1,t2.c2
from t1 left join t2
on t1.c1=t2.c1
where t2.c1=100 and t1.c2=‘A’;

观察上面的计划发现，原始sql是外连接，计划中却变成了内连接。这是优化器根据sql语义判断，就是等价于下面的内连接：

–原始sql
select t1.c1,t2.c2
from t1 inner join t2 on t1.c1=t2.c1
where t2.c1=100 and t1.c2=‘A’;