DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: PARALLEL table with outer join

  1. #1
    Join Date
    Dec 2000
    Posts
    126

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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Dec 2000
    Posts
    126

    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 ?

  4. #4
    Join Date
    Dec 2000
    Posts
    126

    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.

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    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
  •  


Click Here to Expand Forum to Full Width