记录一次比较奇怪的连接条件无法下推的问题，明明都有合适的索引却下推不了，结果只是因为一个参数。

问题SQL

select /*+ use_htab(0) enable_hash_join(0)  view_pullup_flag(7) */
        * 
  from ( select row_.*, 
               rownum rownum_ 
          from ( select 
                         tr_DATE as tr_DATE, 
                         t1.CONTRACT_ID, 
                         MATCH_TIME as MATCH_TIME, 
                         PRICE as PRICE, 
                         QTY as QTY, 
                         t2.order_time as ORDER_TIME, 
                         t2.qty as ORDER_QTY, 
                         t2.price as ORDER_PRICE, 
                         t2.trader_no as TRADER_NO, 
                         t3.order_time as s_ORDER_TIME, 
                         t3.qty as s_ORDER_QTY, 
                         t3.price as s_ORDER_PRICE, 
                         t3.trader_no as s_TRADER_NO, 
                         MEMBER_ID as MEMBER_ID, 
                         t4.abbr as MEMBER_NAME, 
                         CLIENT_ID as CLIENT_ID, 
                         t6.client_name as CLIENT_NAME, 
                         ORDER_NO as ORDER_NO, 
                         t2.eo_flag as EO_FLAG, 
                         t2.sh_flag as SH_FLAG, 
                         s_MEMBER_ID as s_MEMBER_ID, 
                         t5.abbr as s_MEMBER_NAME, 
                         s_CLIENT_ID as s_CLIENT_ID, 
                         t7.client_name as s_CLIENT_NAME, 
                         s_ORDER_NO as s_ORDER_NO, 
                         t3.eo_flag as s_EO_FLAG, 
                         t3.sh_flag as s_SH_FLAG 
                    from (select * 
                            from tr_bucket_dtl 
                           where off_tr_date = '20250311' 
                             and off_match_no = '101404365'
                             and tr_type = '0' ) t1 
               left join VW_BUCKET t2 
                      on t1.tr_date = t2.tr_date 
                     and t1.order_no = t2.order_no 
                     and t2.client_id = '06157358' 
                     and t2.member_id = '0184' 
               left join VW_BUCKET t3 
                      on t1.tr_date = t3.tr_date 
                     and t1.s_order_no = t3.order_no 
                     and t3.client_id = '03533760'
                     and t3.member_id = '0030'
               left join rmmd.tm_member t4 
                      on t1.MEMBER_ID = t4.member_id 
               left join rmmd.tm_member t5 
                      on t1.s_member_id = t5.member_id 
               left join rmmd.tm_client_sort t6 
                      on t1.client_id = t6.client_id 
               left join rmmd.tm_client_sort t7 
                      on t1.s_client_id = t7.client_id 
                   where 1=1 
                     and t1.off_match_no = '101404365'
                     and t1.off_tr_date = '20250311' 
                order by t1.tr_DATE, 
                         t1.match_no asc ) row_ ) 
 where rownum_ <= 10;

执行计划

