Hi,
We all know that doing things row-by-row ("Cursor For Loops") is a bad idea rather than a set-based approach, however I have read in a number of places that in certain circumstances Oracle will convert a cursor for loop into a set-based operation. Can you tell me what those conditions are, or if it makes more sense, what would prevent such a conversion?
I.e. given the following setup structures:
Create Table xxdemo (
a Number
);
Create Table xxdemo2 (
a Number
);
Create Procedure xxdemo_log(pTxt In Varchar2)
Is
Begin
Dbms_Output.Put_Line(To_Char(Sysdate,'DD/MM/YYYY HH24:MI:SS') || ' - ' || pTxt);
End xxdemo_log;
which of these would or wouldn't be converted into a "bulk collect" set based operation?
(1)Declare
Cursor c Is
Select Level n
From dual
Connect By Level <= 1000;
Begin
For i In c Loop
Insert Into xxdemo(a) Values (i.n);
End Loop;
End;(2)Declare
Cursor c Is
Select Level n
From dual
Connect By Level <= 1000;
Begin
For i In c Loop
Insert Into xxdemo(a) Values (i.n);
xxdemo_log('Inserted ' || To_Char(i.n,'fm9999'));
End Loop;
End;
(3)Begin
Insert Into xxdemo(a) Select Level From Dual Connect By Level <= 1000;
-- Would this be done as a set-based operation?
For i In (Select a From xxdemo) Loop
Update xxdemo Set a=a*1000 Where a=i.a;
End Loop;
End;
(4)Begin
-- Seed some data
Insert Into xxdemo(a) Select Level From Dual Connect By Level <= 1000;
For i In (Select a From xxdemo) Loop
Insert Into xxdemo2(a) Values (i.a);
End Loop;
End;
I know that SQL should be written correctly in the first place and the re-write should be no way replied upon, but this is more of an academic question that has arisen because I have seen a number of places using very "loopy" code, and I want to see whether there is actually a performance overhead or whether the code is just going to be transformed into a set-based operation anyway.
Many thanks
Oracle won't transform looped code into a set based operation.
But what it will do is optimize fetches in cursor for loops in PL/SQL. From 10g onwards, Oracle automatically uses an array fetch size of 100 for these.
So which of your examples does it do this for?
All of them!
You can see this by tracing your test:
exec dbms_monitor.session_trace_enable;
Declare
Cursor c Is
Select /*+ explicit 1 */Level n
From dual
Connect By Level <= 1000;
Begin
For i In c Loop
Insert Into xxdemo(a) Values (i.n);
End Loop;
End;
/
Declare
Cursor c Is
Select /*+ explicit 2 */Level n
From dual
Connect By Level <= 1000;
Begin
For i In c Loop
Insert Into xxdemo(a) Values (i.n);
xxdemo_log('Inserted ' || To_Char(i.n,'fm9999'));
End Loop;
End;
/
Begin
Insert Into xxdemo(a) Select Level From Dual Connect By Level <= 1000;
-- Would this be done as a set-based operation?
For i In (Select /*+ implicit 1 */a From xxdemo) Loop
Update xxdemo Set a=a*1000 Where a=i.a;
End Loop;
End;
/
Begin
-- Seed some data
Insert Into xxdemo(a) Select Level From Dual Connect By Level <= 1000;
For i In (Select /*+ implicit 2 */a From xxdemo) Loop
Insert Into xxdemo2(a) Values (i.a);
End Loop;
End;
/
Run tkprof on the trace file and you should see entries like:
SELECT /*+ explicit 1 */LEVEL N
FROM
DUAL CONNECT BY LEVEL <= 1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.00 0 0 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 0 0 1000
SELECT /*+ explicit 2 */LEVEL N
FROM
DUAL CONNECT BY LEVEL <= 1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.00 0 0 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 0 0 1000
SELECT /*+ implicit 1 */A
FROM
XXDEMO
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 31 0.99 0.98 0 6115 0 3000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.99 0.98 0 6116 0 3000
SELECT /*+ implicit 2 */A
FROM
XXDEMO
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 41 0.00 0.00 0 49 0 4000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 43 0.00 0.00 0 50 0 4000
See how the number of fetches = ( num rows / 100 ) + 1 for each query?
This tells you that has fetched the results in arrays of 100.
Further reading:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1203923200346667188