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

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


--