玩转达梦HINT系列之ENABLE_INDEX_FILTER

DM技术交流QQ群：940124259

一、基础介绍

---

二、数据准备

CREATE TABLE "SYSDBA"."TT1"
(
"C1" INT,
"C2" VARCHAR(50)) STORAGE(ON "TT_TBS", CLUSTERBTR) ;

CREATE TABLE "SYSDBA"."TT2"
(
"D1" INT,
"D2" VARCHAR(50),
"D3" INT) STORAGE(ON "TT_TBS", CLUSTERBTR) ;

insert into tt1 select level, level||'a' from dual connect by level<=1000;
insert into tt2 select level, level||'b', mod(level, 1000) from dual connect by level<=100000;
commit;

---

三、执行计划分析全解

3.1 讲解1：普通过滤条件走索引过滤优化

-- 示例1. 对tt2表中d3列建索引
create index idx_on_d3_tt2 on tt2(d3);

select * from tt2 where d3 < 100;
/*
1   #NSET2: [10, 9900, 64] 
2     #PRJT2: [10, 9900, 64]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [10, 9900, 64]; IDX_ON_D3_TT2(TT2)
4         #SSEK2: [10, 9900, 64]; scan_type(ASC), IDX_ON_D3_TT2(TT2), scan_range(null2,100)
*/
结论：CBO优化器内部估算，最终选走SEEK索引范围扫描IDX_ON_D3_TT2索引，快速筛选数据，再回表查询表中其他字段。

-- 示例2. 禁用索引IDX_ON_D3_TT2
select /*+ NO_INDEX(tt2, IDX_ON_D3_TT2) */ * from tt2 where d3 < 100;
/*
1   #NSET2: [12, 9900, 64] 
2     #PRJT2: [12, 9900, 64]; exp_num(4), is_atom(FALSE) 
3       #SLCT2: [12, 9900, 64]; TT2.D3 < 100
4         #CSCN2: [12, 100000, 64]; INDEX33555614(TT2)
*/
结论：禁用D3列索引，过滤条件直接走全表扫描后，再普通过滤数据。

-- 示例3. 增加一个过滤条件d1 < 50
select * from tt2 where d3 < 100 and d1 < 50;
/*
1   #NSET2: [10, 4, 64] 
2     #PRJT2: [10, 4, 64]; exp_num(4), is_atom(FALSE) 
3       #SLCT2: [10, 4, 64]; TT2.D1 < 50
4         #BLKUP2: [10, 9900, 64]; IDX_ON_D3_TT2(TT2)
5           #SSEK2: [10, 9900, 64]; scan_type(ASC), IDX_ON_D3_TT2(TT2), scan_range(null2,100)
*/
结论：先扫描二级索引IDX_ON_D3_TT2，再回表其他表中其他字段数据，回表操作结束后继续硬过滤d1<50的数据记录。

-- 示例4. 启用索引过滤优化参数
select /*+ ENABLE_INDEX_FILTER(1)*/ * from tt2 where d3 < 100 and d1 < 50;
/*
1   #NSET2: [10, 4, 64] 
2     #PRJT2: [10, 4, 64]; exp_num(4), is_atom(FALSE) 
3       #SLCT2: [10, 4, 64]; TT2.D1 < 50
4         #BLKUP2: [10, 9900, 64]; IDX_ON_D3_TT2(TT2)
5           #SSEK2: [10, 9900, 64]; scan_type(ASC), IDX_ON_D3_TT2(TT2), scan_range(null2,100)
*/
结论：执行计划照常与示例3相同，说明该参数对单列索引起不到作用。

-- 示例5：创建一个组合索引，再启索引过滤参数
create index idx_on_d3d1_tt2 on tt2(d3,d1);

select /*+ ENABLE_INDEX_FILTER(1)*/ * from tt2 where d3 < 100 and d1 < 50;
/*
1   #NSET2: [1, 4, 64] 
2     #PRJT2: [1, 4, 64]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [1, 4, 64]; IDX_ON_D3D1_TT2(TT2)
4         #SLCT2: [1, 4, 64]; TT2.D1 < 50
5           #SSEK2: [1, 4, 64]; scan_type(ASC), IDX_ON_D3D1_TT2(TT2), scan_range[(null2,min),(100,min))
*/
结论：
此时可以看到BLKUP操作符上移，SLCT2操作符下移，证明该参数已经对组合索引生效。
先扫描IDX_ON_D3D1_TT2索引的前导列（d3<100>）过滤，再从剩下的索引页中过滤（d1<50）的数据，
最后才回表查询其他字段，返回最终的查询结果集。

