思考-兩個大表的關聯.txt

今天工作中遇到了一個問題，需要對兩個表進行關聯查詢，將結果插入到新表。困難在於兩個表資料量都在三千多萬。其中一張是車票資訊表，按天分割槽，另一個是輔助資訊，無分割槽。
由於之前的工作中遇到的資料量最大不過幾萬，對於這樣比較大的資料量關聯比較恐懼，不敢輕舉妄動。只好求助。

語句如下：
select
  d.bill_i,d.USR_ID,d.TKT,e.FPIN1,e.FPIN2,d.TKT_SALES_NM
from tkt d
left join (select a.sales_b, a.fpin fpin1, c.fpin fpin2
            from pbox a
            left join (select b.sales_b, b.fpin
                       from pbox b
                       where b.pbox_seq = 2) c
            on a.sales_b =c.sales_b
            where a.tkt_seq = 1) e
on d.tkt_sales_nm =e.sales_b;

注：
當前tkt表上按照date分割槽，每天一個分割槽，每個分割槽一百多萬資料，分割槽鍵是bill_i；
pbox表中sales_b、pbox_seq、tkt_seq組成聯合索引，其中pbox_seq、tkt_seq都是引數，絕大多數的取值都是1，只有極少數不是。

自己的想法主要有：
（1）是否可以使用並行？
（2）能否將語句拆分，先將分割槽表資料插入，再對fpin1、fpin2進行update操作？
（3）語句的結構是否合理？其中a、b兩個表的連線實際是同一個表由於需要不同欄位而做外連線操作，這種巢狀的left join是否影響效能？
（4）加索引是否能讓執行速度更快？
（5）在遇到類似問題，如何考慮？
（6）執行計劃一樣，結果一定一樣嗎？

***************************************************************************************************************

（1）首先嚐試了以下並行，在語句上新增hint：
select
  /*+parallel(4)*/d.bill_i,d.USR_ID,d.TKT,e.FPIN1,e.FPIN2,d.TKT_SALES_NM
from tkt d
left join (select a.sales_b, a.fpin fpin1, c.fpin fpin2
            from pbox a
            left join (select b.sales_b, b.fpin
                       from pbox b
                       where b.pbox_seq = 2) c
            on a.sales_b =c.sales_b
            where a.tkt_seq = 1) e
on d.tkt_sales_nm =e.sales_b;

這裡有一個關於並行的知識點，參考自http://space.itpub.net/26686207 ：
在建立表的時候可以使用create table t_name（column_name datatype） parallel n；的句法執行表的並行度，這樣在以後的查詢中除非特別指明no_hint，否則都會用到並行；
修改表的並行度，alter table t_name parallel n|noparallel；
如果建表時未指定，需要在查詢中用parallel（[table_name] n）指定某個表的查詢並行度。
如果只需要在當前會話內使用並行，其他會話不需要，可以使用語句：alter session force parallel query parallel n；

經過試驗，並行並不一定會使效能提升，反而有時會使效能更差。詳見另外一個例子。在這裡，並行並不能顯著改善效能。

第一個疑問解決了

***************************************************************************************************************

（2）分步操作，當我們插入資料的時候肯定是速度相對較快的，但當我們再對資料更新時呢？
毫無疑問，新表中插入的資料與tkt表數量相當，因此也需要一次全表掃描，如果不指定對哪一段資料進行更新，還是需要兩個表的連線，pbox的掃描也很難省略。

***************************************************************************************************************

（3）left join巢狀的問題，可以描述為：

select xxxx from a left join (select xxxx from b left join c on …… where ……) on …… where ……
是否可以改寫為
select xxxxx from a left join b on ……  left join c on …… where …… ……

首先，將上面的語句由巢狀改為非巢狀的形式後，執行計劃是完全相同的。這時候的結果一樣。
經過一個小表上的實驗，修改寫法可能使執行計劃發生改變，而且巢狀不一定比非巢狀的成本高。
對於一個表與本身做外連線後與其他表做外連線，兩種寫法應該是等效的，由於外連線只是增加了空欄位，因此早加和晚加是效果一樣的。

當a與c中的欄位作為where條件時，會有不同的效果。有些left join的巢狀修改可能並不容易的。

***************************************************************************************************************

（4）兩個表的資料量都非常大，比較快的連線方式就是hash join，用不到索引；
如果是其中一個表的資料可以通過索引去除很多，即資料量是一個表大，另一個表小，使用索引+nest loop join可能會比較好。

***************************************************************************************************************

