项目遇到过这种情况，虽然看起来代价高，但改用HASH执行更快，但这个代价不一定准的，建议你选择更快的执行方式

根据sql语句分析.

1. txt 看见有乱码,建议处理一下.
   
2. 筛选条件中, like 有前置匹配. 建议根据业务逻辑, 进行转换或改写.
   

https://eco.dameng.com/community/question/8d17a0a910b037f5c7056569d7207265
可以参考一下.

做个试验，先看看下面这个SQL，执行耗时大概多少。

SELECT 主键字段1,主键字段2....,ROW_ID
  FROM (SELECT p.主键字段1,p.主键字段2....  --这里只取主键字段，其他字段不取
              ,ROW_NUMBER() OVER (ORDER BY p.updateDate desc,p.statYear desc) AS ROW_ID
          FROM proposal_cached p
          join grant_setting gs on gs.grant_code = p.grant_code
         where p.grant_code <> 1000   
           and p.status in ('00','01','02','03','05','06','08','09','98','10')
           and gs.prp_type= '0'  
           and p.org_code = ?
           and p.prp_version like '%' || ? || '%' ESCAPE '\'
       )
 WHERE ROW_ID <= ? AND ROW_ID > ? --分页行号范围

如果上面这个SQL速度还可以接受的话，看看下面这个拼接到一起后的完整SQL的执行耗时大概多少。

select to_char(p.update_date, 'yyyy-mm-dd hh24:mi:ss') as updateDate,
       case when p.ibps_flag is null then 0 else p.ibps_flag end as ibpsFlag,
       p.pos_code as posCode,
       p.grant_code as grantCode,
       '' as pdf,
       p.zh_title as title,
       p.sub_grant_name as category,
       psn.zh_name as userName,
       psn.psn_code as psnCode,
       0 as prjPsnFlag,
       0 as prjPsnFlag2,
       p.stat_year as statYear,
       (case when p.status='06' and pbe.APPROVAL_TYPE=1 then '宸叉壒鍑�' else cd.zh_cn_caption end) as status,
       cd.seq_no as seqNo,
       (case when p.status='06' and pbe.APPROVAL_TYPE=1 then '07' else p.status end)  as statusCode,
       to_char(p.submit_date, 'yyyy-mm-dd hh24:mi:ss') as submitDate,
       o.name as orgName,
       o.ORG_CODE as orgCode,
       p.prp_version as prpVersion,
       gse.end_date as deadlineDate,
       pp.prp_no as prpNo,
       o1.cname as ysOrgName,
       o2.cname as ywbmOrgName,
       o3.cname as ywksOrgName,
       pbe.approval_type as approvalType,
       (case when p.acceptance_type=1 then '鐢靛瓙鍙楃悊' 
             when p.acceptance_type=2 then '绾歌川鍙楃悊' 
             else '' 
         end) as acceptanceType,
       TMP_PAGE.ROW_ID
  from proposal_cached p
  JOIN (SELECT 主键字段1,主键字段2....,ROW_ID
          FROM (SELECT p.主键字段1,p.主键字段2....
                      ,ROW_NUMBER() OVER (ORDER BY p.updateDate desc,p.statYear desc) AS ROW_ID
                  FROM proposal_cached p
                  join grant_setting gs on gs.grant_code = p.grant_code
                 where p.grant_code <> 1000   
                   and p.status in ('00','01','02','03','05','06','08','09','98','10')
                   and gs.prp_type= '0'  
                   and p.org_code = ?
                   and p.prp_version like '%' || ? || '%' ESCAPE '\'
               )
         WHERE ROW_ID <= ? AND ROW_ID > ? --分页行号范围
       ) TMP_PAGE
    ON TMP_PAGE.主键字段1 = p.主键字段1 
   AND TMP_PAGE.主键字段2 = p.主键字段2
   AND 。。。
  left join organization o         on o.org_code = p.org_code
  left join GRANT_SCHEDULE gse     on gse.grant_code = p.grant_code and gse.MARK_YEAR = 1
  left join person psn             on psn.psn_code = p.psn_code
  left join proposal pp            on pp.pos_code = p.pos_code
  left join  const_dictionary cd   on  cd.code = p.status and cd.category='proposal_status'
  left join PROPOSAL_BU_EXTEND pbe on pbe.pos_code = p.pos_code
  left join ORG_OFFICE o1          on o1.OFF_ORG_CODE = pbe.PRELIMINARY_ORG
  left join ORG_OFFICE o2          on o2.OFF_ORG_CODE = pbe.office_code
  left join ORG_OFFICE o3          on o3.OFF_ORG_CODE = pbe.org_code
 ORDER BY TMP_PAGE.ROW_ID