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

本文链接：https://blog.youkuaiyun.com/qq_33570053/article/details/76595260

本文介绍了一种通过使用DRIVING_SITE指示符来优化跨站点SQL查询效率的方法。通过案例展示，原本耗时36.6秒的查询，在应用该技巧后仅需7.641秒完成，提升了查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关于/+ 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)/是可以加多个远程表！

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