Re: SELECT / UNION Giving Different Results

On Jun 30, 6:50 am, Mark Townsend <markbtowns...@xxxxxxxxxxxxx> 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

.

Relevant Pages

  • Re: SELECT / UNION Giving Different Results
    ... There is a table created and loaded by a SELECT statement which UNIONs about ... in the test table but not in the production table I discovered 4,865 rows. ... You can see rows in S1 through S20 that you expect to see in TA1 ... When you looked at TE1 is had 4865 more rows than some production table P1 ...
    (comp.databases.oracle.server)
  • Re: Keeping the World Safe from Union Busting Attorneys...One Post ...
    ... busting the unions is an excellent step in revitalizing Detroit. ... that their health care systems are atrocious. ... We used to be the world leader in steel production. ... This is very embarrassing for the American ...
    (rec.music.dylan)
  • Re: Watching the election results ?
    ... labour at a level that would bankrupt their employers, ... jobs to some third world country. ... E.g. the American autoworkers' unions? ... production off-shore, and chosen to hollow-out the domestic production ...
    (sci.electronics.design)
  • SELECT / UNION Giving Different Results
    ... 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 ... in the test table but not in the production table I discovered 4,865 rows. ... Protected Health Information so I can't provide any. ...
    (comp.databases.oracle.server)