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

Here's just shooting from the hip. What if you put the tablespaces in backup mode when you do your analyze? That way the table changes are being written to the redo log until you have finished your analyze.

"Lee Farrant" <lee_at_vivao.net> wrote in message news:8esjth$mkl$1_at_gxsn.com...
> 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 Mon May 08 2000 - 00:00:00 CDT