Home » RDBMS Server » Performance Tuning » Suggestions of manipulating 3 crores of data (Oracle 8i)
| Suggestions of manipulating 3 crores of data [message #527152] |
Sat, 15 October 2011 21:07  |
 |
shyamu
Messages: 30
Registered: September 2011
Location: HYD
|
Member |
|
|
Team,
I had reqirment such as, I need to insert/Update data 3 crores of data(by doing cursor with joining of 4 tables) and have to manipulate data with some conditions and then need to insert/update data into a table with 5 crores.
I had wriiten this program with normal cursor but it's taking around 250 mins but i had requirment like needs to be complete with in 10 mins or around.
Could you please give me some suggestions on this ?
Appreciate your quick response on this..
Thanks
|
|
|
|
|
|
| Re: Suggestions of manipulating 3 crores of data [message #527154 is a reply to message #527153] |
Sat, 15 October 2011 21:17  |
|
shyamu
Messages: 30
Registered: September 2011
Location: HYD
|
Member |
|
|
procedure xyz as
--------------------------------------------------------------------------------
-- variables
--------------------------------------------------------------------------------
v_version_number varchar2(10) := 'v1.1';
proc_name varchar2(50) := 'xyz'||CHR(32)|| v_version_number;
culprit varchar2(36);
debug varchar2(10) := '1';
sql_cde number(10);
sql_msg varchar2(70);
abort_prog exception;
rec_found varchar2(1);
rec_found_id number(8);
set_status varchar2(1);
order_ord_num number;
order_ord_line_num number;
order_grade_name varchar2(60);
order_mtl_art_id number(15);
order_bxp varchar2(1);
fail_ind number(1);
qty_fail_ind number(1);
grade_fail_ind number(1);
pack_type_fail_ind number(1);
port_fail_ind number(1);
del_date_fail_ind number(1);
bxp_fail_ind number(1);
not_delivered_ind number(1);
nom_qty_low number(12,3);
nom_qty_high number(12,3);
perf_ref number;
-- End change
--------------------------------------------------------------------------------
-- Cursor
--------------------------------------------------------------------------------
cursor order_line is
select a.*, req_prod.grade_name req_grade_name, req_prod.mtl_art_id req_mtl_art_id,
real_prod.grade_name real_grade_name, real_prod.mtl_art_id real_mtl_art_id
from dfn_noms_fact a,dfn_product req_prod,dfn_product real_prod
where a.req_dfn_product_id = req_prod.id
and a.real_dfn_product_id = real_prod.id
and a.positional_status in (3,5,6,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25)
and expc_dvly_date_start < (sysdate+1)
and a.transaction_id > 9999999
order by a.transaction_id, a.transaction_line_id
;
cursor inv_line is
select a.*, b.grade_name, b.mtl_art_id from dfn_sales_fact a, dfn_product b
where a.del_dfn_product_id = b.id
and a.ord_num = order_ord_num
and b.grade_name = order_grade_name
and contra_ind = 'N'
order by a.contra_ind, a.created_date desc
;
inv_line_record inv_line%rowtype;
--------------------------------------------------------------------------------
-- Executable part
--------------------------------------------------------------------------------
begin
debug:='100';
--------------------------------------------------------------------------------
-- Begin loop which retrieves rows from the dfn_noms_fact table
--------------------------------------------------------------------------------
dbms_output.put_line('dfn_upd_failed_noms');
sp_timer.capture('start');
perf_ref := dfn_log_performance (proc_name, 'START', 0);
dbms_output.put_line('perf_ref: '||perf_ref);
culprit := 'xyz ' ||v_version_number;
for ir in order_line loop
--------------------------------------------------------------------------------
-- Set indicators
--------------------------------------------------------------------------------
fail_ind := 0;
qty_fail_ind := 0;
grade_fail_ind := 0;
pack_type_fail_ind := 0;
port_fail_ind := 0;
del_date_fail_ind := 0;
bxp_fail_ind := 0;
nom_qty_low := 0;
nom_qty_high := 0;
not_delivered_ind := 0;
--------------------------------------------------------------------------------
-- For this order line try and get the invoice line
--------------------------------------------------------------------------------
begin
debug:='200';
order_ord_num := ir.order_no;
order_ord_line_num := ir.transaction_line_id;
-- Modlog1 begins
order_grade_name := nvl(ir.real_grade_name,ir.req_grade_name);
-- order_grade_name := nvl(ir.req_grade_name,ir.real_grade_name);
order_mtl_art_id := nvl(ir.real_mtl_art_id,ir.req_mtl_art_id);
-- order_mtl_art_id := nvl(ir.req_mtl_art_id,ir.real_mtl_art_id);
-- Modlog1 ends
order_bxp := nvl(ir.real_bxp,ir.req_bxp);
inv_line_record.id := null;
inv_line_record.transaction_ref := null;
debug:='300';
open inv_line;
debug:='400';
fetch inv_line into inv_line_record;
if inv_line%notfound then
-- dbms_output.put_line('============');
-- dbms_output.put_line('No inv line found for order '||order_ord_num||' line '||order_ord_line_num||' Product '||order_grade_name);
not_delivered_indNo_Inv_Found := 1;
goto No_Inv_Found;
end if;
end;
-- dbms_output.put_line('=======================');
-- dbms_output.put_line('Ord no: '||order_ord_num||' Grade: '||order_grade_name);
--------------------------------------------------------------------------------
-- Quantity failure?
--------------------------------------------------------------------------------
debug:='500';
-- Set low quantity = nominated quantity less 10% if bulk, and less 20% for others.
-- Over supply should not cause failure so set high range to 1100% of nomination
if inv_line_record.bxp = 'B' then
nom_qty_low := nvl(ir.pd_actl_tonnage,nvl(ir.real_tonnage_high,ir.req_tonnage)) -
(nvl(ir.pd_actl_tonnage,nvl(ir.real_tonnage_high,ir.req_tonnage)) * 0.02);
nom_qty_high := nvl(ir.pd_actl_tonnage,nvl(ir.real_tonnage_high,ir.req_tonnage)) +
(nvl(ir.pd_actl_tonnage,nvl(ir.real_tonnage_high,ir.req_tonnage)) * 0.02);
else
nom_qty_low := nvl(ir.pd_actl_tonnage,nvl(ir.real_tonnage_high,ir.req_tonnage)) -
(nvl(ir.pd_actl_tonnage,nvl(ir.real_tonnage_high,ir.req_tonnage)) * 0.02);
nom_qty_high := nvl(ir.pd_actl_tonnage,nvl(ir.real_tonnage_high,ir.req_tonnage)) +
(nvl(ir.pd_actl_tonnage,nvl(ir.real_tonnage_high,ir.req_tonnage)) * 0.02);
end if;
if (inv_line_record.del_quantity not between nom_qty_low and nom_qty_high) and (inv_line_record.del_quantity > 0) then
qty_fail_ind := 1;
end if;
--------------------------------------------------------------------------------
-- Delivery Date failure?
--------------------------------------------------------------------------------
if inv_line_record.del_date between nvl(ir.pd_actl_start_date,ir.expc_dvly_date_start) and nvl(ir.pd_actl_end_date,ir.expc_dvly_date_end) then
del_date_fail_ind := 0;
else
del_date_fail_ind := 1;
end if;
--------------------------------------------------------------------------------
-- Pack Type failure?
--------------------------------------------------------------------------------
-- Pack Type doesn't exist in ISP but we can compare articles so will do this for now....
if inv_line_record.mtl_art_id order_mtl_art_id then
pack_type_fail_ind := 1;
end if;
--------------------------------------------------------------------------------
-- BXP failure?
--------------------------------------------------------------------------------
if nvl(ir.real_bxp,ir.req_bxp) inv_line_record.bxp then
bxp_fail_ind := 1;
end if;
-- dbms_output.put_line('BXP ind:'||bxp_fail_ind);
--------------------------------------------------------------------------------
-- Port failure?
--------------------------------------------------------------------------------
if (nvl(ir.dfn_loc_id,0) inv_line_record.del_dfn_loc_id) and (ir.dfn_loc_id 0) then
port_fail_ind := 1;
end if;
-- port_fail_ind := 0; -- port failures not recorded at present
-- dbms_output.put_line('Port ind:'||port_fail_ind);
<<No_Inv_Found>>
--------------------------------------------------------------------------------
-- Failure?
--------------------------------------------------------------------------------
debug:='600';
if (
qty_fail_ind +
grade_fail_ind +
pack_type_fail_ind +
port_fail_ind +
del_date_fail_ind +
bxp_fail_ind +
not_delivered_ind
)
> 0 then
fail_ind := 1;
end if;
-- dbms_output.put_line('Fail ind:'||fail_ind);
--------------------------------------------------------------------------------
-- Does a dfn_failed_noms record already exist?
--------------------------------------------------------------------------------
debug:='700';
begin
rec_found := 'N';
select 'Y'
into rec_found
from dfn_failed_noms
where dfn_failed_noms.order_no = order_ord_num
and dfn_failed_noms.order_line_no = order_ord_line_num;
exception
when no_data_found then
null;
end;
if rec_found = 'Y' then
debug:='800';
begin
-- dbms_output.put_line('Upd');
update dfn_failed_noms set
dfn_sales_fact_id = inv_line_record.id,
transaction_ref = inv_line_record.transaction_ref,
qty_fail = qty_fail_ind,
grade_fail = grade_fail_ind,
port_fail = port_fail_ind,
del_date_fail = del_date_fail_ind,
bxp_fail = bxp_fail_ind,
pack_type_fail = pack_type_fail_ind,
fail = fail_ind,
not_delivered = not_delivered_ind,
updated_by = culprit,
updated_date = sysdate
where dfn_failed_noms.order_no = order_ord_num
and dfn_failed_noms.order_line_no = order_ord_line_num;
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
dbms_output.put_line('Failed to update rec '||ir.id);
raise abort_prog;
end;
end if;
--------------------------------------------------------------------------------
-- If record not found insert one
--------------------------------------------------------------------------------
if rec_found = 'N' then
debug:='900';
begin
-- dbms_output.put_line('Ins');
insert into dfn_failed_noms (
dfn_sales_fact_id,
transaction_ref,
order_no,
order_line_no,
fail,
qty_fail,
grade_fail,
port_fail,
del_date_fail,
bxp_fail,
pack_type_fail,
not_delivered,
created_by,
created_date)
values (
inv_line_record.id,
inv_line_record.transaction_ref,
order_ord_num,
order_ord_line_num,
fail_ind,
qty_fail_ind,
grade_fail_ind,
port_fail_ind,
del_date_fail_ind,
bxp_fail_ind,
pack_type_fail_ind,
not_delivered_ind,
culprit,
sysdate);
exception
when others then
dbms_output.put_line('Failed to insert record '||inv_line_record.id);
raise abort_prog;
end;
end if;
commit;
debug:='1000';
close inv_line;
end loop;
commit;
dbms_output.put_line(culprit||' finished okay');
sp_timer.show_elapsed(proc_name);
-- Added by Navin 18-Sep-2003
perf_ref := dfn_log_performance (proc_name, 'STOP', perf_ref);
dbms_output.put_line('perf_ref: '||perf_ref);
-- End change
exception
--------------------------------------------------------------------------------
-- fatal Error Report
--------------------------------------------------------------------------------
when abort_prog then
dbms_output.put_line(culprit||' aborted '||debug);
dbms_output.put_line(substr(sqlerrm, 1, 70));
when others then
dbms_output.put_line(culprit||' failed '||debug);
dbms_output.put_line(substr(sqlerrm, 1, 70));
end;
PS:First coursor retunning 3 crores of data and i'm inserting dfn_failed_noms which has 5 crores of data
Please give me some suggestions on this
Can
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 19:04:46 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
