Wrong Results (or No Rows) with Full Partition-wise Join Between Reference Partitioned Tables (Doc ID 1439194.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.3 - Release: 11.2 to 11.2
Information in this document applies to any platform.

Symptoms

The symptoms are as follows:
  1. A join between two reference partitioned tables returns wrong results (or no rows).
  2. The parent table is composite partitioned.
  3. Other databases with the same RDBMS version, OS, and data model, but different data, may not have the issue.
  4. This was found to reproduce in 11.2.0.2 and 11.2.0.3.  Versions 11.2.0.1 and 11gR1 were not tested, but may have the same issue.  (Reference partitioning was not available prior to 11g).

Here is the testcase:

1. Create the composite-partitioned parent table named P

 CREATE TABLE P
 ( "INTERACTION_ID" NUMBER(15,0) NOT NULL ENABLE,
 "CREATION_DATE" DATE NOT NULL ENABLE,
 CONSTRAINT "P_PK" PRIMARY KEY ("INTERACTION_ID")
 )
 PARTITION BY RANGE ("CREATION_DATE")
 SUBPARTITION BY HASH ("INTERACTION_ID")
 SUBPARTITION TEMPLATE (
 SUBPARTITION "SP00001",
 SUBPARTITION "SP00002" )
 (PARTITION "OLDEST" VALUES LESS THAN (TO_DATE(' 2011-05-30 00:00:00',
 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
 PARTITION "P00001" VALUES LESS THAN (TO_DATE(' 2011-06-06 00:00:00',
 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

2. Create the a child reference-partitioned table named C (references the parent)

 CREATE TABLE C
 ( "SEGMENT_ID" NUMBER(15,0) NOT NULL ENABLE,
 "INTERACTION_ID" NUMBER(15,0) NOT NULL ENABLE,
 CONSTRAINT "C_PK" PRIMARY KEY ("SEGMENT_ID"),
 CONSTRAINT "C_FK" FOREIGN KEY ("INTERACTION_ID")
 REFERENCES P ("INTERACTION_ID") ENABLE
 )
 PARTITION BY REFERENCE ("C_FK")
 (PARTITION "OLDEST_SP00001" SEGMENT CREATION IMMEDIATE ,
 PARTITION "OLDEST_SP00002" SEGMENT CREATION IMMEDIATE,
 PARTITION "OLDEST_SP00003" SEGMENT CREATION IMMEDIATE,
 PARTITION "OLDEST_SP00004" SEGMENT CREATION IMMEDIATE); 

3.  Create another child reference-partitioned table named GC (doesn't directly reference the parent)

 CREATE TABLE GC
 ( "SEGMENT_ID" NUMBER(15,0) NOT NULL ENABLE,
 CONSTRAINT "GC_FK" FOREIGN KEY ("SEGMENT_ID")
 REFERENCES C ("SEGMENT_ID") ENABLE
 )
 PARTITION BY REFERENCE ("GC_FK")
 (PARTITION "OLDEST_SP00001" SEGMENT CREATION DEFERRED,
 PARTITION "OLDEST_SP00002" SEGMENT CREATION DEFERRED,
 PARTITION "OLDEST_SP00003" SEGMENT CREATION DEFERRED,
 PARTITION "OLDEST_SP00004" SEGMENT CREATION DEFERRED);

4.  Insert some data into the 3rd partiton:

  insert into P values(3, TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
insert into C values(13,3);
insert into GC values(13);

5.  This query should return 1 row, but instead returns none:

select *
from c, gc
where c.segment_id = gc.segment_id;

6.  Here is the incorrect explain plan.  Note that only 2 of the 4 partitions are accessed, which is why the row is missed.

 ******* INCORRECT PLAN ************* 
------------------------------------------------------------------------------
--------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
Time | Pstart| Pstop |
------------------------------------------------------------------------------
--------------------------
| 0 | SELECT STATEMENT | | 24589 | 936K| | 130 (10)|
00:00:02 | | |
| 1 | PARTITION REFERENCE ALL| | 24589 | 936K| | 130 (10)|
00:00:02 | 1 | 2 | <-----
|* 2 | HASH JOIN | | 24589 | 936K| 152K| 130 (10)|
00:00:02 | | |
| 3 | TABLE ACCESS FULL | GC | 24589 | 312K| | 88 (6)|
00:00:02 | 1 | 2 | <-----
| 4 | TABLE ACCESS FULL | C | 328 | 8528 | | 3 (0)|
00:00:01 | 1 | 2 | <-----
------------------------------------------------------------------------------

7.  Here is the correct explain plan.  Note that partitions 1-4 are accessed and return the correct results from partition 3.

******** CORRECT PLAN (giving right results) ******************
------------------------------------------------------------------------------
--------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
Time | Pstart| Pstop |
------------------------------------------------------------------------------
--------------------------
| 0 | SELECT STATEMENT | | 24589 | 936K| | 130 (10)|
00:00:02 | | |
| 1 | PARTITION REFERENCE ALL| | 24589 | 936K| | 130 (10)|
00:00:02 | 1 | 4 | <-----
|* 2 | HASH JOIN | | 24589 | 936K| 152K| 130 (10)|
00:00:02 | | |
| 3 | TABLE ACCESS FULL | GC | 24589 | 312K| | 88 (6)|
00:00:02 | 1 | 4 | <-----
| 4 | TABLE ACCESS FULL | C | 328 | 8528 | | 3 (0)|
00:00:01 | 1 | 4 | <-----
------------------------------------------------------------------------------


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms