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

I am not sure if it has anything to do with the outer join, but I do know that when you use the FOR UPDATE clause in a cursor that Oracle will attempt to lock ALL rows identified for update. In your example all rows in the ledger_tfr table that have been identified in your WHERE clause. If there are a lot of rows or there are other processes that have the same rows locked, then the select hangs until it can place the locks on all the rows. After it gets the locks is when it will start processing the cursor.

Hope this helps,
Patrick Flahan
flahan_at_leading.net

Stephen Walsh wrote in message <34F60FE0.2F8C152B_at_helix.com.au>...
>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:
>
>SELECT lt.ROWID,
> lt.job_no,
> TO_CHAR(lt.sequence_no),
> lt.cost,
> lt.item_used_date,
> lt.posting_code,
> fi.order_no
>FROM ledger_tfr lt,
> res_expense_element re,
> facility_extra_data fi /* outer join */
>WHERE lt.facility_code = fi.facility_code(+)
>AND re.resource_code = lt.resource_code
>AND re.gl_ctrl_account = '1234'
>AND lt.gl_batch_id = '000011'
>AND SUBSTR(lt.job_no,1,1) = 'J'
>AND (lt.gl_period = 2 OR lt.projects_period = 2)
>AND SUBSTR(lt.posting_code,1,4) = '0101'
>FOR UPDATE OF lt.job_no;
>
>It was narrowed down to the combination of the outer join and FOR UPDATE
>OF lt.job_no.
>
>Without the FOR UPDATE statement, the select statement runs as expected.
>
>Does anyone know why an outer join and for update statement combination
>causes a statement to hang.
>
>Any comments would be appreciated.
>--
>Regards
>Stephen Walsh
>===========================================
>Stephen Walsh
>Helix Systems Pty Ltd
>Ph: +61 7 3846 1999
>Fax: +61 7 3846 2122
>Email: stevew_at_helix.com.au
>Web: http://www.helix.com.au/
>===========================================
>
>
Received on Sun Mar 01 1998 - 00:00:00 CST