How to speed up for loop in Oracle?

Question

I am working in Oracle version > 10gR2. I have a table with 20 million records. I want to process all records before exporting them to file, but performance of this is very slow.

Do you have any advice? Below is my code:

declare   
  l_temp_detail clob := null;    
  icount number:=0;

  cursor c_test is
    select /*+ PARALLEL(12) */ *
      from test_table t;

  TYPE t_test IS TABLE OF test_table%ROWTYPE INDEX BY PLS_INTEGER;
  l_test_items t_test;   

BEGIN
  l_temp_detail := '';  

  OPEN c_test;

  LOOP
    FETCH c_test 
      BULK COLLECT INTO l_test_items LIMIT 25000; 
    EXIT WHEN l_test_items.COUNT = 0;

    FOR i in l_test_items.first .. l_test_items.last      LOOP    
      icount := icount+1;     
      --doing business here
      l_temp_detail := ....
    END LOOP;      

    dbms_output.put_line(to_char(icount));   
  END LOOP;

  CLOSE c_test;    
END;

Answer 1

Couple of things. Use only those columns from test_table which you need. According to your need, seems you'd need only few column from test_table and limited set of records. You won't be concatenating 20 million records together.

Additionally, since table is huge, you may want to check if there are any partitioning and indexes. Using particular partition and right index will enhance the query execution time.

Answer 2

Parallel table functions enable parallel querying and parallel procedural processing.

Sample table and data.

drop table test_table;
create table test_table(a number);
insert into test_table select level from dual connect by level <= 1000000;
commit;

Return type (it's required even if you don't care about the results)

create or replace type number_nt is table of number;

Parallel pipelind function

--The syntax is very picky if you want to enable parallelism.
create or replace function f(p_cursor in sys_refcursor) return number_nt
    pipelined parallel_enable(partition p_cursor by any) is

    l_temp_detail clob := null;    
    icount number:=0;

    type t_test is table of test_table%rowtype index by pls_integer;
    l_test_items t_test;   
begin
    LOOP
        FETCH p_cursor 
            BULK COLLECT INTO l_test_items LIMIT 25000; 
        EXIT WHEN l_test_items.COUNT = 0;

        FOR i in l_test_items.first .. l_test_items.last LOOP    
            icount := icount+1;     
            --doing business here
            --l_temp_detail := ....
        END LOOP;      

        dbms_output.put_line(to_char(icount));   
    END LOOP;
    CLOSE p_cursor;    

    pipe row (icount);
end;
/

Query and results

--You may want to SUM() to get a total count.
--These aren't the results you care about, but they help show the distribution.
select column_value
from table(f(cursor(select /*+ parallel(12) */ * from test_table)));

COLUMN_VALUE
------------
124080
116160
65340
58080
79860
65340
60060
79860
59400
46960
116160
128700

Answer 3

I haven't actually tested this on 10g, but on Oracle9 processing huge numbers of records in plsql table is a factor of 10 less efficient than processing tables of single values.

So - if you refactor your code from "table of records" to "record of tables" you may see a significant improvement. You dont actually need to bundle the individual tables in a record - I just like it for clarity.

E.g.

declare
  type tName    is table of mytable.name%type index by pls_integer;
  type tAddress is table of mytable.address%type index by pls_integer;
  type tPerson  is record (
                     name     tName;
                     address  tAddress;
                     );
  person tPerson;
begin
  open somecursor;
  loop
    fetch somecursor bulk collect into person.name, person.address limit 25000;
    --
    -- processeing
    --
    exit when ...
  end loop;
  close somecursor;
end;

I can't promise that this will solve your problem for sure though - it depends on what you are actually doing with the data in the "processing" - e.g how much time is spent in plsql and how much is spent in sql doing dml.