Home » RDBMS Server » Performance Tuning » URGENT:-Query taking more than 14 hrs.
| URGENT:-Query taking more than 14 hrs. [message #65535] |
Tue, 19 October 2004 04:31  |
Milind Deshpande
Messages: 93
Registered: May 2004
|
Member |
|
|
Hi All,
I have this query which is taking approximately 14 hrs. to execute. I have rewritten the SQL to my knowledge.
Can anybody recommend anything else that how can this query be rewritten in order to respond in minutes instead of hours.
select count(*)
from TCOMMUNICATION_LOG l
where (L.COMP_REP_ID_C is null or exists
(select 'x'
from TCOMP_REP R, TACCOUNT ACCT, TORGANIZATION ORG
where R.COMP_REP_ID_C = L.COMP_REP_ID_C and
R.SVC_C in
('000020', '000021', '000056', '000356', '000057', '000048',
'000046', '008203', '000148', '008201', '000228', '000223',
'000047', '000054', '000049', '008204', '000154', '008202',
'000229', '000227', '000053', '000560', '000160', '000161') and
ACCT.ORG_ID_C = R.ORG_ID_C and
ACCT.ORG_NME_SEQ_C = R.ORG_NME_SEQ_C and
ORG.ORG_ID_C = ACCT.ORG_ID_C and
ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C and
ACCT.ACCT_TYP_C = 'COMP' and
ACCT.ACCT_STAT_C NOT IN ('POTENL', 'ACTWSB') AND
ORG.JURIS_ID_C <> '70001')) and
((L.ORG_ID_C is null and L.ORG_NME_SEQ_C is null) or exists
(select 'x'
from TACCOUNT ACCT, TORGANIZATION ORG
where ACCT.ORG_ID_C = L.ORG_ID_C and
ACCT.ORG_NME_SEQ_C = L.ORG_NME_SEQ_C and
ORG.ORG_ID_C = ACCT.ORG_ID_C and -- exe time. 52263.859
ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C and -- no of rec :- 2312033
ACCT.ACCT_TYP_C = 'COMP' and
ACCT.ACCT_STAT_C NOT IN ('POTENL', 'ACTWSB') and
ORG.JURIS_ID_C <> '70001' and
(select count(R.COMP_REP_ID_C)
from TCOMP_REP R
where R.ORG_ID_C = ACCT.ORG_ID_C and
R.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C) > 0)) and
L.JURIS_ID_C <> '70001'
Pls. help me on this.
Thanks in Advance.
Milind.
|
|
|
|
| Re: URGENT:-Query taking more than 14 hrs. [message #65536 is a reply to message #65535] |
Tue, 19 October 2004 07:51  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Get rid of the select count at the end. Replace it with a where exists (select null
Further, this query has to do a Full Table Scan on TCOMMUNICATION_LOG because of the is null clauses (can't be indexed). If this table is big, tough luck.
Did this query ever perform well on the same amount of data (is it at all possible to get a result in 'minutes instead of hours') or did you just write it and hope it would perform...
|
|
|
|
| Re: URGENT:-Query taking more than 14 hrs. [message #65537 is a reply to message #65536] |
Tue, 19 October 2004 21:30 |
Milind Deshpande
Messages: 93
Registered: May 2004
|
Member |
|
|
Hi Frank,
Thanks for your Reply.I really hoped it would perform.I will try the suggestions given by you.
I need your help on one more query.I came to knoe that you should rewrite the query instead of outer join with UNION's. Can you help me in rewriting this query with UNION's instead of Outer join or any other suggestions from your side.I am new to SQL statement Tuning and I dont have any idea how to write Optimised SQLs. Pls. help me on this.
select
/*+ LEADING(STM) */
stm.service_team_member_id EmployeeId,
decode((stm.full_name), '', '--',(stm.full_name)) UserName,
stm.status Status,
nvl(st.city, '--') City,
nvl(st.name, '--') Team,
nvl(st.team_name, '--') TeamType,
nvl(ug.user_group, '--') UserGroup
from
av_service_team_member stm,
av_service_team st,
av_service_team_member_keys stmk,
av_internal_user_group ug
where
(stm.full_name) like ('Alfred Esposito Jr') and
stm.service_team_id = st.service_team_id(+) and
stm.service_team_member_id = stmk.service_team_member_id(+) and
stmk.oneworld_id = ug.oneworld_id(+) and
stm.status = 'Active' and
stm.service_team_member_id not in(select employee_id from arv_security_role_grant where sec_role_id = ('6000000087')) order by stm.full_name
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 23:59:13 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
