Re: Strange behavior - recursive sql

From: EdStevens <quetico_man_at_yahoo.com>
Date: 23 Mar 2006 17:45:38 -0800
Message-ID: <1143164737.967383.266190@i40g2000cwc.googlegroups.com>

Jonathan Lewis wrote:
> "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

Johnathan,

Thanks for the follow-up. I'm out of the office for a few days, and just checking here from a public PC, but will follow-up on Monday. Received on Thu Mar 23 2006 - 19:45:38 CST