-
PARALLEL table with outer join
We have a problem with parralell table with outer join .
SQL> select * from v$version
2 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
create table foo_ACCOUNT(ACC_ACCOUNT_ID number, ACC_NAME varchar2(60))
PARALLEL ( DEGREE DEFAULT INSTANCES 1 );
insert into foo_ACCOUNT values ( 100, 'ACC 100');
insert into foo_ACCOUNT values ( 200, 'ACC 200');
insert into foo_ACCOUNT values ( 300, 'ACC 300');
insert into foo_ACCOUNT values ( 400, 'ACC 400');
insert into foo_ACCOUNT values ( 500, 'ACC 500');
insert into foo_ACCOUNT values ( 600, 'ACC 600');
create table foo_PROFILE_ELIGIBILITY(PRE_ACCOUNT_ID number, PRE_ACCOUNT_ID1 number, PRE_DESC
varchar2(60))
PARALLEL ( DEGREE DEFAULT INSTANCES 1 );
insert into foo_PROFILE_ELIGIBILITY values ( 200, 200, 'PROF ELIG 200');
insert into foo_PROFILE_ELIGIBILITY values ( 300, 300, 'PROF ELIG 300');
insert into foo_PROFILE_ELIGIBILITY values ( 400, 400, 'PROF ELIG 400');
create table foo_NAMEINFO(NAM_ACCOUNT_ID number, NAM_ACCOUNT_ID1 number, NAM_REC_ID number
, NAM_CONTACT_TYPE varchar2(60), NAM_DESC varchar2(60))
PARALLEL ( DEGREE DEFAULT INSTANCES 1 );
insert into foo_NAMEINFO values ( 100, 100, 110, 'ACCOUNT HOLDER', 'NAMEINFO 100');
insert into foo_NAMEINFO values ( 300, 300, 330, 'ACCOUNT HOLDEr', 'NAMEINFO 300');
insert into foo_NAMEINFO values ( 500, 500, 550, 'ACCOUNT hhOLDER', 'NAMEINFO 500');
commit;
Join 2 tables:
1 select t1.ACC_ACCOUNT_ID ,t2.PRE_ACCOUNT_ID
2 from foo_ACCOUNT t1, foo_PROFILE_ELIGIBILITY t2
3* where t1.ACC_ACCOUNT_ID = t2.PRE_ACCOUNT_ID(+)
SQL> /
ACC_ACCOUNT_ID PRE_ACCOUNT_ID
-------------- --------------
300 300
100
500
600
200 200
400 400
6 rows selected.
Join 3 tables:
SQL> l
1 select t1.ACC_ACCOUNT_ID ,t2.PRE_ACCOUNT_ID,t3.NAM_ACCOUNT_ID
2 from foo_ACCOUNT t1, foo_PROFILE_ELIGIBILITY t2
3 , foo_NAMEINFO t3
4 where t1.ACC_ACCOUNT_ID = t2.PRE_ACCOUNT_ID(+)
5* and t2.PRE_ACCOUNT_ID = t3.NAM_ACCOUNT_ID(+)
SQL> /
ACC_ACCOUNT_ID PRE_ACCOUNT_ID NAM_ACCOUNT_ID
-------------- -------------- --------------
300 300 300
100
500
600
200 200
400 400
6 rows selected.
Now change and t2.PRE_ACCOUNT_ID = t3.NAM_ACCOUNT_ID(+) to and t2.PRE_ACCOUNT_ID1 =
t3.NAM_ACCOUNT_ID(+)
SQL> 5
5* and t2.PRE_ACCOUNT_ID = t3.NAM_ACCOUNT_ID(+)
SQL> c/ID/ID1
5* and t2.PRE_ACCOUNT_ID1 = t3.NAM_ACCOUNT_ID(+)
SQL> l
1 select t1.ACC_ACCOUNT_ID ,t2.PRE_ACCOUNT_ID,t3.NAM_ACCOUNT_ID
2 from foo_ACCOUNT t1, foo_PROFILE_ELIGIBILITY t2
3 , foo_NAMEINFO t3
4 where t1.ACC_ACCOUNT_ID = t2.PRE_ACCOUNT_ID(+)
5* and t2.PRE_ACCOUNT_ID1 = t3.NAM_ACCOUNT_ID(+)
SQL> /
ACC_ACCOUNT_ID PRE_ACCOUNT_ID NAM_ACCOUNT_ID
-------------- -------------- --------------
300 300 300
200 200
400 400
SQL> should display "3 rows selected" here
Only 3 rows return . Should 6 rows return as previous SQL ?
Also it does not display 3 rows selected either.
Query return 6 rows when adding hint /*+ use_nl (t3) */
add hint
SQL> l
1 select /*+ use_nl(t3) */ 2 t1.ACC_ACCOUNT_ID ,t2.PRE_ACCOUNT_ID,t3.NAM_ACCOUNT_ID
3 from foo_ACCOUNT t1, foo_PROFILE_ELIGIBILITY t2
4 , foo_NAMEINFO t3
5 where t1.ACC_ACCOUNT_ID = t2.PRE_ACCOUNT_ID(+)
6* and t2.PRE_ACCOUNT_ID1 = t3.NAM_ACCOUNT_ID(+)
SQL> /
ACC_ACCOUNT_ID PRE_ACCOUNT_ID NAM_ACCOUNT_ID
-------------- -------------- --------------
100
500
600
200 200
400 400
300 300 300
6 rows selected.
Does anyone know why it is so?
-
Hi
I am unable to reproduce
Code:
SQL> select t1.ACC_ACCOUNT_ID ,t2.PRE_ACCOUNT_ID
2 from foo_ACCOUNT t1, foo_PROFILE_ELIGIBILITY t2
3 where t1.ACC_ACCOUNT_ID = t2.PRE_ACCOUNT_ID(+)
4 /
ACC_ACCOUNT_ID PRE_ACCOUNT_ID
-------------- --------------
200 200
300 300
400 400
100
600
500
6 rows selected.
SQL>
SQL> select t1.ACC_ACCOUNT_ID ,t2.PRE_ACCOUNT_ID,t3.NAM_ACCOUNT_ID
2 from foo_ACCOUNT t1, foo_PROFILE_ELIGIBILITY t2
3 , foo_NAMEINFO t3
4 where t1.ACC_ACCOUNT_ID = t2.PRE_ACCOUNT_ID(+)
5 and t2.PRE_ACCOUNT_ID = t3.NAM_ACCOUNT_ID(+)
6 /
ACC_ACCOUNT_ID PRE_ACCOUNT_ID NAM_ACCOUNT_ID
-------------- -------------- --------------
300 300 300
500
600
100
400 400
200 200
6 rows selected.
SQL>
SQL> select t1.ACC_ACCOUNT_ID ,t2.PRE_ACCOUNT_ID,t3.NAM_ACCOUNT_ID
2 from foo_ACCOUNT t1, foo_PROFILE_ELIGIBILITY t2 ,
3 foo_NAMEINFO t3
4 where t1.ACC_ACCOUNT_ID = t2.PRE_ACCOUNT_ID(+)
5 and t2.PRE_ACCOUNT_ID1 = t3.NAM_ACCOUNT_ID(+)
6 /
ACC_ACCOUNT_ID PRE_ACCOUNT_ID NAM_ACCOUNT_ID
-------------- -------------- --------------
300 300 300
500
600
100
400 400
200 200
6 rows selected.
But my version is
Code:
C:\>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 15 11:42:45 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Raise a TAR with oracle
-
only with PARALLEL table
Hi hrishy,
Thanh you for your reply.
The issue only applies to PARALLEL TABLE
create table xxx
.
.
.
PARALLEL ( DEGREE DEFAULT INSTANCES 1 );
Are your PARALLEL TABLE ?
-
parallel tables
Hi hrishy,
Please note that ALL my tables are created with PARALLEL option.
create table foo_ACCOUNT(ACC_ACCOUNT_ID number, ACC_NAME varchar2(60))
PARALLEL ( DEGREE DEFAULT INSTANCES 1 );
create table foo_PROFILE_ELIGIBILITY(PRE_ACCOUNT_ID number, PRE_ACCOUNT_ID1 number, PRE_DESC
varchar2(60))
PARALLEL ( DEGREE DEFAULT INSTANCES 1 );
create table foo_NAMEINFO(NAM_ACCOUNT_ID number, NAM_ACCOUNT_ID1 number, NAM_REC_ID number
, NAM_CONTACT_TYPE varchar2(60), NAM_DESC varchar2(60))
PARALLEL ( DEGREE DEFAULT INSTANCES 1 );
Anyone else ?? slimdave, jeff hunter, oracledoc just to name a few experts.
-
Hi
Yes i have used parallel 1 only difference is my version and i see if you have a reproducible test case then its time to raise a TAR with oracle support
They might ask you to apply the latest patch set .
Try applying the next patchset on a dev box and see if you can reproduce
regards
Hrishy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|