Re: SELECT / UNION Giving Different Results

On Jun 30, 6:50 am, Mark Townsend <markbtowns..._at_sbcglobal.net> wrote:
> 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 ?

To add to this questionnaire: are the source tables static (that is, no DML happens against them while the insert goes on?) And were they such when original insert took place? Is the issue reproducible in stabilized environment (that is, no DML on source tables, you run insert as select and create as select and see different results?)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sat Jun 30 2007 - 03:12:47 CDT