| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Strange behavior - recursive sql
Jonathan Lewis wrote:
> "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
Johnathan,
Thanks for the response. Yes, there was an FK relationship involved, but I'm still puzzled about a few things. Let me show you some more detail from the traces. For brevity, I won't paste everything from the trace, but will add some comments/questions as we go). I will show every statement in the sequence.
(this statement is issued by the app)
Parsing user id: SYS (recursive depth: 2)
(this table has two FK relationships with POSTING_SUMMARY. So I see the relationship, but don't really understand what is being accomplished here)
Parsing user id: SYS (recursive depth: 2)
(this table has one FK relationship with POSTING_SUMMARY. So I see the relationship, but don't really understand what is being accomplished here. As with the previous statement, now that I've come this far, I would have expected a DELETE on these dependent tables)
and this was our original problem statement. The referenced column
PSTDTL_PSTSUM_TS is defined
"PSTDTL_PSTSUM_TS VARCHAR2(26) NOT NULL ".
The FK relationship back to POSTING_SUMMARY is
"CONSTRAINT SYS_C003202524
FOREIGN KEY (PSTDTL_PSTSUM_TS)
REFERENCES NMM.POSTING_SUMMARY (PSTSUM_PSTSUM_TS)"
Notice there is no ON DELETE clause. So, at this point I would not
expect it to attempt to set the column to null, and given that it *is*
attempting and the column has a NOT NULL constraint, I'd expect this
to fail.
Parsing user id: SYS (recursive depth: 2)
This is the last of the recursive SQL. Not sure what's going on here, either.
 |
 |