Re: Strange behavior - recursive sql

"EdStevens" <quetico_man_at_yahoo.com> wrote in message news:1142869217.242054.294390_at_i40g2000cwc.googlegroups.com...
> Subject: Strange behavior - recursive sql
> Platform: Oracle 9.2.0.6.0 on Solaris 9
>
> What we found in looking at the 'before' trace was that most of the
> time was spent on this statement:
>
> UPDATE "NMM"."POSTING_DETAIL" set "PSTDTL_PSTSUM_TS" = null
> where "PSTDTL_PSTSUM_TS" = :1
>
> Two things were VERY strange about this.
> First, tkprof showed the parsing user id to be SYS, with a recursive
> depth of 2. We don't understand what we could have done to cause
> this kind of statement to show up as recursive SQL. The developer
> scanned the procedure to confirm that he is not issuing this statement.
>
> Second, the column PSTDTL_PSTSUM_TS that this statement is setting to
> null has a NOT NULL constraint! Am I losing my mind?
>
> While we couldn't figure out what was going on, we decided to try
> throwing an index on the column referenced by the WHERE clause and
> re-ran the test. This time, the procedure ran much faster. However,
> when we looked to see the performance of the problem query described
> above, we found that it had disappeared completely. There was no such
> query shown in the tkprof report.
>

It looks as if you have a referential integrity constraint on "NMM"."POSTING_DETAIL"
defined as

    on delete set null

Possibly with no index (or no useful index) to assist Oracle in handling the constraint.

Your pl/sql code then does

    delete from {parent_table} where ...

This is the level 1 recursive depth.
SYS fires the update to enforce the requirements of the foreign key definition - this is the level 2 recursive depth.

Creating the index may have allowed Oracle to use the index to detect efficiently that there were no child rows - thus avoiding the need to execute the update statement.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html