问题

在项目中我们可以通过optimzer_or_nbexp参数来优化or条件，在这个参数的优化情况下，要么OR表达式考虑整体处理方式，要么过滤条件好的情况下，让它生成 UNION_FOR_OR 操作符，利用索引处理。在全局优化下，二者不可兼得。有时我们可以通过把语句写成我们想要的方式，这样就既可以很好地利用规则达到优化目的，也可以避免一些场景在规则影响下效率低下的情况。下面是一个例子适合做or合并处理的，通过改写语句来达到目的。

测例

drop table if exists t1;
create table t1 (id int primary key,c1 number(4,0),c3 varchar2(4),c4 varchar2(2),c5 varchar2(2),
c6 timestamp)
partition by list(c3)(
partition P_0401 values('0401'),
partition P_0402 values('0402'),
partition P_0403 values('0403'),
partition P_0404 values('0404'),
partition P_0405 values('0405'),
partition P_0406 values('0406'),
partition P_0407 values('0407'),
partition P_0408 values('0408'),
partition P_0409 values('0409'),
partition P_DEFALUE values(default)
);

insert into t1 select level,round(dbms_random.value(1,100),0),'0'||to_char(round(dbms_random.value(401,409),0)),to_char(round(dbms_random.value(1,2),0)),
to_char(round(dbms_random.value(1,2),0)),sysdate-round(dbms_random.value(0,730),0) from dual connect by level<=200000;
commit;

drop table if exists t2;
create table t2 (id int primary key,d1 int,d2 int);
insert into t2 select level,dbms_random.value(1,50000),round(dbms_random.value(1,2),0) from dual connect by level<=100000;
commit;


BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => USER,
                                tabname          => 'T1',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size auto',
                                no_invalidate    => FALSE,
                                granularity      => 'ALL',
                                cascade          => TRUE);
END;

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => USER,
                                tabname          => 'T2',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size auto',
                                no_invalidate    => FALSE,
                                granularity      => 'ALL',
                                cascade          => TRUE);
END;


用例：
SELECT * FROM(SELECT INNER_TABLE.*,ROWNUM OUTER_TABLE_ROWNUM FROM (SELECT distinct T1.*,t2.d1
 from t1
left join t2 on t1.id=t2.d1
where t1.c4=1
and (t1.c1>50
or t2.d2=1)
)INNER_TABLE ) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM<=25AND OUTER_TABLE_ROWNUM >0
执行时间：0.173s
计划：

1   #NSET2: [200, 1, 191] 
2     #PIPE2: [200, 1, 191] 
3       #PRJT2: [136, 1, 191]; exp_num(8), is_atom(FALSE) 
4         #SLCT2: [136, 1, 191]; OUTER_TABLE.OUTER_TABLE_ROWNUM > var2
5           #PRJT2: [136, 25, 191]; exp_num(8), is_atom(FALSE) 
6             #RNSK: [136, 25, 191]; rownum <= exp_cast(25)
7               #PRJT2: [136, 25, 191]; exp_num(7), is_atom(FALSE) 
8                 #TOPN2: [136, 25, 191]; top_num(25)
9                   #DISTINCT: [136, 25, 191] 
10                    #UNION FOR OR2: [136, 7506, 191]; key_num(0), outer_join(-)
11                      #SLCT2: [67, 2502, 191]; DMTEMPVIEW_889199073.TMPCOL7 = 1
12                        #HEAP TABLE SCAN: [64, 100083, 191]; table_no(0) 
13                      #SLCT2: [67, 5004, 191]; (DMTEMPVIEW_889199073.TMPCOL2 > var3 AND exp11)
14                        #HEAP TABLE SCAN: [64, 100083, 191]; table_no(0) 
15      #HEAP TABLE: [64, 100083, 191]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
16        #HASH RIGHT JOIN2: [64, 100083, 191]; key_num(1), ret_null(0), KEY(T2.D1=T1.ID)
17          #CSCN2: [10, 100000, 8]; INDEX33555827(T2); btr_scan(1)
18          #PARALLEL: [30, 100083, 191]; scan_type(FULL), key_num(0, 0, 0), simple(0)
19            #SLCT2: [30, 100083, 191]; exp_cast(T1.C4) = 1
20              #CSCN2: [30, 200000, 191]; INDEX33555805(T1); btr_scan(1)
在optimizer_or_nbexp(2)优化时
SELECT /*+OPTIMIZER_OR_NBEXP(2)*/* FROM(SELECT INNER_TABLE.*,ROWNUM OUTER_TABLE_ROWNUM FROM (SELECT distinct T1.*,t2.d1
 from t1
left join t2 on t1.id=t2.d1
where t1.c4=1
and (t1.c1>50
or t2.d2=1)
)INNER_TABLE ) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM<=25AND OUTER_TABLE_ROWNUM >0
执行时间：0.042s
计划：
1   #NSET2: [6, 1, 191] 
2     #PRJT2: [6, 1, 191]; exp_num(8), is_atom(FALSE) 
3       #SLCT2: [6, 1, 191]; OUTER_TABLE.OUTER_TABLE_ROWNUM > var2
4         #PRJT2: [6, 25, 191]; exp_num(8), is_atom(FALSE) 
5           #RNSK: [6, 25, 191]; rownum <= exp_cast(25)
6             #PRJT2: [6, 25, 191]; exp_num(7), is_atom(FALSE) 
7               #TOPN2: [6, 25, 191]; top_num(25)
8                 #DISTINCT: [6, 25, 191] 
9                   #SLCT2: [6, 10008, 191]; (T2.D2 = 1 OR T1.C1 > var3)
10                    #INDEX JOIN LEFT JOIN2: [1, 200166, 191]; ret_null(0)
11                      #PARALLEL: [30, 100083, 191]; scan_type(FULL), key_num(0, 0, 0), simple(0)
12                        #SLCT2: [30, 100083, 191]; exp_cast(T1.C4) = 1
13                          #CSCN2: [30, 200000, 191]; INDEX33555805(T1); btr_scan(1)
14                      #BLKUP2: [3306, 2, 4]; IDX_DM_T2_D1(T2)
15                        #SSEK2: [3306, 2, 4]; scan_type(ASC), IDX_DM_T2_D1(T2), scan_range[T1.ID,T1.ID], is_global(0)

