本文共 6178 字,大约阅读时间需要 20 分钟。
今天工作中遇到了一个问题,需要对两个表进行关联查询,将结果插入到新表。困难在于两个表数据量都在三千多万。其中一张是车票信息表,按天分区,另一个是辅助信息,无分区。
由于之前的工作中遇到的数据量最大不过几万,对于这样比较大的数据量关联比较恐惧,不敢轻举妄动。只好求助。语句如下:
select d.bill_i,d.USR_ID,d.TKT,e.FPIN1,e.FPIN2,d.TKT_SALES_NMfrom tkt dleft 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_NMfrom tkt dleft 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; 这里有一个关于并行的知识点,参考自 :在创建表的时候可以使用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_NMfrom tkt dleft 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_bwhere 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_NMfrom tkt dleft 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_bwhere 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/