Hello All,
I have two tables Table_A and Table_B. Table_A contains unque id 's of 30,000 customers and Table_B contains the details of these customers which has 3 million records. I have to check for several types of order_num for each person. So I have taken two cursors cur_1 and Cur_2. Cur_1 contains unique id's of 30,000 from table_A. Cur_2 contains 3 million records of these customers from Table_B. I have to look for certain orders_nums for each person. So I loop through cur_1 and then loop through cur_2 to find details of each customer. But when I run the program, it takes ages and ultimately I'm logged out with the error message "Exceeded Session limit on CPU Usage". I spoke to my DBA but he says it's because of my "two FOR LOOPS" as it seems it has to loop through 3 million records 30 thousand times, if I'm not wrong. Can you suggest me a way arround it. My sample code looks like this:
cursor cur_1 is select cust_id from customers;
cursor cur_2 is select cust_id, order_num, from diagnosis;
g number := 0;
g1 number := 0;
vcount1 number := 0;
vcount2 number:= 0;
vcount number := 0 ;
begin
for c1 in cur_1
loop
for c2 in cur_2
loop
if c1.cust_id = c2.cust_id then
if c1.order_num in ('V200','V270','V600') then
g := g+1;
if g>1 then
vcount1 :=1;
end if;
elsif c1.order_num between 'E550' and 'E770' then
g1 := g1+1;
if g1>1 then
vcount2 := 1;
end if;
end if;
vcount := vcount1+vcount2;
if vcount >1 then
dbms_output.put_line( c1.cust_id ||' '||vcount);
end if;
end if;
end loop;
vcount1 := 0;
vcount2 := 0;
vcount := 0;
g :=0;
end loop;
Thanks in advance!
Start Free Trial
