Re: Outer Joins and Select ... for update of statements

On Fri, 27 Feb 1998 11:59:14 +1100, Stephen Walsh <stevew_at_helix.com.au> wrote:

>In Oracle 7.3 on NT and HPUX 10, the following select statement is part
>of a database procedure. When the procedure is executed, the database
>procedure hangs when this statement is reached:
>
>
>It was narrowed down to the combination of the outer join and FOR UPDATE
>OF lt.job_no.
>

First possibility is that someone else has one of the rows locked, and the procedure is waiting for the lock to be released.

Secondly, for update causes all rows retrieved by the query to be locked before the first row is returned. This has two consequences. First, if the query will process a large number of rows or the access path is poor, there will be a large delay. Secondly, Oracle might switch from applying a first_rows rule when determining the access path to an all_rows rule, thereby choosing a different access path. In my experience, all_rows should be avoided as it always seems to produce a poor access path. Received on Sun Mar 01 1998 - 00:00:00 CST