通過圖表簡化sql語句的表關聯

jeanron100發表於2015-03-10
SQL
在之前的博文中分享過一個執行了兩天的一條sql語句，走了兩個大表的掃描，導致執行時間很長，通過簡化sql做了不小的改進，今天我們來看看還可以做些什麼。
上次簡化後的語句如下：
with tmp_logical_date as (SELECT logical_date
                  FROM logical_date
                 WHERE logical_date_type = 'R'
                   AND expiration_date IS NULL)
SELECT trim(TO_CHAR(COUNT(distinct coll.entity_id), '000000000'))
  FROM cl1_coll_entity coll,
       table_bpm_step_inst bpm,
       table_bpm_step,
       ar1_account,
       csm_account,
       csm_pay_channel,
       customer,
       subscriber,
       ar1_billing_arrangement,
       ar1_address_name,
       charge_distribute,
       tmp_logical_date
 WHERE coll.entity_id(+) = csm_account.ban
   AND coll.proc_inst_id = bpm.parent2proc_inst
   AND bpm.step2step = table_bpm_step.objid
   AND bpm.status = 30
   AND coll.entity_id = ar1_account.account_id
   AND csm_account.ban = csm_pay_channel.ban
--   AND ar1_account.account_id = ar1_aged_trial_balance.account_id
   AND csm_account.customer_id = customer.customer_id
   AND csm_account.customer_id = subscriber.customer_id
   AND ar1_account.account_id = ar1_billing_arrangement.account_id
   AND ar1_account.account_id = ar1_address_name.account_id
   AND ar1_address_name.address_type = 'ACC'
  and exists(
  (SELECT 1
          FROM ar1_aged_trial_balance
         WHERE aged_type = 'D'
           AND group_type = 'B'
           AND status = 'EFF'
           AND TRUNC(tmp_logical_date.logical_date - due_date) >= 0
           AND account_id = coll.entity_id
         )
  )
   AND subscriber.trx_id = charge_distribute.trx_id
   AND subscriber.subscriber_no = charge_distribute.agreement_no
   AND charge_distribute.target_pcn = csm_pay_channel.pym_channel_no
   AND csm_account.ban = csm_pay_channel.ban
   AND EXISTS
 (SELECT null--cl1_treatment_activity.entity_id
          FROM cl1_treatment_activity, table_bpm_step_inst, table_bpm_step
         WHERE cl1_treatment_activity.step_id = table_bpm_step_inst.objid
           AND table_bpm_step_inst.step2step = table_bpm_step.objid
           AND table_bpm_step.NAME LIKE '%IVR%'
           AND table_bpm_step_inst.status = 65
           AND TO_DATE(TO_CHAR(cl1_treatment_activity.activity_date,
                               'YYYYMMDD'),
                       'YYYYMMDD') =tmp_logical_date.logical_date
           AND cl1_treatment_activity.entity_id = csm_account.ban)

單純來看這麼多表的關聯，著實是一個很棘手的事情，十多張大表關聯，從技術角度來看，oracle的分析確實還是很細緻的，根據資料量，走索引的地方都走了索引，預估的資料量也差不離。
但是想對這條語句做進一步的改進，單純調整執行計劃還是很有限制的。
我們來看看一個新的方法，首先我已經被這些表關聯弄暈了，我簡單整理了下面的圖表。這個圖表能夠很清楚的看到表連線的情況。

表的資料都是基於cl1_coll_entity，但是通過這個圖發現，重心似乎轉移了。感覺重心似乎是csm_account
我們來看看csm_account和cl1_coll_entity的關聯，使用了一個外連線，即對於csm_account中的關聯資料在cl1_coll_entity都存在。csm_account的資料是最全的。
coll.entity_id(+) = csm_account.ban
明白了這一點，我們來看看紅色框內的表連線，既然csm_account中的資料是完整的，類似一個全表掃描，那麼後面的一個環形表連線就是多餘的。因為方框中的表連線都是業務層面，是這些entity之間的完全對映。這些表中沒有額外的過濾條件。
可以通過一個簡單的例子來說明。我們建立兩個表csm_account,cl1_coll_entity
create table csm_account(id number);
insert into csm_account values(1);
insert into csm_account values(2);
insert into csm_account values(3);

create table cl1_coll_entity(id number);
insert into cl1_coll_entity values(1);

select coll.id from cl1_coll_entity coll,csm_account
where coll.id(+)=csm_account.id
        ID
----------
         1

3 rows selected.
select count(coll.id) from cl1_coll_entity coll,csm_account
where coll.id(+)=csm_account.id
COUNT(COLL.ID)
--------------
             1
1 row selected.
因為cl1_coll_entity中的資料是csm_account中的子集，所以後面csm_account的完全對映絲毫不會對cl1_coll_entity的資料有任何的影響。既然沒有任何的影響，就不需要保留它了。
同理標紅的ar1_billing_arrangement和ar1_account中的資料是多對一的對映。這個也是完全從業務層面保證。
簡化後的表連線情況如下：


可以看到原本14個表連線最後簡化為了8個表連線，簡化的幅度還是比較大的。
這種簡化思路可以在平時的調優中參考，從業務層面能夠完全保證的資料情況反覆關聯就顯得有些冗餘了。畢竟從技術層面我們無法得到更多的細節。
不管怎麼樣，都是為了簡化邏輯，減少資源的消耗。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1455102/，如需轉載，請註明出處，否則將追究法律責任。

相關文章