注册

优化问题

fang 2023/12/08 684 1

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:
【操作系统】:
【CPU】:
【问题描述】*:
两个字段建了组合索引,如下面执行计划走的索引全扫描
select count(1) from DOC_BAK t1
where ( t1.target_id = 861080310864871425 and t1.target_type = 1 )
or ( t1.target_id = 861080310864871426 and t1.target_type = 110 );
执行计划如下:

1 #NSET2: [130, 1, 60]
2 #PRJT2: [130, 1, 60]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [130, 1, 60]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #SLCT2: [130, 51335, 60]; LIST = LIST
5 #SSCN: [130, 51335, 60]; IDX_DOC_BAK_UU01(DOC_BAK as T1)

当去掉一个 type条件时,执行计划如下

select count(1) from DOC_BAK t1
where ( t1.target_id = 861080310864871425 and t1.target_type = 1 )
or ( t1.target_id = 861080310864871426
-- and t1.target_type = 110
);
执行计划如下:
1 #NSET2: [1, 1, 72]
2 #PRJT2: [1, 1, 72]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [1, 1, 72]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #UNION FOR OR2: [1, 6, 72]; key_num(0), outer_join(-)
5 #SLCT2: [1, 5, 72]; T1.TARGET_ID IN LIST
6 #SSEK2: [1, 5, 72]; scan_type(ASC), IDX_DOC_BAK_UU01(DOC_BAK as T1), scan_range[(exp_cast(861080310864871426),min),(exp_cast(861080310864871426),max))
7 #SLCT2: [1, 1, 72]; (T1.TARGET_ID IN LIST AND exp11)
8 #SSEK2: [1, 1, 72]; scan_type(ASC), IDX_DOC_BAK_UU01(DOC_BAK as T1), scan_range[(exp_cast(861080310864871425),exp_cast(1)),(exp_cast(861080310864871425),exp_cast(1))]

回答 0
暂无回答
扫一扫
联系客服