-- 示例6：删除idx_on_d3d1_tt2复合索引，重建（d1,d3）排序组合的复合索引
drop index idx_on_d3d1_tt2;
create index idx_on_d1d3_tt2 on tt2(d1,d3);

select /*+ ENABLE_INDEX_FILTER(1)*/ * from tt2 where d3 < 100 and d1 < 50;
/*
1   #NSET2: [0, 4, 64] 
2     #PRJT2: [0, 4, 64]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [0, 4, 64]; IDX_ON_D1D3_TT2(TT2)
4         #SLCT2: [0, 4, 64]; TT2.D3 < 100
5           #SSEK2: [0, 4, 64]; scan_type(ASC), IDX_ON_D1D3_TT2(TT2), scan_range[(null2,min),(50,min))
*/
结论：
不管复合索引中的列位置如何定义，开启该优化参数都可以达到类似示例5的作用。

-- 示例7：删除复合索引，保留单列索引
drop index idx_on_d1d3_tt2;

select /*+ ENABLE_INDEX_FILTER(1)*/ * from tt2 where d3 < 100 and d1 < 50;
/*
1   #NSET2: [10, 4, 64] 
2     #PRJT2: [10, 4, 64]; exp_num(4), is_atom(FALSE) 
3       #SLCT2: [10, 4, 64]; TT2.D1 < 50
4         #BLKUP2: [10, 9900, 64]; IDX_ON_D3_TT2(TT2)
5           #SSEK2: [10, 9900, 64]; scan_type(ASC), IDX_ON_D3_TT2(TT2), scan_range(null2,100)
*/
结论：删除复合索引后，剩单列索引，又回到示例4的效果，更加证明该优化参数在复合索引前提下生效。

-- 示例8：两表连接查询带索引列的过滤条件
select * from tt2 t2 , tt1 t1 where t2.d1 = t1.c1 and d3 < 100 and d1 < 50;
/*
1   #NSET2: [11, 4, 108] 
2     #PRJT2: [11, 4, 108]; exp_num(5), is_atom(FALSE) 
3       #HASH2 INNER JOIN: [11, 4, 108];  KEY_NUM(1); KEY(T2.D1=T1.C1) KEY_NULL_EQU(0)
4         #SLCT2: [10, 4, 56]; T2.D1 < 50
5           #BLKUP2: [10, 9900, 56]; IDX_ON_D3_TT2(T2) -- 总记录100 000
6             #SSEK2: [10, 9900, 56]; scan_type(ASC), IDX_ON_D3_TT2(TT2 as T2), scan_range(null2,100)
7         #SLCT2: [0, 49, 52]; T1.C1 < 50
8           #CSCN2: [0, 1000, 52]; INDEX33555613(TT1 as T1)
*/
结论：
即使查询SQL变成复杂一点的连接查询，t2表依然先对单列索引IDX_ON_D3_TT2扫描过滤，再回表后硬过滤（d1<50），
最后才与t1表做表连接（哈希连接方式）查询。

-- 示例9：建回复合索引（d3,d1）
create index idx_on_d3d1_tt2 on tt2(d3,d1);

select /*+ ENABLE_INDEX_FILTER(1)*/ * 
from tt2 t2 join tt1 t1 on t2.d1 = t1.c1 
where d3 < 100 and d1 < 50;
/*
1   #NSET2: [2, 4, 108] 
2     #PRJT2: [2, 4, 108]; exp_num(5), is_atom(FALSE) 
3       #HASH2 INNER JOIN: [2, 4, 108];  KEY_NUM(1); KEY(T2.D1=T1.C1) KEY_NULL_EQU(0)
4         #BLKUP2: [1, 4, 56]; IDX_ON_D3D1_TT2(T2)
5           #SLCT2: [1, 4, 56]; T2.D1 < 50   -- 怎么估算成4行
6             #SSEK2: [1, 4, 56]; scan_type(ASC), IDX_ON_D3D1_TT2(TT2 as T2), scan_range[(null2,min),(100,min))
7         #SLCT2: [0, 49, 52]; T1.C1 < 50
8           #CSCN2: [0, 1000, 52]; INDEX33555613(TT1 as T1)
*/
结论：
成功利用复合索引，先索引快速扫描（d3<100），再在索引扫描后的基础上继续过滤（d1<50），接着回表查询，
最后才与t1做表连接查询。

