思考-两个大表的关联.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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-753505/