Wrong Results From Query Using Parallel Execution And Global Temporary Table(s) (Doc ID 457040.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

When executing a parallel query on a set of tables including one or more Global Temporary Tables (GTT) resulting in an execution plan that involves Nested Loop Joins against the GTTs, NULL values result in the column which are part of the GTTs.

Reproducing the issue is fairly simple:

drop table t1; 
drop table t2; 
drop table t3; 

create table t1(c1 varchar2(64 )); 
create table t2(c1 varchar2(64 ),c2 varchar2(1 )) parallel ; 
create global temporary table t3(c1 varchar2(64),c2 varchar2(1)) on 
commit preserve rows; 


insert into t1 values('2'); 
insert into t2 values('2', '0'); 
insert into t3 values('2', '0'); 
commit; 

alter session disable parallel query; 
select t3.rowid t_3_rowid from t2  inner join t1  on t1.c1 = t2.c1 
  left outer join t3 on t2.c1 = t3.c1 and (t2.c2 = 2 or t3.c2 = t2.c2); 
  
T_3_ROWID  
------------------  
AAQAAJAABAAAAAKAAA 

alter session enable parallel query; 

select t3.rowid t_3_rowid from t2  inner join t1  on t1.c1 = t2.c1 
  left outer join t3 on t2.c1 = t3.c1 and (t2.c2 = 2 or t3.c2 = t2.c2); 

T_3_ROWID  
------------------  
                  <---- NULL value returned              

The resulting parallel execution plan shows that the GTT is scanned by a parallel slave in a NLJ:

-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT         |          |        |      |            | 
|   1 |  PX COORDINATOR          |          |        |      |            | 
|   2 |   PX SEND QC (RANDOM)    | :TQ10002 |  Q1,02 | P->S | QC (RAND)  | 
|   3 |    NESTED LOOPS OUTER    |          |  Q1,02 | PCWP |            | 
|*  4 |     HASH JOIN            |          |  Q1,02 | PCWP |            | 
|   5 |      PX RECEIVE          |          |  Q1,02 | PCWP |            | 
|   6 |       PX SEND HASH       | :TQ10001 |  Q1,01 | P->P | HASH       | 
|   7 |        PX BLOCK ITERATOR |          |  Q1,01 | PCWC |            | 
|   8 |         TABLE ACCESS FULL| T2       |  Q1,01 | PCWP |            | 
|   9 |      BUFFER SORT         |          |  Q1,02 | PCWC |            | 
|  10 |       PX RECEIVE         |          |  Q1,02 | PCWP |            | 
|  11 |        PX SEND HASH      | :TQ10000 |        | S->P | HASH       | 
|  12 |         TABLE ACCESS FULL| T1       |        |      |            | 
|  13 |     VIEW                 |          |  Q1,02 | PCWP |            | 
|* 14 |      TABLE ACCESS FULL   | T3       |  Q1,02 | PCWP |            | 
--------------------------------------------------------------------------

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