优化

上面的例子显然or要整体处理才更加高效，如何用改写语句方式实现？
分析上面optimizer_or_nbexp(2)的计划，or条件
9 #SLCT2: [6, 10008, 191]; (T2.D2 = 1 OR T1.C1 > var3)
它在关联后再筛选获取，那是不是关联后作为查询项在外层用where 筛选满足条件的。因此想到使用case when，把条件放到查询项，满足的就置为1，不满足的置为0，再用where获取为1的数据。

改写如下：
SELECT * FROM(SELECT INNER_TABLE.*,ROWNUM OUTER_TABLE_ROWNUM FROM (select distinct id,c1,c3,c4,c5,d1 from (SELECT T1.*,t2.d1,case when (t1.c1>50
or t2.d2=1) then 1 else 0 end as sel
 from t1
left join t2 on t1.id=t2.d1
where t1.c4=1
) AA where sel=1
)INNER_TABLE ) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM<=25AND OUTER_TABLE_ROWNUM >0
计划：

1   #NSET2: [6, 1, 191] 
2     #PRJT2: [6, 1, 191]; exp_num(7), is_atom(FALSE) 
3       #SLCT2: [6, 1, 191]; OUTER_TABLE.OUTER_TABLE_ROWNUM > var2
4         #PRJT2: [6, 25, 191]; exp_num(7), is_atom(FALSE) 
5           #RNSK: [6, 25, 191]; rownum <= exp_cast(25)
6             #PRJT2: [6, 25, 191]; exp_num(6), is_atom(FALSE) 
7               #TOPN2: [6, 25, 191]; top_num(25)
8                 #DISTINCT: [6, 25, 191] 
9                   #SLCT2: [6, 5004, 191]; exp11 = 1
10                    #INDEX JOIN LEFT JOIN2: [1, 200166, 191]; ret_null(0)
11                      #PARALLEL: [30, 100083, 191]; scan_type(FULL), key_num(0, 0, 0), simple(0)
12                        #SLCT2: [30, 100083, 191]; exp_cast(T1.C4) = 1
13                          #CSCN2: [30, 200000, 191]; INDEX33555805(T1); btr_scan(1)
14                      #BLKUP2: [3306, 2, 4]; IDX_DM_T2_D1(T2)
15                        #SSEK2: [3306, 2, 4]; scan_type(ASC), IDX_DM_T2_D1(T2), scan_range[T1.ID,T1.ID], is_global(0)

与or整体处理的方式一致，执行时间：0.033s

总结

当or条件的分支来自不同表的列，且过滤性不好的情况下，可以采用case when的方式在查询项中判断满足的为1，不满足的为0，再外层筛选为1即满足条件的数据即可是我们想要的计划来优化语句，也不受规则的影响。