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
------------------
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 查詢發現由於並行引起的查詢結果非常多
To Bottom
