关于/*+ DRIVING_SITE(tableName)*/的优化（SQL效率）

时间：2021-02-17 04:50:37

关于/+ DRIVING_SITE(tableName)/的优化（SQL效率）

今天遇到一个问题，一个SQL通过DB_LINK链接查询多表，效率相当慢，就上网搜索了一下解决办法，发现通过DRIVING_SITE(tableName)的使用，可以数量级的提升SQL效率，使用方法示例如下：

原 SQL语句

SELECT A.ITEM_ID 主键ID,
       B.ORGAN_CODE 支公司代码,
       H.CHANNLE_CODE 营业区代码,
       ...
       ...
       ...
  FROM ebao.T_PRODUCT_COMMISION@cms_ebao A,
       ebao.T_COMPANY_ORGAN@cms_ebao B,
       ebao.T_AGENT@cms_ebao C,
       ebao.T_CHANNEL_ORG@cms_ebao E,
       ebao.T_CHANNEL_ORG@cms_ebao G,
       ebao.T_CHANNEL_ORG@cms_ebao H,
       ebao.T_CONTRACT_MASTER@cms_ebao I,
       ebao.T_PRODUCT_LIFE@cms_ebao J,
       ebao.T_CONTRACT_PRODUCT@cms_ebao K,
       ebao.T_PREM_ARAP@cms_ebao L,
       ebao.T_POLICY_HOLDER@cms_ebao M,
       ebao.T_CUSTOMER@cms_ebao N,
       ebao.T_BENEFIT_INSURED@cms_ebao V,
       ebao.T_INSURED_LIST@cms_ebao O,
       ebao.T_CUSTOMER@cms_ebao P,
       ebao.T_POLICY_ACKNOWLEDGEMENT@cms_ebao Q,
       MADB01.T_INTEG_NBU_DATA_FETCH_CC@cms_27 R,
       ebao.T_POLICY_CHANGE@cms_ebao T
 WHERE 1 = 1
   AND A.ORGAN_ID = B.ORGAN_ID(+)
   AND A.AGENT_ID = C.AGENT_ID(+)
   AND A.CHANNEL_ORG_ID = E.CHANNEL_ID(+)
   AND E.PARENT_ID = G.CHANNEL_ID(+)
   AND G.PARENT_ID = H.CHANNEL_ID(+)
   AND A.POLICY_ID = I.POLICY_ID(+)
   AND A.PRODUCT_ID = J.PRODUCT_ID(+)
   AND A.BENEFIT_ITEM_ID = K.ITEM_ID(+)
   AND A.LIST_ID = L.LIST_ID(+)
   AND A.POLICY_ID = M.POLICY_ID(+)
   AND M.PARTY_ID = N.CUSTOMER_ID(+)
   AND A.BENEFIT_ITEM_ID = V.ITEM_ID(+)
   AND V.INSURED_ID = O.LIST_ID(+)
   AND O.PARTY_ID = P.CUSTOMER_ID(+)
   AND C.AGENT_CATE = 'D'
and A.comm_status = 2
   AND A.POLICY_ID = Q.POLICY_ID(+)
   AND A.POLICY_ID = R.DATA_ID(+)
   AND L.policy_chg_id = T.policy_chg_id
and trunc(A.happen_time) between date '2017-07-01' and date'2017-07-01'

上边SQL执行起来需要时间为：36.6 seconds;

修改后如下

SELECT /*+ DRIVING_SITE(A)*/
       A.ITEM_ID 主键ID,
       B.ORGAN_CODE 支公司代码,
       H.CHANNLE_CODE 营业区代码,
       ...
  FROM ebao.T_PRODUCT_COMMISION@cms_ebao A,
       ebao.T_COMPANY_ORGAN@cms_ebao B,
       ebao.T_AGENT@cms_ebao C,
       ebao.T_CHANNEL_ORG@cms_ebao E,
        .
        .
        .

执行时间为： 7.641 seconds

其中/+ DRIVING_SITE(tableName)/是可以加多个远程表！

粗略的总结了一下，欢迎补充。。。。。
标签：

相关文章