Re: INSERT into partitioned table from a simple query gives wrong result

Thanks Kari

Regards
Lee

Kari Pannila <Xkari.pannila_at_tietoenator.com> wrote in message news:3911124F.A3AAAD7D_at_tietoenator.com...
> Hi
>
> I have heard only rumours that they would never fix this bug in 8.0.X
> I just hope it's not true...
>
> Kari
>
> (Mr.) Kari Pannila
> Oracle DBA
> --
>
> PS. Delete X from my mail address , it (X) is only for SPAMMERS.
> Poista X mail-osoitteen edestä, se on SPAMmereita varten.
>
> Lee Farrant wrote:
>
> > Is anyone aware of a fix for this?
> >
> > Lee
> >
> > Hdr: 1140366 8.0.5.2.1 RDBMS 8.0.5.2.1 PRODID-5 PORTID-453
> > Abstract: WRONG RESULT (DUPLICATED ROWS) FROM INSERT SELECT INTO
> > PARTITIONED TABLE
> > =========================
> > PROBLEM:
> > .
> > INSERT into partitioned table from a simple query gives wrong result
> > (duplicated rows) when target partitioned table is analyzed at the same
> > time.
> > .
> > Simplest testcase:
> > .
> > 1. Table d2 is empty and is partitioned by id and has two partitions,
> > table d1 contains, let's say, 10000 rows; no indexes on both tables.
> > .
> > 2. In the first session run:
> > .
> > insert into d2 (id) select id from d1;
> > .
> > 3. In the second session, while INSERT is still runnning, issue:
> > .
> > analyze table d2 estimate statistics sample 1 percent;
> > .
> > 4. After INSERT finishes table d2 contains more rows than d1.
> > .
> > Environment: no MTS, no OPS, no PQO, most INIT.ORA settings default
> > .
> > =========================
> > DIAGNOSTIC ANALYSIS:
> > .
> > Reproducibility does not depend on:
> > - number of partitions (tested with 2 and 10)
> > - data type of the partition key (tested with DATE and NUMBER)
> > - presence of statistics on the target table before the INSERT is
> > started
> > - presence of rows in the target table before the INSERT is started
> > - source or target table being locked exclusive before the INSERT is
> > started
> > .
> > Not tested:
> > - partitioning schemes other than PARTITION BY RANGE
> > - target table with only 1 partition
> > .
> > In another (more complicated) testcase the INSERT was run with a
> > sequence used
> > to number target rows. The resulting rows had no duplicates on the
> > sequence
> > number, i.e. the duplicates were created before sequence numbers were
> > added.
> > .
> > =========================
> > WORKAROUND:
> > Not using INSERT and ANALYZE on one partitioned table at the same time.
> > .
> > =========================
> > RELATED BUGS:
> > None found.
> > .
> > =========================
> > REPRODUCIBILITY:
> > 100% in 8.0.5.2.1 on Sun Solaris and Windows NT
> > the problem does not reproduce in 8.1.5
> > This problem also reproduces on 8.0.6.1
>
> --
>
>
>
Received on Thu May 04 2000 - 00:00:00 CDT