3.2 讲解2：IN查询列表走索引过滤优化 +HASH RIGHT SEMI JOIN

-- 示例1：过滤条件列全部包含在复合索引，且前导列为范围比较，默认CBO优化器选出以下最优执行路径
select  * 
from tt2 t2 
where d3 < 100 and d1 in (10, 20, 30);
/*
1   #NSET2: [10, 1, 64] 
2     #PRJT2: [10, 1, 64]; exp_num(4), is_atom(FALSE) 
3       #HASH RIGHT SEMI JOIN2: [10, 1, 64]; n_keys(1)   KEY(DMTEMPVIEW_16779283.colname=T2.D1) KEY_NULL_EQU(0)
4         #CONST VALUE LIST: [0, 3, 4]; row_num(3), col_num(1), 
5         #BLKUP2: [10, 9900, 64]; IDX_ON_D3D1_TT2(T2)  -- 过早回表，数据量一大，效率非常低
6           #SSEK2: [10, 9900, 64]; scan_type(ASC), IDX_ON_D3D1_TT2(TT2 as T2), scan_range[(null2,min),(100,min))
*/
结论：虽然对IDX_ON_D3D1_TT2索引范围扫描过滤，但只针对过滤条件（d3<100）应用其他特性，紧接着开始回表查询t2，可能会造成大量的回表操作。
而IN查询列表被当为整体（常量表）处理并作为驱动表（数据量小，优化选为HASH连接的驱动表），再与刚才回表t2再做哈希右半连接。

-- 示例2：过滤条件列全部包含在复合索引，且前导列为范围比较，强制索引过滤优化
select /*+ ENABLE_INDEX_FILTER(2)*/ * 
from tt2 t2 
where d3 < 100 and d1 in (10, 20, 30);
/*
1   #NSET2: [1, 1, 64] 
2     #PRJT2: [1, 1, 64]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [1, 1, 64]; IDX_ON_D3D1_TT2(T2)
4         #HASH RIGHT SEMI JOIN2: [1, 1, 64]; n_keys(1)   KEY(DMTEMPVIEW_16779317.colname=T2.D1) KEY_NULL_EQU(0)
5           #CONST VALUE LIST: [0, 3, 4]; row_num(3), col_num(1), 
6           #SSEK2: [1, 1, 64]; scan_type(ASC), IDX_ON_D3D1_TT2(TT2 as T2), scan_range[(null2,min),(100,min))
*/
结论：IN查询列表被作为整体的常量列表（视作单列表），t2扫描先索引范围扫描过滤掉d1无用数据，IDX_ON_D3D1_TT2
再将IDX_ON_D3D1_TT2索引剩余数据与常量表中的列（DMTEMPVIEW_16779317.colname）哈希右半连接查询，最后才回表查询。
可能大家疑问为什么在索引扫描后不直接回表查询？
原因：IDX_ON_D3D1_TT2索引中包含d3和d1列（可理解为索引是一张精简表，即瘦表，同样具备像表的数据行特征），
在索引中先过滤掉d3列无用数据，又因索引中存存d1列，所以它可以直接与常量表做连接查询，不用立马就回表查询完t2所有字段数据，
如此一来减少数据量的连接和回表操作量，何乐而不为。

-- 示例3：过滤条件列全部包含在复合索引，且前导列为等值比较
select /*+ ENABLE_INDEX_FILTER(2)*/ * 
from tt2 t2 
where d3 in (100, 200, 300) and d1 < 50;
/*
1   #NSET2: [0, 1, 64] 
2     #PRJT2: [0, 1, 64]; exp_num(4), is_atom(FALSE) 
3       #NEST LOOP INDEX JOIN2: [0, 1, 64]   -- 走索引连接，因右孩子走了索引扫描
4         #CONST VALUE LIST: [0, 3, 4]; row_num(3), col_num(1), 
5         #BLKUP2: [0, 0, 64]; IDX_ON_D3D1_TT2(T2)
6           #SSEK2: [0, 0, 64]; scan_type(ASC), IDX_ON_D3D1_TT2(TT2 as T2), scan_range((DMTEMPVIEW_16779274.colname,null2),(DMTEMPVIEW_16779274.colname,50))
*/
结论：看SQL语义条件满足复合索引的完全应用条件，即前缀列为等值比较在前，非等值比较置后。
此时优化参数不再生效，CBO查询优化器考虑走复合索引全部列的索引定位更快，代价更小，
故通过常量表嵌套连接查询，将值传给复合索引扫描后立即回表查询。