1   #NSET2: [13993392, 1, 4634] 
2     #PRJT2: [13993392, 1, 4634]; exp_num(28), is_atom(FALSE) 
3       #PRJT2: [13993392, 1, 4634]; exp_num(28), is_atom(FALSE) 
4         #RNSK: [13993392, 1, 4634]; rownum <= exp_cast(10)
5           #PRJT2: [13993392, 1, 4634]; exp_num(27), is_atom(FALSE) 
6             #SORT3: [13993392, 1, 4634]; key_num(2), is_distinct(FALSE), top_flag(1), is_adaptive(0)
7               #INDEX JOIN LEFT JOIN2: [13993391, 1, 4634]  ret_null(0)
8                 #INDEX JOIN LEFT JOIN2: [13993391, 1, 4634]  ret_null(0)
9                   #INDEX JOIN LEFT JOIN2: [13993391, 1, 4634]  ret_null(0)
10                    #INDEX JOIN LEFT JOIN2: [13993391, 1, 4634]  ret_null(0)
11                      #NEST LOOP LEFT JOIN2: [13993391, 1, 4634]; join condition((tr_bucket_dtl.tr_DATE = T3.tr_DATE AND tr_bucket_dtl.s_ORDER_NO = T3.ORDER_NO)) partition_keys_num(0) ret_null(0)
12                        #NEST LOOP LEFT JOIN2: [1260528, 1, 3128]; join condition((tr_bucket_dtl.tr_DATE = T2.tr_DATE AND tr_bucket_dtl.ORDER_NO = T2.ORDER_NO)) partition_keys_num(0) ret_null(0)
13                          #SLCT2: [1, 1, 1622]; tr_bucket_dtl.tr_TYPE = '0'
14                            #BLKUP2: [1, 1, 1622]; INDEX33561223(tr_bucket_dtl)
15                              #SSEK2: [1, 1, 1622]; scan_type(ASC), INDEX33561223(tr_bucket_dtl), scan_range[(exp_cast('101404365'),'20250311',min,min),(exp_cast('101404365'),'20250311',max,max))
16                          #SLCT2: [58981, 3564881, 1506]; (T2.CLIENT_ID = '06157358' AND T2.MEMBER_ID = '0184')
17                            #PRJT2: [56604, 71297623, 1506]; exp_num(10), is_atom(FALSE) 
18                              #UNION ALL: [56604, 71297623, 1506]
19                                #PRJT2: [4658, 238527, 1506]; exp_num(10), is_atom(FALSE) 
20                                  #SLCT2: [4658, 238527, 1506]; var2 = var3
21                                    #CSCN2: [4658, 9541108, 1506]; INDEX33557205(TR_FTR)
22                                #PRJT2: [34019, 71059096, 1506]; exp_num(10), is_atom(FALSE) 
23                                  #PARALLEL: [34019, 71059096, 1506]; scan_type(FULL), key_num(0, 0, 0), simple(0)
24                                    #CSCN2: [34019, 71059096, 1506]; INDEX33561224(TH_FTR)
25                        #SLCT2: [58981, 3564881, 1506]; (T3.CLIENT_ID = '03533760' AND T3.MEMBER_ID = '0030')
26                          #PRJT2: [56604, 71297623, 1506]; exp_num(10), is_atom(FALSE) 
27                            #UNION ALL: [56604, 71297623, 1506]
28                              #PRJT2: [4658, 238527, 1506]; exp_num(10), is_atom(FALSE) 
29                                #SLCT2: [4658, 238527, 1506]; var4 = var5
30                                  #CSCN2: [4658, 9541108, 1506]; INDEX33557205(TR_FTR)
31                              #PRJT2: [34019, 71059096, 1506]; exp_num(10), is_atom(FALSE) 
32                                #PARALLEL: [34019, 71059096, 1506]; scan_type(FULL), key_num(0, 0, 0), simple(0)
33                                  #CSCN2: [34019, 71059096, 1506]; INDEX33561224(TH_FTR)
34                      #BLKUP2: [1, 1, 48]; INDEX33556875(T4)
35                        #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556875(TM_MEMBER as T4), scan_range[(tr_bucket_dtl.MEMBER_ID,min),(tr_bucket_dtl.MEMBER_ID,max))
36                    #BLKUP2: [1, 1, 48]; INDEX33556875(T5)
37                      #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556875(TM_MEMBER as T5), scan_range[(tr_bucket_dtl.s_MEMBER_ID,min),(tr_bucket_dtl.s_MEMBER_ID,max))
38                  #BLKUP2: [1, 1, 48]; INDEX33556894(T6)
39                    #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556894(TM_CLIENT_SORT as T6), scan_range[tr_bucket_dtl.CLIENT_ID,tr_bucket_dtl.CLIENT_ID]
40                #BLKUP2: [1, 1, 48]; INDEX33556894(T7)
41                  #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556894(TM_CLIENT_SORT as T7), scan_range[tr_bucket_dtl.s_CLIENT_ID,tr_bucket_dtl.s_CLIENT_ID]

SQL分析

SQL逻辑比较简单，就是通过t1部分的传参（过滤性较高，一般就返回1-2条行记录），将t1过滤后的结果通过关联条件下推到t2和t3的视图部分，如下图一所示：

视图结构如下（简化后）：

CREATE OR REPLACE VIEW VW_BUCKET AS
SELECT 
    tr_DATE,
    ORDER_NO,
    TID,
    MEMBER_ID,
    CLIENT_ID,
    ...
FROM TR_FTR   --实时表（普通表）
where 1 = fn_get_alertflag 
UNION ALL 
SELECT 
    tr_DATE,
    ORDER_NO,
    TID,
    MEMBER_ID,
    CLIENT_ID,
   ...
FROM TH_FTR;   --历史表（分区表）

并且tr_date和order_no字段为视图内部两张表的唯一索引和主键

CREATE OR REPLACE UNIQUE  INDEX INDEX_TH_FTR ON TH_FTR(TR_DATE ASC,ORDER_NO ASC) STORAGE(ON TBS_INDEX, CLUSTERBTR) ;
alter table TR_FTR add CONSTRAINT "PK_TR_FTR" NOT CLUSTER PRIMARY KEY("TR_DATE", "ORDER_NO") USING INDEX TABLESPACE "TBS_INDEX" ) STORAGE(ON "TBS_LARGE", CLUSTERBTR) ;

最终整体也是只返回两条行记录。

排查执行计划发现t1与t2和t3部分虽然通过hint enable_hash_join(0)走了嵌套循环的执行计划，但是t2和t3作为被驱动表并没有利用唯一索引进行有效的条件下推，而是进行了全表扫描，如下图二所示：

