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

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

Modified:23-Nov-2010Type:PROBLEMStatus:PUBLISHEDPriority:3
There are no commentsComments (0)Rate this documentEmail link to this documentOpen document in new windowPrintable Page

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 查询发现由于并行引起的查询结果非常多
请使用浏览器的分享功能分享到微信等