Oracle optimise query avoiding cursors

Question

Hello everybody,

I'm working on a piece of sql that I want to optimize. I have inside a bunch of cursors. I'm wondering if I can use something else instead of cursors. I'm thinking using some kind of variables, filling them, and for the rest of the treatment avoiding the DB connection (I have a complex treatment).

For instance I have a piece of code like :

TYPE rec_basket IS RECORD (
 FIELD1 VARCHAR2(40),
 FIELD2 NUMBER(10),
 FIELD3 VARCHAR2(6)
 );

 TYPE tab_basket IS TABLE OF rec_basket
 INDEX BY BINARY_INTEGER;

........................

CURSOR cur_baskets
   IS
select * from toto

............................

 FOR i IN cur_baskets
   LOOP
  l_tab_basket (l_nbasket).field1 := i.field1;
  l_tab_basket (l_nbasket).field2 := i.field2;
  l_tab_basket (l_nbasket).field3  := i.field3;
  l_nbasket := l_nbasket + 1;    
   END LOOP;

Using a cursor and filling the l_tab_basket variable is the best way to go? I'm using l_tab_basket (index) somewhere in my code. The reason I've put this piece of code is that I would like use this mechanism for my other cursors. Actually I have a cursor inside another one. And for each line of each of them I have some treatment. I would like to replace the cursors with something else, but I don't know how. Thanks.

Answer 1

Hi CC,

You can use BULK COLLECT to fetch all records into your nested table. This would work in 10g+:

SQL> DECLARE
  2     TYPE rec_basket IS RECORD(
  3        field1 VARCHAR2(40),
  4        field2 NUMBER(10),
  5        field3 VARCHAR2(6));
  6     TYPE tab_basket IS TABLE OF rec_basket INDEX BY BINARY_INTEGER;
  7     l_tab_basket tab_basket;
  8  BEGIN
  9     SELECT 'a', ROWNUM, 'b'
 10       BULK COLLECT INTO l_tab_basket
 11       FROM dual CONNECT BY LEVEL <= 1000;
 12  END;
 13  /

PL/SQL procedure successfully completed

Keep in mind that Oracle 10g automatically fetch records from implicit cursors in bulk (100) in pl/sql, so the gains should be marginal at best: you will probably spend more time querying the DB than building the array unless the array is really really big (and in that case is it wise to use a nested table?)

Answer 2

Logic of your code is not very clear. You have not written whole program. Let us examine:

CURSOR cur_baskets IS select * from toto

Here the value are read from the table toto and put into a cursor.

In the following lines the values are read from the cursor and put into l_tab_basket.

FOR i IN cur_baskets LOOP l_tab_basket (l_nbasket).field1 := i.field1; l_tab_basket (l_nbasket).field2 := i.field2; l_tab_basket (l_nbasket).field3 := i.field3; l_nbasket := l_nbasket + 1;
END LOOP;

So same values are going to local variable twice. This can be avoided. You can find out ways to directly insert into or update target table.

You can try bulk collect. If toto is small, you can insert into or update the target table without using cursor.