Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql tuning
icon5.gif  pl/sql tuning [message #236543] Thu, 10 May 2007 04:10 Go to next message
huje
Messages: 7
Registered: May 2007
Location: kolkata
Junior Member

i have three tables consider A,B,C. now i want fetch data from A by comparing with B and the data will be inserted into C.there are about 2,00,000 rows into table A and about 6,00,000 rows into table B.i write a procedure. it is given below.

PROCEDURE LAST_AMENDED_emp IS
cursor c1 is select emp_no from A
order by emp_no ;
v_amend_dt date;

i number:=0;

BEGIN

for i in c1

loop

begin

select max(to_date(log_dt)) into v_amend_dt from B where substr(record_key,1,Cool = i.emp_no;
exception
when no_data_found then null;
when too_many_rows then null;
end;
begin
insert into C (emp_NO,DT_LAST_AMEND_CREA) values (i.rc_no,v_amend_dt);
commit;
exception
when others then null;
end;
end loop ;
END;

------
its a simple procedure. but it take too long time due to the large number of records. now i want to tune this procedure.
can any one help me.

[Updated on: Thu, 10 May 2007 04:13]

Report message to a moderator

Re: pl/sql tuning [message #236552 is a reply to message #236543] Thu, 10 May 2007 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
insert into C (emp_NO,DT_LAST_AMEND_CREA)
select emp_no,
       max(to_date(log_dt)) 
from a, b
where substr(b.record_key,1,) = i.emp_no
group by emp_no
/

Btw, there is no greatest error in writing PL/SQL than:
Quote:
exception
when others then null;
end;

Regards
Michel
Re: pl/sql tuning [message #236612 is a reply to message #236552] Thu, 10 May 2007 06:48 Go to previous messageGo to next message
huje
Messages: 7
Registered: May 2007
Location: kolkata
Junior Member

thank u Michel,
i know that it will work,but i want to tune the procedure.basically a sql does not serve my problem,the main procedure is different from the example which is given by me.
Re: pl/sql tuning [message #236623 is a reply to message #236543] Thu, 10 May 2007 07:26 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I can only imagine that Michel has thrown his hands over his eyes in despair.
Quote:
know that it will work,but i want to tune the procedure...,the main procedure is different from the example which is given by me.

So you are basically saying
"I need someone to tune this query only it's not actually this query, it's a different one.
Let me ask you this
Can you tune this query
SELECT * from employees
but take into account the fact that I'm not using an employees table and i'm not doing a select *

If you can, then you are a GOD! Wink

You might want to look at bulk processing rather than row by row. Something like

...
TYPE typ_tab IS TABLE OF VARCHAR2(200);
  tabs typ_tab;

BEGIN

   SELECT emp_no
   BULK COLLECT INTO tabs
   FROM A
   Order By emp_no;
   
  for i in tabs.FIRST..tabs.Last loop
    begin ...

But you really need to give much more accurate information if you are going to get any kind of truly useful answer.
Re: pl/sql tuning [message #236631 is a reply to message #236623] Thu, 10 May 2007 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can't say better or maybe ./fa/917/0/

Regards
Michel
Re: pl/sql tuning [message #236638 is a reply to message #236543] Thu, 10 May 2007 08:02 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
The order by could probably be got rid of.
Re: pl/sql tuning [message #236775 is a reply to message #236623] Fri, 11 May 2007 00:17 Go to previous messageGo to next message
huje
Messages: 7
Registered: May 2007
Location: kolkata
Junior Member

sorry michel,
its my fault.now i get the answer.
actually i am looking for a example of bulk processing.
Re: pl/sql tuning [message #236785 is a reply to message #236775] Fri, 11 May 2007 00:39 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try to search in Oracle documentation?
"PL/SQL User's Guide and Reference" and "Application Developer's Guide - Fundamentals" contain plenty of examples.

Regards
Michel
Previous Topic: PLS-00306: wrong number or types of arguments in call to
Next Topic: Need help on ALL/DBA_DEPENDENCIES
Goto Forum:
  


Current Time: Sat Dec 03 06:08:21 CST 2016

Total time taken to generate the page: 0.10716 seconds