ref cursor slow performance
Date: 12 Jul 2002 21:22:33 -0700
Message-ID: <b653bde2.0207122022.6cc8986d_at_posting.google.com>
Hi
i've a performance problem when using ref cursor. My code is as
follows
str:=' select a.col1,b.col2,c.col3(, a list of columns..)
The above block is a small part of my actual procedure. In this, the
table a contains 300,000 records, table b contains more than 2 million
records and table c contains only 200 records. All tables analyzed.
The issue is when i execute the query alone without the following
cursor loop it executes in 1 second. But the block after that where i
use the cursor ,it takes nearly 3 minutes to complete the complete
fetch loop.
The main probs is this query returns around 7500 records. The same
block if i add a rownum restriction with <10 or 100 in the query
records its faster.
from
from tab1 a,tab2 b ,tab3 c
WHERE a.col6=3
AND a.col3 <= TO_DATE(''02-SEP-2002'',''DD-MON-YY'') + 1
AND b.col2=62
AND c.col1=b.col2
AND a.col4=b.col1
AND EXISTS ( SELECT 1 FROM tab4 c where col1=b.col1)
AND EXISTS ( SELECT 1 FROM tab5 d where col2=a.col5) ';
open cur1 for str;
loop
fetch cur1 into typ1 ; /* typ1 is a record type variable */
exit when cur1%notfound;
tot := tot+1;
end loop;
I tried to use the oracle 8i bulk collect statements like BULK INTO .
kumar
Received on Sat Jul 13 2002 - 06:22:33 CEST