Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sql debugging tip: 'duplicate rows' from a complex query

Re: sql debugging tip: 'duplicate rows' from a complex query

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sun, 1 Feb 2004 18:12:14 -0500
Message-ID: <Q-ednRNI__tuGoDdRVn-jw@comcast.com>

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2687bb95.0402011402.4e15fdfc_at_posting.google.com...
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
news:<3Jidnec-iZpyCIfdRVn_iw_at_comcast.com>...
| > ever have a complex statement that works just fine in development and
test
| > but once you get it into production returns duplicate rows?
| >
| > had to figure one of those out last night, and the more we locked at the
| > statement and examined the data, the more our heads hurt
| >
| > this morning i remembered a quick way to determine what table is
| > contributing to the extra rows:
| >
| > we simply added the rowid of each table to the select list, and within a
few
| > minutes saw which table was returning too many rows by looking for
changes
| > in rowid without changes in data -- a whole lot easier than digging thru
the
| > data and doing extensive rewrites to progressively eliminate, or
buildup,
| > joins
| >
| > -- mcs
|
| Mark, I guess that is about the same as returning a constant to
| represent the table and has the advantage of allowing you to zero in
| on the rows in doubt. I like to add a constant that represents the
| table to view definitions that use a union all instead of a union
| where the same row should not exist in both source sets. Now and then
| what should not happen, does.
|
| -- Mark D Powell --

actually, far different from a constant -- in our case some developer (ok, me) missed a couple columns of a complex non-equjoin condition, causing 4x as many rows to be returned

with the production data the rows looked absolutely identical -- until we added the ROWID's and saw which table was contributing different rows, since the ROWID's were identical in the dup'd rows for all tables except for the poorly joined one. adding a constant wouldn't help to differentiate the rows, only the tables

Received on Sun Feb 01 2004 - 17:12:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US