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 Sat Apr 29 2000 - 00:00:00 CDT
