Slow Cursor

Hi there,

we have a problem with the performance of a cursor. The cursor is a join of two tables A and B, A has about 500.000 records, B about 10.000.000 records. Primary key of A is col1, primary key of B is col1,col2. The join is like

select ...
from a,b
where a.col1 = b.col1

The execution plan uses a unique index access for table A and a index range scan for table B

We're using this cursor in a package, where it needs 4 seconds to retrieve about 10 records. In my opinion, this is SLOW! If we test the same select-statement directly from the SQL*Plus-Prompt, the DB answers in less than 1 seconds.

How can we tune this ? The cursor has to be executed several thousand times, this will last for hours....

Can anyone help ?

Jens

-
Jens Mayer
Rat & Tat GmbH
Hamburg, Germany Received on Tue Nov 23 1999 - 10:30:05 CST