Question

Oracle中循环遍历从一个表中查出30W数据,并插入另一个表.我用了4个多小时，求问怎样优化？

cursor all_station is select station_name from gj_station;
begin open all_station ;
loop fetch all_station into start_station; exit when all_station%notfound; for end_station in (select station_name from gj_station) loop insert into GJ_ZEROTRANS (id,start_station,line,end_station,stop_count) (select zero_id.nextval,a1.station_name, a1.line_name, a2.station_name, (a2.position-a1.position) stopcount from ********* where a1.id = a2.id and a1.position < a2.position); commit; end loop;end loop;

Answer 1

cursor all_station is 
  select station_name from gj_station;
begin 
    open all_station ;
    loop 
        fetch all_station into start_station; 
        exit when all_station%notfound; 
    
        for end_station in (select station_name from gj_station) loop   
            insert into GJ_ZEROTRANS (id,start_station,line,end_station,stop_count)   
                (select zero_id.nextval,a1.station_name, a1.line_name, a2.station_name, (a2.position-a1.position) stopcount    
                from  *********    
                where a1.id = a2.id    and a1.position < a2.position); 
            commit; 
        end loop;
    end loop;

你的存储过程， 格式化一下以后

显示的是， 你是  30W * 30W 的级别来处理的啊

外面一个 

loop 

        fetch all_station into start_station; 

        exit when all_station%notfound; 

30W

里面一个

for end_station in (select station_name from gj_station) loop   

30W

也就是你执行了那个  30W * 30W  次  insert into GJ_ZEROTRANS  ... 语句啊...

追问

是这样的，这两层循环一共可以循环出30W左右数据。不是一层循环就30W。

追答

select COUNT( station_name ) from gj_station
这个表多少行记录

然后执行一个
select
count(*)
from *********
where a1.id = a2.id

看看行数多少， 耗时多少。

追问

第一个是这样的。

 

第二个查询是带查询条件的，查询条件是从第一层循环中得到。

追答

外面的表有 2114 行数据。
循环套2次， 是 执行 2114 * 2114 = 4468996 次 里面的 sql 语句。

耗时 4 小时。
总秒数 = 4 * 60 * 60 = 14400 秒。
中间那个 insert 平均耗时 = 14400 / 4468996 = 0.0032222 秒

我又仔细看了看你内部的逻辑。
感觉完全可以 外面的 2 次 loop 不要。
内部的 2个查询条件不要。

直接一次性的
insert ... select ... from ( (第一个子查询), (第二个子查询) )

如果这个查询更新数据太多， 导致异常的话。

可以尝试把 循环， 修改成 1个 LOOP 的
也就是一个

for linee in (select line_id from gj_line ) loop
来处理。

只循环一次。

然后
insert ... select ... from ( (第一个子查询 增加一个 line_id 条件 ), (第二个子查询 增加一个 line_id 条件 ) )

追问

你说的有道理，我试一下，一会儿把结果告诉你。

Answer 2

1.为什么要用两个循环？第二个for循环的意义何在？
2.你定义了游标，为什么我在你循环往另外一个表插入数据的时候跟游标没半毛钱关系？
3.插入一笔数据就commit一次，commit也要花时间的大哥。

在做数据的insert之前，请将目标表的约束和索引去掉，inset完了之后，再建立
insert into tab1 select * from tab2;
commit;
直接用这个，一般千万级的数据可在一小时内完成。

建议直接用游标控制，只要一个循环。insert 时用批量提交。本来想帮你重新写一下，可是你的from **********，不知道你这里还有什么业务逻辑

追问

‍

　　这是修改后的全部sql，是这个意思吧（字数超过限制所以我放的图片）。小弟不才，对存储过程不太了解。‍

Answer 3

分析下表数据，建立表的主键，利用主键索引查询，30w的数据不大，很快就可以查询出

追问

主键都有，索引还没建，建完索引查询快了，但是插入不就慢了么。

追答

30w的数据一次全部都插入的话是会慢点，但是你可以分块插入，比如分成10个数据块依次插入，这样就不会很慢了。。