Re: Slow cursor

From: Jens Mayer <jmayer_at_ratundtat.com>
Date: Wed, 24 Nov 1999 11:45:40 GMT
Message-ID: <383bcecb.8758604@news.space.net>

Lee,

are you sure that there is a differenec between 'where a.col1=b.col1' and 'where b.col1 = a.col1' ? Until now, I could only see differences in the order of the tables in the FROM-clause:

select ....

 from tabA,
      tabB,
      tabC,
      tabD

 where ....

MAY have differences in the execution plan to

select ....

 from tabD,
      tabC,
      tabA,
      tabB

where ....

I am using Oracle 7.3.4 I don't know if it's different in 8.x.

Bye
Jens

On Wed, 24 Nov 1999 02:43:43 +0900, "Lee Kyoung Rok" <tunnel_at_hananet.net> wrote:

>
>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
>
>
>

-
Jens Mayer
Rat & Tat GmbH
Hamburg, Germany Received on Wed Nov 24 1999 - 05:45:40 CST