Re: Strange behavior - recursive sql

"EdStevens" <quetico_man_at_yahoo.com> wrote in message news:1142954215.380481.103700_at_i39g2000cwa.googlegroups.com...
>
>
> 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.
>
> ************************************************************************
> DELETE FROM NMM.POSTING_SUMMARY PS
> WHERE
> PS.PSTSUM_PSTD_CDE = 'D'
>
> Parsing user id: 62 (recursive depth: 1)
>
> (this statement is issued by the app)
>
> ************************************************************************
> select /*+ all_rows */ count(1)
> from
> "NMM"."POSTING_CORRECTION" where "PSTCOR_CRCTN_PSTSUM_TS" = :1
>
> 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)
>
> ************************************************************************
> select /*+ all_rows */ count(1)
> from
> "NMM"."POSTING_EXCEPTION" where "PSTEXC_PSTSUM_TS" = :1
>
> 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)
>
> ************************************************************************
> update "NMM"."POSTING_DETAIL" set "PSTDTL_PSTSUM_TS" = null
> where
> "PSTDTL_PSTSUM_TS" = :1
>
> Parsing user id: SYS (recursive depth: 2)
>
> 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.
>
> ************************************************************************
> select condition
> from
> cdef$ where rowid=:1
>
> Parsing user id: SYS (recursive depth: 2)
>
> This is the last of the recursive SQL. Not sure what's going on here,
> either.
> ************************************************************************
>

First point - where did you get the constraint definition from ? If it's an extract from an old export file, or the script used to create the data, it may not be true. Check the user_constraints view for the child table - look for the delete rule.

Second - just because a column is declared NOT NULL, that doesn't mean that code which tries to set it null is illegal. If there is an RI constraint set to "on delete set null", then the parent delete WILL try to fire the update, and will then report an error about not being able to set the column null.

Look VERY closely at the table definitions - maybe there's an oddity if some NOT NULL constraints have been added as table-level check constraints rather than column level NOT NULL constraints; maybe things get a little different if some of the constraints are deferrable.

Export the structures of the three tables and import them to an empty schema elsewhere and see if you can reproduce the effect - possibly the actual SQL used is dictated by a cost-based decision so my trace won't look the same as your production-sized trace.

-- 
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