Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Slow cursor
Jens Mayer <jmayer_at_ratundtat.com>ÀÌ(°¡) ¾Æ·¡ ¸Þ½ÃÁö¸¦
news:383ac111.23589830_at_news.space.net¿¡ °Ô½ÃÇÏ¿´½À´Ï´Ù.
> 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
How about make a index on col1 of table B, or change the statement like
below
where b.col1 = a.col1
Received on Tue Nov 23 1999 - 11:43:43 CST