求助：大数据量统计提高效率的方法

Question

我现在有2张大数据量表：表A有6万条数据，表B有20万条数据， 我做了一个存储过程，用游标遍历了表A，然后把数据一条条取出来，到表B中统计匹配的数据，每一条需要统计10种数据，然后再更新到表A上。可是这样效率非常低，存储过程执行了10多个小时还没有结束。  请高手指点下，有没有提高效率的方法呢？

Answer 1

汗~ 就是把表A的数据依次取出，到表B中统计，然后把结果插回到表A对应字段， 就这么一个循环，我执行了10多个小时还没结束，谁能告诉下为什么吗

Answer 2

如果A表与B表的数据表结构是一样的，可以采用SELECT * FROM B表  MINUS SELECT * AFROM 表 的方法，把结果放在一张临时表中，然后从临时表中取记录对A表进行操作，临时表中的记录在A表中有的进行更新，没有的可以不操作。查询时最好采用CUNT（*）的方式！注意最好表中有索引！

Answer 3

B表建适当的索引。此外，看看是否必须要用cursor，是否可以改为用一个update语句直接更新A表。

Answer 4

求一个数据统计的解决方案由于项目需要做一个统计，我按普通的游标循环做统计一天都没统计结束，在这里请高手给一个解决方案，下面是表结构：[表A有10万条数据，表B有100万条数据]，表A结构是树型目录：
  SORTID   VARCHAR2(20) not null,
  SORTNAME VARCHAR2(100),
  PARENT   VARCHAR2(20),
  ROOT     VARCHAR2(20),
  TYPE1    NUMBER,
  TYPE2   NUMBER,
  TYPE3    NUMBER,
…………………………
   TYPE15 NUMBER表B结构
  ID      NUMBER(10) not null,
  SORTID      VARCHAR2(20)  not null,
  CTYPE    CHAR(10),
  CTIME   date,
  …………………………
表A.sortid = 表B.sortid;表A.type1~15 = 表B.ctype   ；从表A取出SORTID 到表B轮循一遍，然后算出每种type的数量，更新到表A对应的字段中
我原来的方法是[用存储过程定时统计]：
update 表A set type1 = (select count(*) from 表B where substr(sortid,1,length('||cur.sortid||')) = '||cur.sortid||') where sortid = '||cur.sortid||'

Answer 5

既然“表A.sortid = 表B.sortid”，为什么还要用substr(sortid,1,length(...))？可以直接写sortid=...

Answer 6

表A中统计的数是总值，表A是树型结构，我用substr(sortid,1,length(...))就可以直接把当前sortid和在sortid节点以下的所有值的总值都算不出，不用到时候再从最低节点一个个节点加回来啊，比如sortid = a1b1,parent = a1,root=a1;sortid=a1b1c1,parent = a1b1,root =a1;
这样我用substr(a1,1,2) 不是把a1~a1b1c1的总数算出来了吗？

Answer 7

你是用的动态SQL执行的update语句吗？是的话，这样写
'update 表A set type1 = (select count(*) from 表B where sortid like '''||cur.sortid||'%'') where sortid = '''||cur.sortid||''''
如果不是，就这样写
update 表A set type1 = (select count(*) from 表B where sortid like cur.sortid||'%') where sortid = cur.sortid此外，如果是动态SQL，最好是用绑定参数方式，这样可以减少SQL语句分析的开销，如
s := 'update 表A set type1 = (select count(*) from 表B where sortid like :v1) where sortid = :v2';
...
execute immediate s using cur.sortid||'%',cur.sortid;

Answer 8

bobfang 的说法很好 最好是绑定变量，因为在重复执行的过程中，解析语句花费的时间是惊人的（比更新数值会多几十倍时间）

Answer 9

哦，明白，谢谢哦，有点不明白sortid like '''||cur.sortid||'%''中的  %是啥意思呢？ 如果只是like的话，可能匹配结果不怎么对哦，比如sortid =1 去匹配 1和11和12……

Answer 10

1、不明白你说的是啥，但有一点可以肯定，做适当处理的话，你那点数据肯定可以在1分钟内完成
2、看你用了substr，不知道你是怎么建索引的，一般的索引在substr下是无效的，按照你的说法，估计你需要建N个函数索引。不过我觉得肯定还有其他方法
3、我也做过类似的统计，数据量和你的查不过，一般在几十秒可以出来结果，
create or replace procedure p_count_rev_invalid is
cursor c1 is select seg_no_min,seg_no_max,cnt from prov_segno for update of cnt;
t_c1 c1%rowtype;
l_cnt int;
begin
  open c1;
  loop
  l_cnt:=0;
  fetch c1 into t_c1;
  exit when c1%notfound;
       select count(1) into l_cnt from mdn 
       where mdn>=t_c1.seg_no_min and mdn<=t_c1.seg_no_max;
       update prov_segno set cnt=l_cnt where current of c1;
 end loop;
 close c1;
 commit;
 exception
          when others then
          rollback;
