Home » SQL & PL/SQL » SQL & PL/SQL » limit in forall colect
limit in forall colect [message #320465] Thu, 15 May 2008 03:36 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
i have data in millions of records , and i get constraint when i try to fetch with or with out limit , kindly notice.
here emp1 has 3521476 records.

SQL> create or replace procedure bulkcollect1 is
  2   type emptype is table of emp1%rowtype;
  3   v_emptype emptype;
  4  begin
  5   select * bulk collect into v_emptype from emp1 ;
  6   forall i in 1 .. v_emptype.count
  7   insert into t2 values v_emptype(i);
  8  end;
  9  /

Procedure created.

 real: 32
SQL> 
SQL> execute bulkcollect1;

BEGIN bulkcollect1; END;

*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu call ,pmuccst:
adt/record)
ORA-06512: at "KEYFOOD1.BULKCOLLECT1", line 5
ORA-06512: at line 1



Re: limit in forall colect [message #320469 is a reply to message #320465] Thu, 15 May 2008 03:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And what happens if you replace this
select * bulk collect into v_emptype from emp1
with this
select * bulk collect into v_emptype from emp1 LIMIT 100


It will mean that you'll need another loop, but that's not a massive problem.

Of course the answer to the question that you're not asking is:
INSERT INTO t2(<column_list> SELECT <column_list> FROM emp1;
- just do it in a single SQL.
Re: limit in forall colect [message #320478 is a reply to message #320469] Thu, 15 May 2008 04:06 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
i wrote one procedure for similar purpose
  SQL> get forloop.sql
  1  create or replace  procedure forloop is
  2    cursor c1  is select * from emp1 ;
  3    c1row  c1%rowtype ;
  4  begin
  5   open c1;
  6   loop
  7    fetch c1 into c1row ;
  8   exit when c1%notfound ;
  9   insert into t3 values(c1row.empno , c1row.empno , c1row.job , c1row.mgr ,c1row.hiredate , c1ro
 10   end loop ;
 11   close c1 ;
 12* end ;
SQL> /

Procedure created.

SQL> exec forloop ;

PL/SQL procedure successfully completed.

 real: 294563

and then another one
SQL> set timing on
SQL> get bulkcollect
  1  create or replace procedure bulkcollect is
  2    cursor ee1 is select * from emp1 ;
  3    type emptype is table of ee1%rowtype;
  4    v_emptype emptype;
  5  begin
  6    open ee1 ;
  7    loop
  8    fetch ee1 bulk collect into v_emptype limit 10000;
  9    exit when v_emptype.count<1 ;
 10    forall i in 1 .. v_emptype.count
 11    insert into t2 values v_emptype(i);
 12    end loop ;
 13    close ee1 ;
 14* end;
SQL> /

Procedure created.

 real: 0
SQL> exec bulkcollect ;

PL/SQL procedure successfully completed.

 real: 28719
SQL> select count(*) from t2;

 COUNT(*)
---------
  3521476

 real: 3938
SQL> 


see both procedures returned the same number of records but real tells the time difference

procedure forloop real: 294563.

procedure bulkcollect real: real: 28719.

i get real when " set timing on"
kindly tellme two things.
what should be the valus of limit to get the best performance ,
and also in this real:28719 , what is it , is is time in milliseconds ???

[Updated on: Thu, 15 May 2008 04:08]

Report message to a moderator

Re: limit in forall colect [message #320479 is a reply to message #320478] Thu, 15 May 2008 04:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
NOw try running this:
create or replace procedure insert_select is
  begin
    insert into t2 (select * from emp1)
 end;


As I understand it, LIMIT is a resource management control.
The lower LIMIT is, the slower the code will run, but the less resources the session will consume.
Re: limit in forall colect [message #320498 is a reply to message #320479] Thu, 15 May 2008 05:15 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
JROWBUTTOM
ya sir i appreciate your idea.
but its a test situation ,
my real situations demand cursors with columns from more than 3/4 tables and with conditions in where clause ,
which require for cursors ,
as i have come to know that ForAll loops with collections is a much faster alternative t han for cursors
thats why i am trying to learn them and then
apply on my problems.
thanx
Re: limit in forall colect [message #320501 is a reply to message #320498] Thu, 15 May 2008 05:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

You should do it in a single SQL statement if at all possible.
If you cannot do it in a single SQL Statement, then do it in PL/SQL (as little PL/SQL as possible!).
If you cannot do it in PL/SQL, try a Java Stored Procedure. The number of times this is necessary is extremely rare today with Oracle9i and above.
If you cannot do it in Java, do it in a C external procedure. This is most frequently the approach when raw speed, or the use of a 3rd party API written in C is needed.
If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it...


This is the mantra from the Oracle Guru Tom Kyte. I follow it strictly when I am developing my code.

Quote:
my real situations demand cursors with columns from more than 3/4 tables and with conditions in where clause ,

I don't think just by looking at your justification it is a candidate for using cursors. If you really do some complex calculations/validations which is not possible in sql then do it in pl/sql, otherwise , If I were you I will try to do it in single sql statement. Also, another to bear in mind is from oracle 10g onwards following construct (implict cursor) uses bulk collect with a limit set to 100.
for i in (<query>)
loop
   ....
end loop;


Check this link for some factual information.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5918938803188

Regards

Raj
Re: limit in forall colect [message #320546 is a reply to message #320501] Thu, 15 May 2008 08:06 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hello,

Even if, you don't want to insert all the data from one table to another and want the data to be inserted based on some condition, you can do it using
insert into <tablename> ( <select_query> );

i.e. If you are able to query the data from table using select statement, then you can use that select query to insert those data in other table..

Try it.

Regards
Dipali
Previous Topic: Can we rename a constraint
Next Topic: Group by months
Goto Forum:
  


Current Time: Thu Dec 08 04:11:33 CST 2016

Total time taken to generate the page: 0.06841 seconds