（5）以上的問題首先從技術方面進行考慮，再從業務方面分析。對於這種兩個大表連線的問題，其實可以首先從業務上分析一下。
比如，為什麼一個幾千萬資料的表沒有分割槽？怎麼能提高這部分資料的查詢效率？

合理的設計會讓以後的工作更加順利，但對於遷移來說，由於很多資料缺失是平時非常少用的，可能定期清除，建立分割槽管理確實沒有必要；
但是考慮到功能和穩定性，遷移時又必須將這些資料倒出來，尤其是還需要做一定的結構調整，就顯得比較困難了。

***************************************************************************************************************

（6）首先，在檢視不同欄位時，執行計劃可能一樣。執行計劃反映了查詢資料時選擇的路徑、評估或實際檢索的行數、成本資訊。
如果執行計劃一樣，返回的結果欄位相同，資料應該是一樣的。

 

***************************************************************************************************************
***************************************************************************************************************
以下是該問題的結果：

整理語句後，得到的執行計劃如下：

SQL> select
  d.bill_i,d.USR_ID,d.TKT,e.FPIN1,e.FPIN2,d.TKT_SALES_NM
from tkt d
left join (select a.sales_b, a.fpin fpin1, c.fpin fpin2
            from pbox a
            left join (select b.sales_b, b.fpin
                       from pbox b
                       where b.pbox_seq = 2) c
            on a.sales_b =c.sales_b
            where a.tkt_seq = 1) e
on d.tkt_sales_nm =e.sales_b
where d.bill_i=20120601;

已選擇1190746行。

已用時間:  00: 12: 37.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1885333 Card=1281616 Bytes=631836688)
   1    0   HASH JOIN (OUTER) (Cost=1885333 Card=1281616 Bytes=631836688)
   2    1     PARTITION LIST (ALL) (Cost=1214968 Card=1280892 Bytes=539255532)
   3    2       TABLE ACCESS (FULL) OF 'TKT' (TABLE) (Cost=1214968 Card=1280892 Bytes=539255532)
   4    1     VIEW (Cost=504708 Card=34971129 Bytes=2517921288)
   5    4       HASH JOIN (RIGHT OUTER) (Cost=504708 Card=34971129 Bytes=4686131286)
   6    5         TABLE ACCESS (FULL) OF 'PBOX' (TABLE) (Cost=153988 Card=17485565 Bytes=1171532855)
   7    5         TABLE ACCESS (FULL) OF 'PBOX' (TABLE) (Cost=154063 Card=34971129 Bytes=2343065643)

Statistics
----------------------------------------------------------
        611  recursive calls
          0  db block gets
    5537289  consistent gets
    5599406  physical reads
          0  redo size
  332433587  bytes sent via SQL*Net to client
     555959  bytes received via SQL*Net from client
      79385  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1190746  rows processed

由於採用了分割槽鍵作為查詢條件，oracle只對bill_i欄位對應的分割槽進行分割槽內的全掃描，
在pbox表中，雖然我們使用的是索引的前導列，有機會用到索引，但hash join的效率相對於nest_loop_join+index高，因此未用索引（這句是猜測，通過hint可以確認）
雖然得到結果花費了十多分鐘，相對於不使用分割槽鍵還是有很大效能提高的。

select /*+use_nl(tkt e)*/
  d.bill_i,d.USR_ID,d.TKT,e.FPIN1,e.FPIN2,d.TKT_SALES_NM
from tkt d
left join (select a.sales_b, a.fpin fpin1, c.fpin fpin2
            from pbox a
            left join (select b.sales_b, b.fpin
                       from pbox b
                       where b.pbox_seq = 2) c
            on a.sales_b =c.sales_b
            where a.tkt_seq = 1) e
on d.tkt_sales_nm =e.sales_b
where d.bill_i=20120601;

***********************************************************************************************************************

該語句最後需要通過dblink遠端執行，執行計劃及統計資訊為：

……
已選擇1190746行。

已用時間:  00: 10: 42.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=0)
   1    0   REMOTE* (REMOTE)                                           TO_DB

   1 SERIAL_FROM_REMOTE            EXPLAIN PLAN SET STATEMENT_ID='PLUS5021658'
                                   INTO PLAN_TABLE@! FOR SELECT "A2"."M

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
  328913796  bytes sent via SQL*Net to client
     555959  bytes received via SQL*Net from client
      79385  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1190746  rows processed

由於在遠端執行，無法獲得詳細的執行計劃，只得到了語句的id，但後面的“INTO PLAN_TABLE@! FOR SELECT "A2"."M”是什麼意思呢？

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