69 积分	0 好友	13 主题

发消息

大数据量的两或三个表连接带来的疑惑？

1^#

发表于 2012-7-12 00:45:37 | 查看: 2787| 回复: 0

从今在一家彩票公司做统计，做个存储过程里面涉及多个表LEFT JOIN 有点复杂
8:34 跑到01：24 约6个小时产生61万4009行
如下：

Insert Into T_WAP_FINANCE_ANALYZ
Select *
From
(
Select X.*, e.f_Regchannel
From
(
select udf_weekofyear( v_startday + (level-1), 'yyyyiw') f_yearweek,
To_char( v_startday + (level-1), 'day') f_week,
(v_startday + (level-1)) As f_day
from dual
connect by level < v_enddate+1 - v_startday
)X,
(Select F_REGCHANNEL From T_base_wap_REGChannel g Where g.f_Regchannel <> '500wan' ) e
Order By f_yearweek,f_week
) a
Left Join
(
Select
udf_weekofyear(F_Date,'yyyyiw') f_yearweek,to_char(F_Date,'day') f_week,wr.f_Regchannel,
Count(Distinct fu.f_username) As f_fillUserNum,
Count(fu.f_Username) As f_fillNum,
Sum(F_FillMoney) As F_FillMoney
From T_Base_User_Fill_Burse fu
Inner Join T_Base_Userinfo ui On fu.f_Username = ui.f_Username
Inner Join T_base_wap_RegChannel wr On ui.f_Regchannel = wr.f_Regchannel
Where wr.f_Regchannel != '500wan'
And F_Date Between v_startday And v_enddate
And F_FillSuccess = 1
Group By udf_weekofyear(F_Date,'yyyyiw'),to_char(F_Date,'day'),f_Regchannel
)
.....

复制代码

下面还 left join c d e 子查询表。具体在这http://blog.csdn.net/zengmuansha/article/details/4601410
在这里不评价和讨论此语句问题可能走了索引，还有采用了NESTED LOOP连接，SORT ORDER BY 用了临时表空间排序。

在新一家公司继续做统计报表工作！新公司的风格是采用物理表保持中间计算的数据，然后再进行表的关联操作，具体原因DBA未说明过，也解释不太清楚。
另外就是采用了游标批量处理

open cursor ;
loop
fetch cursor bulk collect into
arry
limited 1000；
forall i in 1.. arry.count
insert into table_a (....) values(....)
commit;
end loop;

复制代码

从今的彩票公司的DBA说过 ORACLE是块操作，组长建议过采用物理表法，也演示过大数据量的插入一个表用一条语句写完不如根据数据特性按月分批成12个语句，也就是一次插一个月的数据。12个月串行插入，比一次性插入1年的数据快。彩票公司统计服务器是4个CPU 32G内存 2TB硬盘。

新公司得到经验当一个大表约15G大，有一个字符字段800长。对它做统计按省份，按内容，统计条数。
即

insert into table_a (province,say_content,number)
select /*+full(h)*/
Province,say_content,count(*)
from table_content_hist h
group by Province,say_content
having count(*)>1000

复制代码

5千万数据 15G只要 5分钟结束了！

本人采用一贯的风格物理表化，FETCH BUILK 到物理然后读回来做GROUP BY. 读写花费55分钟，统计花了16分钟。

而另外个过程对同样的表和数据及环境先对表做GROUP BY 然后插入到物理表。再和其他表获得数据做关联统计。
运行时间却很快。尤其是table_content_hist 这步骤。

select /*+full(h)*/
province,count(*) t
from table_content_hist h
group by province

复制代码

现在公司的DBA也建议采用物理表化进行两表关联。

另外个现公司追认的优秀员工解释到两表关联的时侯之前要把块中的行读取出来关联。
1 那么要把块从数据文件读到内存中或许通过全表扫描，或许通过索引定位。
2 有可能要进行条件过滤，不管如何总之都是块操作，即块中还是含不必要的列和不必要的行。
3 如果这样进行关联的话，所有读取的块还是很多。
4 假如在关联之前把所要的行，所要的列写到物理表上，那么将得到更少块，每个块包含的全是需要的行和列。
5 这样再来做关联速度是很快的，虽然牺牲了一些物理IO

他的说法是有点道理，有验证了彩票公司DBA说的ORACLE采用块操作。

假如步骤有：1 读取数据文件的块，2 过滤，3 做关联操作，4返回给母查询，5 母查询做统计，6 客户FETCH结果集
那我疑惑
1  读取块到内存，它并没有更改，当读完它后，读下一个块，那这个块应该可以得到释放机会，只要touch减少。不受语句是否执行完的影响？

2 过滤当读到一个块时发现符号条件，那是把行提取出来放在某个地方，还是把块保留下来？

3 关联操作，如果前2个步骤可以读一个块，过滤一个块的串行干，那关联必须等到数据全部过滤出来，否则如何关联呢？那就得找个地方存放中间这些数据。
  据了解有可能是PGA工作区的HASH_JOIN_AREA 和临时表空间。

4 关联操作的子查询返回的结果集很有可能很多行，放在哪里？ PGA的私有SQL区还是游标区，工作区应该不可能，临时表空间很有可能。

5 应该是在 select 这步过滤列

6 客户提取操作每提取100行，有1万行结果集。那么这1万行也将放在临时表空间中，不太可能放在PGA吧！

关于OPEN CURSOR 和  FETCH CURSOR BUILK COLLECT INTO LIMMITE 疑惑

7  版主在这里说过 http://t.askmaclean.com/thread-1370-1-1.html

  OPEN只是解析并记住块的SCN号，FETCH负责提取数据。
  那么FETCH 还要完成SQL语句的执行计划的每个步骤，包含过滤，包含连接，包含GROUP BY。
那么每FETCH一次就要EXECUTE一次。
而采用了BULK COLLECT 只要循环一次，执行一次就能把全部数据装载到数组中，
  而普通游标循环一次，提取一行就要执行一次SQL语句。这样就造成PL/SQL和SQL引撑大量切换。

  而采用了limite 的bulk 也就是每次只能提取那么多行的数据，比如上面说的1万行，limite 1000 这要循环10次
  那么也得执行10 FETCH cursor bulk collecnt into 操作，也就是进行10次引撑切换、

而LIMITE 只是避免数据太多填充到数组中把PGA的私有SQL区给爆满

  我这里理解对吗？哪几个理解错了？

8 在大数据量的表插入时一条语句插入全部数据还是分成12个语句串插呢？
从理论上说1次性插如和多次性总和起来的插入  其物理读IO 是一样的，物理写IO是一样的。需要一样多的UNDO块，产生一样的REDO量。

9 存储过程中的SQL语句除了动态SQL外好像在V$SQL,V$SQLAREA,V$SQLTEXT里无法找到！
  是不是PGA里的私有SQL区是不能被查询的？

谢谢！！

[ 本帖最后由 zengmuansha 于 2012-7-12 00:56 编辑 ]

分享0

收藏0 回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册