综上所述，问题SQL的代价主要还是消耗在了被驱动表低效率的嵌套循环关联，由于驱动表t1需要返回两条行记录，因此被驱动表也要循环两次，而且t1与视图left join了两次，就需要对TR_FTR表和TH_FTR表分别做四次全表扫描，导致效率低下。

问题排查

正常来说驱动表的过滤性很好，被驱动表的关联字段上也已经存在了合适的索引，走嵌套循环执行计划是可以通过连接条件下推的，问题是条件已经具备却没有走合适的执行计划，结果一顿排查数据库参数之后，发现了一个参数OPT_CVT_VAR_COST_FACTOR，当前值是0（据说为了解决其他问题改的… ಠ_ಠ），此参数为调整连接转变为VAR方式的代价值，值越大就越倾向于走“with var”通过变量传参做嵌套循环，默认值为1，当参数为0时就会导致不倾向传参方式将连接条件下推，也是计划跑偏的主要原因。此外再配合enable_hash_join(0)固定为嵌套循环连接方式，use_htab(0)避免产生HTAB临时数据来调整执行计划，利用被驱动表的唯一索引做高效的条件下推。

问题验证

select /*+ use_htab(0) enable_hash_join(0) opt_cvt_var_cost_factor(10000) */
        * 
  from ( select row_.*, 
               rownum rownum_ 
          from ( select 
                         tr_DATE as tr_DATE, 
                         t1.CONTRACT_ID, 
                         MATCH_TIME as MATCH_TIME, 
                         PRICE as PRICE, 
                         QTY as QTY, 
                         t2.order_time as ORDER_TIME, 
                         t2.qty as ORDER_QTY, 
                         t2.price as ORDER_PRICE, 
                         t2.trader_no as TRADER_NO, 
                         t3.order_time as s_ORDER_TIME, 
                         t3.qty as s_ORDER_QTY, 
                         t3.price as s_ORDER_PRICE, 
                         t3.trader_no as s_TRADER_NO, 
                         MEMBER_ID as MEMBER_ID, 
                         t4.abbr as MEMBER_NAME, 
                         CLIENT_ID as CLIENT_ID, 
                         t6.client_name as CLIENT_NAME, 
                         ORDER_NO as ORDER_NO, 
                         t2.eo_flag as EO_FLAG, 
                         t2.sh_flag as SH_FLAG, 
                         s_MEMBER_ID as s_MEMBER_ID, 
                         t5.abbr as s_MEMBER_NAME, 
                         s_CLIENT_ID as s_CLIENT_ID, 
                         t7.client_name as s_CLIENT_NAME, 
                         s_ORDER_NO as s_ORDER_NO, 
                         t3.eo_flag as s_EO_FLAG, 
                         t3.sh_flag as s_SH_FLAG 
                    from (select * 
                            from tr_bucket_dtl 
                           where off_tr_date = '20250311' 
                             and off_match_no = '101404365'
                             and tr_type = '0' ) t1 
               left join VW_BUCKET t2 
                      on t1.tr_date = t2.tr_date 
                     and t1.order_no = t2.order_no 
                     and t2.client_id = '06157358' 
                     and t2.member_id = '0184' 
               left join VW_BUCKET t3 
                      on t1.tr_date = t3.tr_date 
                     and t1.s_order_no = t3.order_no 
                     and t3.client_id = '03533760'
                     and t3.member_id = '0030'
               left join rmmd.tm_member t4 
                      on t1.MEMBER_ID = t4.member_id 
               left join rmmd.tm_member t5 
                      on t1.s_member_id = t5.member_id 
               left join rmmd.tm_client_sort t6 
                      on t1.client_id = t6.client_id 
               left join rmmd.tm_client_sort t7 
                      on t1.s_client_id = t7.client_id 
                   where 1=1 
                     and t1.off_match_no = '101404365'
                     and t1.off_tr_date = '20250311' 
                order by t1.tr_DATE, 
                         t1.match_no asc ) row_ ) 
 where rownum_ <= 10;

