Interesting to discuss the situation.
The goal is to unload data from table to simple .txt file with the help of UTC_FILE.
Whole file value is approximate 50Mb.
Execution script time is approximate 30hr.
As for it is too long. The question is - how modify this script to have less time to unloading.
The average speed is 1Kb per second. Writing operation takes many time to be executed.
May be it will be correct to write more data per time that it is now occured (to take more symbols to be written in one time).
Storage procedure in package is:
create or replace
procedure export_roads is
count_num number:=0;
BEGIN
select count(DISTINCT settle_idx) cnt into count_num from ACON_ADMIN;
log_road_writer.put_line(count_num); -- Count SYNT_ID
for i in (SELECT DISTINCT settle_idx, admin_order FROM ACON_ADMIN)
loop
log_road_writer.put_line(i.settle_idx); -- SYNT_ID
if i.admin_order=8 then -- order8_id roads count
begin
SELECT count(DISTINCT a.street_name) into count_num FROM acon_road_2 a where a.order8_id=i.settle_idx order by a.street_name;
log_road_writer.put_line(count_num);
for j in (SELECT DISTINCT a.street_name, a.street_language FROM acon_road_2 a where a.order8_id=i.settle_idx order by a.street_name)
loop
log_road_writer.put_line(j.street_name);
log_road_writer.put_line(j.street_language);
SELECT DISTINCT count(a.road_link_id) into count_num FROM acon_road_2 a where a.order8_id=i.settle_idx and a.street_name=j.street_name;
log_road_writer.put_line(count_num);
for c in (SELECT DISTINCT a.road_link_id FROM acon_road_2 a where a.order8_id=i.settle_idx and a.street_name=j.street_name)
loop
log_road_writer.put_line(c.road_link_id);
end loop;
end loop;
end;
else -- builtup_id roads count
begin
SELECT count(DISTINCT a.street_name) into count_num FROM acon_road_2 a where a.builtup_id=i.settle_idx order by a.street_name;
log_road_writer.put_line(count_num);
for j in (SELECT DISTINCT a.street_name, a.street_language FROM acon_road_2 a where a.builtup_id=i.settle_idx order by a.street_name)
loop
log_road_writer.put_line(j.street_name);
log_road_writer.put_line(j.street_language);
SELECT DISTINCT count(a.road_link_id) into count_num FROM acon_road_2 a where a.builtup_id=i.settle_idx and a.street_name=j.street_name;
log_road_writer.put_line(count_num);
for c in (SELECT DISTINCT a.road_link_id FROM acon_road_2 a where a.builtup_id=i.settle_idx and a.street_name=j.street_name)
loop
log_road_writer.put_line(c.road_link_id);
end loop;
end loop;
end;
end if;
end loop;
END;
/
