You Asked

I have a query with 4 For loops puting data into temp table an then that temp table TEMPTBL_NUMBER_SEARCH is called to execute the operations in a select clause. So the problem with the 4 for loop is making it slow to 15-20 mins. Its all indside a procedure for generating a report:
This is one loop And there are 4
BEGIN
delete from tempTbl_NUMBER_SEARCH;
FOR I IN P_Locations.FIRST .. P_Locations.LAST LOOP
BEGIN
if(P_Locations(I) !='0') then
Insert into TEMPTBL_NUMBER_SEARCH values(I,P_Locations(I),'DF');
end if;
END;
END LOOP;

FOR I IN P_WorkCenters.FIRST .. P_WorkCenters.LAST LOOP
BEGIN
if(P_WorkCenters(I) !='0') then
Insert into TEMPTBL_NUMBER_SEARCH values(I,P_WorkCenters(I),'WC');
end if;
END;
END LOOP;

FOR I IN P_KeyRingIds.FIRST .. P_KeyRingIds.LAST LOOP
BEGIN
if(P_KeyRingIds(I) !='0') then
Insert into TEMPTBL_NUMBER_SEARCH values(I,P_KeyRingIds(I),'KR');
end if;
END;
END LOOP;

FOR I IN P_Routes.FIRST .. P_Routes.LAST LOOP
BEGIN
if(P_Routes(I) !='0') then
Insert into TEMPTBL_NUMBER_SEARCH values(P_Routes(I),'','RT');
end if;
END;
END LOOP;
followed by a select statment calling them.

Can you suggest me how can improve it.

and Connor said...

You can do an initial parse of the incoming data to split it into separate PLSQL tables. That will let you use FORALL for dramatic speed up eg

SQL> create table t_source as select owner, object_name from dba_objects;

Table created.

SQL>
SQL> create table t_target as select rpad('x',20) typ, owner, object_name from dba_objects where 1=0;

Table created.

SQL>
SQL> declare
  2     type rowlist is table of t_source%rowtype index by pls_integer;
  3     r rowlist;
  4     r1 rowlist;
  5     r2 rowlist;
  6
  7  BEGIN
  8    select owner, object_name bulk collect into r from t_source;
  9    for i in 1 .. r.count loop
 10      if r(i).owner in ('SYS','SYSTEM') then
 11         r1(r1.count+1) := r(i);
 12      else
 13         r2(r2.count+1) := r(i);
 14      end if;
 15    end loop;
 16
 17    forall i in r1.first .. r1.last
 18       insert
 19       into  t_target
 20       values ( 'INTERNAL', r1(i).owner, r1(i).object_name );
 21
 22    forall i in r2.first .. r2.last
 23       insert
 24       into  t_target
 25       values ( 'EXTERNAL', r2(i).owner, r2(i).object_name );
 26
 27  end;
 28  /

PL/SQL procedure successfully completed.

SQL>

You can also you the INDICES OF clauses to achieve similar. Check here for details on that

http://www.oracle.com/technetwork/issue-archive/o14tech-plsql-091175.html

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Better is

Gh, November 13, 2017 - 8:55 am UTC

If your collections are db defined objects your can use the TABLE keyword and do your inserts as sql oneshot. E.g. insert into t
Select colunm value from TABLE(..cast as your type) including a where clause .

Neha Dhingra, November 14, 2017 - 2:46 pm UTC

I changed my code based on the example you gave me, but now its NULL issue. can you suggest me an approach,
TYPE rowlist IS TABLE OF  TEMPTBL_NUMBER_SEARCH%rowtype INDEX BY  pls_integer;
     NUM_COLUMN rowlist;
     VAR_COLUMN rowlist;
     TYPEDATA rowlist;
BEGIN
select NUM_COLUMN, VAR_COLUMN, TYPE bulk collect into NUM_COLUMN from TEMPTBL_NUMBER_SEARCH;
       FOR I in 1 .. NUM_COLUMN.count LOOP
    IF(P_Locations(I) !='0') OR (P_WorkCenters(I) !='0') OR (P_KeyRingIds(I) !='0') OR (P_Routes(I) !='0') THEN
    VAR_COLUMN(VAR_COLUMN.count+1):=NUM_COLUMN(I);
    TYPEDATA(TYPEDATA.count+1):=NUM_COLUMN(I);
       ELSE 
       NUM_COLUMN := I;
    END IF;
    END LOOP;
    
    FORALL I IN P_Locations.FIRST .. P_Locations.LAST 
       Insert into TEMPTBL_NUMBER_SEARCH values(I,P_Locations(I),'DF');
        
       FORALL I IN P_WorkCenters.FIRST .. P_WorkCenters.LAST 
       Insert into TEMPTBL_NUMBER_SEARCH values(I,P_WorkCenters(I),'WC');
       
    FORALL I IN P_KeyRingIds.FIRST .. P_KeyRingIds.LAST 
       Insert into TEMPTBL_NUMBER_SEARCH values(I,P_KeyRingIds(I),'KR');
      
       FORALL I IN P_Routes.FIRST .. P_Routes.LAST 
       Insert into TEMPTBL_NUMBER_SEARCH values(P_Routes(I),'','RT');

END
But now its giving me error
Error(558,49): PLS-00430: FORALL iteration variable I is not allowed in this context

SO which means its taking NULL what should I do?

November 15, 2017 - 2:04 am UTC

Look at my example again - notice I reference the elements of the record, ie

r1(i).owner, r1(i).object_name

not

r1(i)

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Better is

More to Explore

PL/SQL demos

PL/SQL docs

For Joins in Query Performance optimization

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Better is

More to Explore

PL/SQL demos

PL/SQL docs