1   #NSET2: [1, 1, 4634] 
2     #PRJT2: [1, 1, 4634]; exp_num(28), is_atom(FALSE) 
3       #PRJT2: [1, 1, 4634]; exp_num(28), is_atom(FALSE) 
4         #RNSK: [1, 1, 4634]; rownum <= exp_cast(10)
5           #PRJT2: [1, 1, 4634]; exp_num(27), is_atom(FALSE) 
6             #SORT3: [1, 1, 4634]; key_num(2), is_distinct(FALSE), top_flag(1), is_adaptive(0)
7               #INDEX JOIN LEFT JOIN2: [1, 1, 4634]  ret_null(0)
8                 #INDEX JOIN LEFT JOIN2: [1, 1, 4634]  ret_null(0)
9                   #INDEX JOIN LEFT JOIN2: [1, 1, 4634]  ret_null(0)
10                    #INDEX JOIN LEFT JOIN2: [1, 1, 4634]  ret_null(0)
11                      #NEST LOOP LEFT JOIN2: [1, 1, 4634]; join condition((T3.CLIENT_ID = '03533760' AND T3.MEMBER_ID = '0030' AND T1.tr_DATE = T3.tr_DATE AND T1.s_ORDER_NO = T3.ORDER_NO))[with var] partition_keys_num(0) ret_null(0)
12                        #NEST LOOP LEFT JOIN2: [1, 1, 3128]; join condition((T2.CLIENT_ID = '06157358' AND T2.MEMBER_ID = '0184' AND T1.tr_DATE = T2.tr_DATE AND T1.ORDER_NO = T2.ORDER_NO))[with var] partition_keys_num(0) ret_null(0)
13                          #PRJT2: [1, 1, 1622]; exp_num(12), is_atom(FALSE) 
14                            #SLCT2: [1, 1, 1622]; tr_bucket_dtl.tr_TYPE = '0'
15                              #BLKUP2: [1, 1, 1622]; INDEX33561223(tr_bucket_dtl)
16                                #SSEK2: [1, 1, 1622]; scan_type(ASC), INDEX33561223(tr_bucket_dtl), scan_range[(exp_cast('101404365'),'20250311',min,min),(exp_cast('101404365'),'20250311',max,max))
17                          #PRJT2: [1, 2, 1506]; exp_num(10), is_atom(FALSE) 
18                            #UNION ALL: [1, 2, 1506]
19                              #PRJT2: [1, 1, 1506]; exp_num(10), is_atom(FALSE) 
20                                #SLCT2: [1, 1, 1506]; var6 = var7
21                                  #BLKUP2: [1, 1, 1506]; INDEX33557206(TR_FTR)
22                                    #SSEK2: [1, 1, 1506]; scan_type(ASC), INDEX33557206(TR_FTR), scan_range[(var4,var5),(var4,var5)]
23                              #PRJT2: [1, 1, 1506]; exp_num(10), is_atom(FALSE) 
24                                #PARALLEL: [1, 1, 1506]; scan_type(EQU), key_num(1, 0, 0), simple(0)
25                                  #BLKUP2: [1, 1, 1506]; INDEX_TH_FTR(TH_FTR)
26                                    #SSEK2: [1, 1, 1506]; scan_type(ASC), INDEX_TH_FTR(TH_FTR), scan_range[(var4,var5),(var4,var5)]
27                        #PRJT2: [1, 2, 1506]; exp_num(10), is_atom(FALSE) 
28                          #UNION ALL: [1, 2, 1506]
29                            #PRJT2: [1, 1, 1506]; exp_num(10), is_atom(FALSE) 
30                              #SLCT2: [1, 1, 1506]; var8 = var9
31                                #BLKUP2: [1, 1, 1506]; INDEX33557206(TR_FTR)
32                                  #SSEK2: [1, 1, 1506]; scan_type(ASC), INDEX33557206(TR_FTR), scan_range[(var2,var3),(var2,var3)]
33                            #PRJT2: [1, 1, 1506]; exp_num(10), is_atom(FALSE) 
34                              #PARALLEL: [1, 1, 1506]; scan_type(EQU), key_num(1, 0, 0), simple(0)
35                                #BLKUP2: [1, 1, 1506]; INDEX_TH_FTR(TH_FTR)
36                                  #SSEK2: [1, 1, 1506]; scan_type(ASC), INDEX_TH_FTR(TH_FTR), scan_range[(var2,var3),(var2,var3)]
37                      #BLKUP2: [1, 1, 48]; INDEX33556875(T4)
38                        #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556875(TM_MEMBER as T4), scan_range[(T1.MEMBER_ID,min),(T1.MEMBER_ID,max))
39                    #BLKUP2: [1, 1, 48]; INDEX33556875(T5)
40                      #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556875(TM_MEMBER as T5), scan_range[(T1.s_MEMBER_ID,min),(T1.s_MEMBER_ID,max))
41                  #BLKUP2: [1, 1, 48]; INDEX33556894(T6)
42                    #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556894(TM_CLIENT_SORT as T6), scan_range[T1.CLIENT_ID,T1.CLIENT_ID]
43                #BLKUP2: [1, 1, 48]; INDEX33556894(T7)
44                  #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556894(TM_CLIENT_SORT as T7), scan_range[T1.s_CLIENT_ID,T1.s_CLIENT_ID]

优化后执行计划，可见第22、26、32、36行的计划已经可以利用到唯一索引做条件下推，并且其中的历史分区表也可以做scan_type(EQU)进行分区裁剪，最后整体耗时47ms。