hint i a cursor

From: Ludek Novotny <novot_at_post.tele.dk>
Date: Sun, 12 Dec 1999 20:11:51 +0100
Message-ID: <830t12$3i3$1@news.inet.tele.dk>

Hi all,

I use Oracle 7.3.3.6.0.

I select a sum from a view, such as:

select sum(col1)
from view1
where...

(there is no index on col1 in the underlying table)

The response time is about 10s with the rule based optimizer. When I use first_rows hint, the performance becomes substantially better (response time less than 0,1s). The response time is the same, when I use the underlying tables and the hint. So far no problems.

Now I want to use the same query in a cursor (c1), which is called from a for loop:

 c0 is...
 c1 is select sum(col1)

       from view1
       where ...;

 ...
 for r0 in c0
 loop

...

    open c1;
    fetch c1 into p1;
    close c1;
...

 end loop;

With the rule based optimizer the cursor is slow as expected. But: the performance becomes not better with the hint! The only way to reduce response time is to use the underlying tables and the hint.

How is it possible, that the same query performs much better 'as such' than in a cursor? My theory is, that Oracle 'translates' the hint in another way in
the isolated query and in the cursor. Is that possible? And is there a way to prove it?

Thanks in advance,

Ludek Novotny Received on Sun Dec 12 1999 - 13:11:51 CST