SQL code with connect by -- running too slow
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL code with connect by -- running too slow

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    SQL code with connect by -- running too slow

    All, I really need help on this.

    Here is my sql code. This code is taking too much time. I mean, this sql code is running inside the cursor for loop. For loop is looping 6500 times. I am using bulk insert on for loop.It is talking 6 hours to complete the job. I wanted to reduce the time. Can any expert can take a look at this select statment.

    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
    PL/SQL Release 9.2.0.6.0 - Production
    CORE 9.2.0.6.0 Production
    TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
    NLSRTL Version 9.2.0.6.0 - Production



    select distinct
    null csi_material_dim_sid,
    a.solution_cd,
    b.solution_desc solution_desc,
    a.capability_cd,
    c.capability_desc capability_desc,
    a.resource_cd,
    d.resource_desc resource_desc,
    a.source_system
    from
    (select
    ind,solution_cd,capability_cd,resource_cd,
    doc_item_num,doc_higher_lvl_item_num,source_system from (
    select
    level ind,
    solution_cd,
    (case when level=2 then capability_cd else prior capability_cd end)
    capability_cd,
    (case when level=3 then resource_cd else prior resource_cd end) resource_cd,
    doc_item_num,
    doc_higher_lvl_item_num,
    source_system
    from
    (
    select
    ind,
    solution_cd ,
    capability_cd,
    resource_cd,doc_item_num,
    doc_higher_lvl_item_num,
    doc_ref_matl_num,
    source_system
    from
    (select
    level ind,
    case when level=1 then doc_matl_num else doc_ref_matl_num end solution_cd,
    case when level=2 then doc_matl_num end capability_cd,
    case when level>2 then doc_matl_num end resource_cd,
    doc_item_num,
    doc_higher_lvl_item_num,
    doc_ref_matl_num,
    source_system
    from
    (select /*+ parallel(csi_mat_stg,6) */
    doc_matl_num,doc_item_num,doc_higher_lvl_item_num,doc_ref_matl_num,
    source_system
    from csi_mat_stg
    where doc_num ='xxxxxxxxx'
    )
    start with doc_higher_lvl_item_num=0
    connect by prior doc_item_num = doc_higher_lvl_item_num)
    )
    connect by prior doc_item_num=doc_higher_lvl_item_num)
    where resource_cd is not null and capability_cd is not null) a,
    csi_material_master b,csi_material_master c,csi_material_master d
    where
    a.SOLUTION_CD=b.solution_cd and
    a.capability_cd=c.capability_cd and
    a.resource_cd=d.resource_cd

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you have indexes on the joined on columns?
    Particulary columns used in the "connect by prior" clause.

    Tamil

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    I do have index. But no luck.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by pranavgovind
    I do have index. But no luck.
    What does the tkprof output say?

    Tamil

  5. #5
    Join Date
    Dec 2005
    Posts
    195
    I added index on doc_num. My problem resolved. thank you.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by pranavgovind
    I added index on doc_num. My problem resolved. thank you.
    I believe you created index on doc_item_num col.

    Tamil

  7. #7
    Join Date
    Dec 2005
    Posts
    195
    Yes. I did create index on doc_num & doc_item_num. Basically performance improved due to bulk insert and this indexes... It was taking 6 hours. After this indexes, bulk insert, it takes 2 min.

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