end p_count_rev_invalid;其中在MDN表的MDN字段建有索引

Answer 11

建议你跟踪下你的语句，有时在SQL执行计划中观察你的SQL虽然用了索引，但是正常执行时，由于数据量的问题，Oracle会认为全表扫描会比用索引快，所以就全表扫描了，你不如加上强制索引试试。update /*+index(表A 索引名字) index(表B 索引名字)*/ 表A set type1 = (select count(*) from 表B where substr(sortid,1,length('||cur.sortid||')) = '||cur.sortid||') where sortid = '||cur.sortid||'

Answer 12

給lz个建议，不一定可行，但可以试一下：）首先，在B表中建一个复合索引SORTID + CTYPE
然后写一个存储过程，
GetSortIDType(sortid in varchar, type1count out number, ..., type15count out number)
用它来获取某个sortid对应的所有type的数量，对于每一个sortid而言，这个存储过程的执行速度应该能接受。然后从A表中逐一取出sortid，然后调用这个存储过程，最后插入。遍历A表的效率应该是没有办法提高的

Answer 13

如果匹配的数据比较多,最好不要这样,把两个表做hash join,效率会提高比较多.

Answer 14

有个奇怪的问题，substr是不能用索引，但个条件语句，比like 'x%'慢很多，但是用了组合条件语句后(加了一个and aa>0)，substr反而比 like快？ 不是很明白为什么了。

Answer 15

substr是不能用索引 ==>你要创建函数索引
like 'x%'          ==>是可以用到索引的用了组合条件语句后(加了一个and aa>0)，substr反而比 like快？
----------------------------------------------------
可能是aa>0过滤掉很多记录,很就会有很少的记录返回,在这个时候全表扫描比走索引快.

Answer 16

substr是不能用索引，比like 'x%'慢很多
你可以查看执行计划，你加了查询条件后，查询结果比不加查询条件的时候少了很多。oracle实用索引有一个计算方法，当查询的结果集占到总数据量的一定百分比后，他就将直接进行全表检索而不是索引检索。当然，你也可以强制要求进行索引检索（但不建议这么做）加了and aa>0后，会快一些，道理和以上一样，详细的可以参阅
http://matrix.org.cn/thread.shtml?topicId=26948&forumId=36

Answer 17

有条语句 select * from table where ak like 'aa%' and ad > 'j'  为什么我把 and ad > 'j' 的条件去掉后，反而速度快很多？ 加了 后面的条件，速度慢了近1/2？

Answer 18

你有建ak和ad的复合索引吗，如果没有，慢是必然的。你想，它先查出ak like 'aa%'的记录，然后再在这里面搜索ad>'j'的记录，速度能不慢吗？

Answer 19

UPDATE 表A a
SET type1 = (
SELECT COUNT(*) 
FROM 表B b
WHERE ctype='type1'
AND a.sortid=b.sortid)
WHERE a.sortid IN (SELECT sortid FROM 表B)
GO
UPDATE 表A a
SET type2 = (
SELECT COUNT(*) 
FROM 表B b
WHERE ctype='type2'
AND a.sortid=b.sortid)
WHERE a.sortid IN (SELECT sortid FROM 表B)
GO
------------------------------
UPDATE 表A a
SET type15 = (
SELECT COUNT(*) 
FROM 表B b
WHERE ctype='type15'
AND a.sortid=b.sortid)
WHERE a.sortid IN (SELECT sortid FROM 表B)
GO

Answer 20

create index idx_table_aj_aa 
  on table (aj,aa);

Answer 21

索引是一种很奇妙的东西,它可以让执行效率提高,也可以让执行效率降低,建议你去DOWN点资料看看

Answer 22

不用搞这么复杂.再2个表的sortid上建索引,并做分析
基本思路如下:
update tab_A set (type1,..type15)=(select t1.type1,..t15.type15 from (
(select SORTID ,count(*) as type1 from tab_b 
 where ctype=type1
 group by sortid) t1,
...
(select SORTID ,count(*) as type15 from tab_b 
  where ctype=type15
  group by sortid) t15)
)
where tab_A.sortid=t1.sortid
...
and  tab_A.sortid=t15.sortid