Re: SELECT / UNION Giving Different Results

From: Mark Townsend <markbtownsend_at_sbcglobal.net>
Date: Sat, 30 Jun 2007 02:50:54 GMT
Message-ID: <iyjhi.545$eY.275@newssvr13.news.prodigy.net>

Dereck L. Dietz wrote:
> We've had a very strange (and possibly troubling) event happen.
>
> There is a table created and loaded by a SELECT statement which UNIONs about
> 20 tables together. While an analyst was analyzing the data she discovered
> there were rows in the source tables that never made it into the table
> created by the UNIONS and hence never loaded into the production table.
>
> When I was testing I reran the SELECT statement by copying it from the
> script it was in into SQL*PLUS and created a test table using the same
> source tables the original SELECT did. When I checked to see how many were
> in the test table but not in the production table I discovered 4,865 rows.
>
> Has anybody ever experienced anything like this? Using the same source
> tables the SELECT / UNION will one time miss almost 5,000 rows that it picks
> up the next time. We're at a loss trying to figure this out so any
> help/advice would be greatly appreciated.
>
> I know examples may be helpful but the information in the tables is all
> Protected Health Information so I can't provide any.
>
>

Having trouble following you.

Problem Part 1

---

You have 20 source tables - say S1 through S20  From these tables you populate a target table TA1 doing a UNION You can see rows in S1 through S20 that you expect to see in TA1

Problem Part 2

---

You created a test table TE1 from S1 through S20. When you looked at TE1 is had 4865 more rows than some production table P1 What is P1 ? P1=TA1 ?
Are the 4865 rows you see in TE1 the 'missing' rows you expected to see in TA1 ?

How do you populate TA1 and TE1 - CREATE TABLE AS SELECT, or INSERT ? Are there any primary keys, or ref integrity rules, or triggers that may prevent rows being inserted into TA1 ? Are the extra 4865 rows that you see in TE1 duplicates that you may be stripping out in your unions in production, but not in testing ?

Version of the database ? Received on Fri Jun 29 2007 - 21:50:54 CDT