-
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
-
Do you have indexes on the joined on columns?
Particulary columns used in the "connect by prior" clause.
Tamil
-
I do have index. But no luck.
-
Originally Posted by pranavgovind
I do have index. But no luck.
What does the tkprof output say?
Tamil
-
I added index on doc_num. My problem resolved. thank you.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|