Re: Cursor with subquery takes forever when using different field

From: TurkBear <john.greco_at_dot.state.mn.us>
Date: Tue, 06 May 2003 12:28:14 -0500
Message-ID: <82sfbvcde3ht2ed0a7ufevi477mu31i3c7_at_4ax.com>


don_at_seiler.us (Don Seiler) wrote:

>I have a stored proc on an Oracle 7.3.4 database that has a query that
>goes something like this:
>
>CURSOR my_cur IS
>SELECT field1
> FROM table1
> WHERE blah = in_blah
> AND field1 NOT IN (SELECT other_field
> FROM table2
> WHERE blash = in_blah)
>
>However we are changing table2 to use another_field instead of
>other_field and will be dropping other_field altogether. However when
>I change the cursor subquery to use table2.another_field it takes
>FOREVER. I don't recall if my developer was ever able to allow the
>query to finish.
>
>table1.field1 is char(10). table2.other_field varchar2(32) and
>table2.another_field is varchar2(15). There are corresponding indexes
>on another_field that exist for other_field, and the query uses those
>indexes, but it still appears to hang.

Are there indexes on the blash and in_blah fields..? Did you reanalyze the tables after changing to another_field?

Have you tried it from SqlPLus with AUTOTRACE ON?

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- Received on Tue May 06 2003 - 19:28:14 CEST

Original text of this message