-- 示例4：查询语句与示例3保持不变，但手工禁用索引连接查询
select /*+ ENABLE_INDEX_FILTER(2) ENABLE_INDEX_JOIN(0)*/ * 
from tt2 t2 
where d3 in (100, 200, 300) and d1 < 50;
/*
1   #NSET2: [12, 3, 68] 
2     #PRJT2: [12, 3, 68]; exp_num(4), is_atom(FALSE) 
3       #HASH2 INNER JOIN: [12, 3, 68];  KEY_NUM(1); KEY(DMTEMPVIEW_16779280.colname=T2.D3) KEY_NULL_EQU(0)
4         #CONST VALUE LIST: [0, 3, 4]; row_num(3), col_num(1), 
5         #BLKUP2: [11, 48, 64]; IDX_ON_D3D1_TT2(T2)
6           #SLCT2: [11, 48, 64]; T2.D1 < 50
7             #SSCN: [11, 48, 64]; IDX_ON_D3D1_TT2(TT2 as T2)
*/
结论：
看上去t2表确实走索引扫描IDX_ON_D3D1_TT2，并没什么不同。细心就可以发现走的是索引全扫描SSCN，
同时优化器判断d1列的直方图信息（缺省）满足跳跃索引扫描，所以二级索引IDX_ON_D3D1_TT2确实先全部扫描，
再普通过滤来D1 < 50的索引数据，继续回表查询t2，最后才与常量表做哈希连接查询。

-- 示例5：验证IN子查询是否满足ENABLE_INDEX_FILTER优化
select * 
from tt2 t2 
where d3 < 100 and d1 in (select c1 from tt1 t1 where c2 < 10);
/*
1   #NSET2: [12, 50, 64] 
2     #PRJT2: [12, 50, 64]; exp_num(4), is_atom(FALSE) 
3       #HASH RIGHT SEMI JOIN2: [12, 50, 64]; n_keys(1)  join condition(exp_cast(T1.C2) < 10) KEY(T1.C1=T2.D1) KEY_NULL_EQU(0)
4         #CSCN2: [0, 1000, 52]; INDEX33555613(TT1 as T1)
5         #BLKUP2: [10, 9900, 64]; IDX_ON_D3D1_TT2(T2)
6           #SSEK2: [10, 9900, 64]; scan_type(ASC), IDX_ON_D3D1_TT2(TT2 as T2), scan_range[(null2,min),(100,min))
*/

select /*+ ENABLE_INDEX_FILTER(2)*/ *    
from tt2 t2 
where d3 < 100 and d1 in (select c1 from tt1 t1 where c1 < 10);
/*
1   #NSET2: [12, 8, 64] 
2     #PRJT2: [12, 8, 64]; exp_num(4), is_atom(FALSE) 
3       #HASH RIGHT SEMI JOIN2: [12, 8, 64]; n_keys(1)  join condition(T1.C1 < 10) KEY(T1.C1=T2.D1) KEY_NULL_EQU(0)
4         #CSCN2: [0, 1000, 4]; INDEX33555613(TT1 as T1) -- 全表扫描
5         #BLKUP2: [10, 9900, 64]; IDX_ON_D3D1_TT2(T2) -- 索引范围扫描再回表
6           #SSEK2: [10, 9900, 64]; scan_type(ASC), IDX_ON_D3D1_TT2(TT2 as T2), scan_range[(null2,min),(100,min))
*/
结论：无论是否指定优化ENABLE_INDEX_FILTER参数，根本没起到作用，即不满足此参数的优化条件。
t2表走IDX_ON_D3D1_TT2索引筛掉一部分数据，再与t1表做哈希右半连接查询，哈希连接定位条件（T1.C1=T2.D1），再做连接过滤条件c1 < 10。