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

Modified:23-Nov-2010

Type:PROBLEM

Status:PUBLISHED

Priority:3

Comments (0)

In this Document
  Symptoms
  Cause
  Solution

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 |            |  
--------------------------------------------------------------------------

注：在MOS 查询发现由于并行引起的查询结果非常多

ITPub博客

[ORACLE BUG]查询结果错误--临时表并行

Applies to:

Symptoms

最新文章