sql - 哪个查询执行得更快

Question

以下是查询 CTE 和 NFSE 数据的两个选项。请给个建议 ！

许多地方可能必须使用类似的方法。

如果有更好的选择，我希望您验证它们并提出建议。

选项 1：使用所有必要的连接进行查询CTEUNION 使用所有必要的连接进行NFSe查询

SELECT DISTINCT 'CTE' as docTypeID
   , cte.isqn_mstr_cd as ctePk
   , cte.ct_e_cd
   , cter.stat_desc
   , awb.CREATE_DT
   , awb.AWB_NBR
   , awb.SHPR_NM
   , awbs.DEST_LOC_CD
   , nfe.NT_FSCL_CD
   , nfe.FSCL_DOC_NBR
FROM cte_identity_master cte
  INNER JOIN cte_response_detail cter ON (cte.isqn_mstr_cd = cter.isqn_mstr_cd)
  LEFT JOIN match_ref_awb mawb ON (cte.isqn_ref_cd = mawb.isqn_mstr_cd)
  LEFT JOIN awb_cust_master awb ON (mawb.awb_nbr = awb.awb_nbr)
  LEFT JOIN awb_shipment_detail awbs ON (awb.awb_nbr = awbs.awb_nbr)
  LEFT JOIN match_ref_nfe mnfe ON (cte.isqn_ref_cd = mnfe.isqn_mstr_cd)
  LEFT JOIN nfe_identity_master nfe ON (mnfe.nt_fscl_cd = nfe.nt_fscl_cd)
UNION
SELECT DISTINCT 'NFSE' as docTypeID
   , nfse.isqn_mstr_cd as nfsePk
   , nfse.rp_s_id
   , nfser.stat_desc
   , awb.CREATE_DT
   , awb.AWB_NBR
   , awb.SHPR_NM
   , awbs.DEST_LOC_CD
   , nfe.NT_FSCL_CD
   , nfe.FSCL_DOC_NBR
FROM nfse_request_detail nfse
  INNER JOIN nfse_response_detail nfser ON (nfse.isqn_mstr_cd = nfser.isqn_mstr_cd)
  LEFT JOIN match_ref_awb mawb ON (nfse.isqn_ref_cd = mawb.isqn_mstr_cd)
  LEFT JOIN awb_cust_master awb ON (mawb.awb_nbr = awb.awb_nbr)
  LEFT JOIN awb_shipment_detail awbs ON (awb.awb_nbr = awbs.awb_nbr)
  LEFT JOIN match_ref_nfe mnfe ON (nfse.isqn_ref_cd = mnfe.isqn_mstr_cd)
  LEFT JOIN nfe_identity_master nfe ON (mnfe.nt_fscl_cd = nfe.nt_fscl_cd)
;

选项 2：首先使用 and 的CTe并集NFSe，然后将连接应用于其他表

SELECT ctnf.* 
   , awb.CREATE_DT
   , awb.AWB_NBR
   , awb.SHPR_NM
   , awbs.DEST_LOC_CD
   , nfe.NT_FSCL_CD
   , nfe.FSCL_DOC_NBR
FROM (
SELECT DISTINCT 'CTE' as docTypeID
   , cte.isqn_mstr_cd as docPk
   , cte.ct_e_cd as docNbr
   , cter.stat_desc as docStat
   , cte.isqn_ref_cd as matchRef
FROM cte_identity_master cte
  INNER JOIN cte_response_detail cter ON (cte.isqn_mstr_cd = cter.isqn_mstr_cd)
UNION
SELECT DISTINCT 'NFSE' as docTypeID
   , nfse.isqn_mstr_cd as nfsePk
   , nfse.rp_s_id
   , nfser.stat_desc
   , nfse.isqn_ref_cd
FROM nfse_request_detail nfse
  INNER JOIN nfse_response_detail nfser ON (nfse.isqn_mstr_cd = nfser.isqn_mstr_cd)
) ctnf
  LEFT JOIN match_ref_awb mawb ON (ctnf.matchRef = mawb.isqn_mstr_cd)
  LEFT JOIN awb_cust_master awb ON (mawb.awb_nbr = awb.awb_nbr)
  LEFT JOIN awb_shipment_detail awbs ON (awb.awb_nbr = awbs.awb_nbr)
  LEFT JOIN match_ref_nfe mnfe ON (ctnf.matchRef = mnfe.isqn_mstr_cd)
  LEFT JOIN nfe_identity_master nfe ON (mnfe.nt_fscl_cd = nfe.nt_fscl_cd)
        ;

上述两种方法都将具有以下Where条款：

WHERE lower(cte.sttn_cd) = lower(:stationId) 
and (:documentType is null or lower(:documentType) = 'cte') 
and (:shipperName is null or lower(awb.shipperNm) like lower(concat(concat('%',:shipperName),'%'))) 
  and (:awbCreated is null or to_char(awb.createDt, 'MM-DD-YYYY') = :awbCreated) 
  and (:awbNumber is null or m2.awbNbr like concat(concat('%',:awbNumber),'%')) 
  and (:serviceType = 0 or awbs.baseServiceCd = :serviceType) 
  and (:commitmentDate is null or awbs.commitmentDate = :commitmentDate) 
  and (:ursa is null or lower(awbs.ursaCd) like lower(concat(concat('%',:ursa),'%'))) 
  and (:destLocationId is null or lower(awbs.destLocCd) like lower(concat(concat('%',:destLocationId),'%'))) 
  and (:nfeNumber is null or nfe.fiscalDocumentNumber like concat(concat('%',:nfeNumber),'%')) 

请建议 - 这两种获取数据的方法的输出，哪一种更好地在JavaEnd 修复以检索数据。

任何帮助将不胜感激。还建议除了这两个之外是否还有其他更好的查询！

标签： sqloracledatabase-performancequery-performance