Hello Experts,
Oracle DB version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64 bit Production
As part of a package-function, I am inserting data into a table.
Table tab1 is the source table while table tab2 is the target table.
Data from tab1 is being validated using function : val_fun
Table Structure:
Tab1
create table tab1 (item number(8), item_desc varchar2(100));
Sample Data (around 10,00,000 records in the table)
ITEM ITEM_DESC
101 Shirt
102 Socks
103 Trousers
Tab2
create table tab2 (item number(8), item_desc varchar2(100), flag1 varchar2(1), flag2 varchar2(1),
create_id varchar2(30), create_time date);
Sample Data (to be inserted using function : fun1)
ITEM ITEM_DESC FLAG1 FLAG2 CREATE_ID CREATE_TIME
101 Shirt Y Y AAA 14-JUN-20
102 Socks Y Y AAA 14-JUN-20
103 Trousers Y Y AAA 14-JUN-20
Approach 1 for function : fun1
create or replace function fun1
return boolean
is
cursor c1
is
select item
from tab1 a
where not exists (select 1
from tab2 b
where a.item = b.item);
type t_c1 is table of c1%ROWTYPE;
L_c1 t_c1;
app_error exception;
L_flag varchar2(1);
begin
open c1;
loop
fetch c1 bulk collect into L_c1 limit 100;
exit when L_c1.COUNT = 0
begin
for i in 1..L_c1.count
loop
if val_fun (L_c1(i),
L_flag) = TRUE -- L_flag is out parameter of function : val_fun
then
if L_flag = Y -- skip, for any other value of L_flag
then
insert into tab2
select item,item_desc,'Y','Y',user,sysdate
from tab1
where item = L_c1(i);
end if;
else
raise app_error;
end if;
commit;
end loop;
exception
<handling when others / app_error>
return false;
end;
end loop;
return true;
exception
<handling WHEN OTHERS>
return false;
end fun1;
return true;
exception
<handling WHEN OTHERS >
end fun1;
Approach 2 for fun1:
create or replace function fun1
return boolean
is
cursor c1
is
select item,item_desc,'Y','Y',user,sysdate
from tab1 a
where not exists (select 1
from tab2 b
where a.item = b.item);
type t_c1 is table of c1%ROWTYPE;
L_c1 t_c1;
app_error exception;
L_flag varchar2(1);
begin
open c1;
loop
fetch c1 bulk collect into L_c1 limit 100;
exit when L_c1.COUNT = 0
begin
for i in 1..L_c1.count
loop
if val_fun (L_c1(i),
L_flag) = TRUE
then
if L_flag = Y
then
insert into tab2
values (L_c1.item,L_c1.item_desc,
L_c1.flag1,L_c1.flag2,L_c1.create_id,
L_c1.create_time);
end if;
else
raise app_error;
end if;
commit;
end loop;
exception
<handling when others / app_error>
end;
end loop;
return true;
exception
<handling WHEN OTHERS >
end fun1;
Could you please suggest the best approach with respect to code performance.
Thank You