MySQL Forums
Forum List  »  Oracle

help me tune the query
Posted by: prakash gr
Date: April 03, 2009 03:30AM

Hi,

I need to tune the following query, please do help me.


select a.id , call_id, logged_date, account_number, process_code, sub_process_code, customer_name, cust_type,
count(distinct b.history_id) as repeatcalls, ifnull(d.cust_address,a.address)as cust_address, a.area, city,
node_name, soc_desc,soc_id, complaint, agent_reply, agent_comments, phone, mobile, assignto, cstatus,lco_name,
max(e.iqara_id) as iqara_id, action_taken, rem_desc, CASE WHEN cstatus = 'Pending' THEN
TIMESTAMPDIFF(MINUTE,a.logged_date,now()) WHEN cstatus = 'Cleared' THEN
TIMESTAMPDIFF(MINUTE,a.logged_date,a.cleared_datetime) END as ageing_mins from
new_call a left join history b on a.id = b.id left join modem c on a.account_number = c.cust_acct_id
left join userdetail d on a.account_number = d.account_no left join history_iqara e on a.id=e.id left join
compsource_m f on a.comp_source = f.soc_id left join co_ordinator_remarks_m g on e.rem_no = g.rem_no where city in
('Bangalore') and process_code in (3) and lower(cstatus)='Pending' group by a.id, call_id, logged_date, account_number,
process_code, sub_process_code, customer_name, cust_type, ifnull(d.cust_address,a.address), a.area, city, node_name,
soc_desc,soc_id, complaint, agent_reply, agent_comments, phone, mobile, assignto,lco_name,iqara_id, action_taken,
rem_desc, cstatus, cleared_datetime having e.iqara_id = (select max(iqara_id) from history_iqara where id = a.id) or
(e.iqara_id is null) order by logged_date desc


explain plan:

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3186623
filtered: 100.00
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: b
type: ref
possible_keys: IX_HISTORY1,IX_ID
key: IX_HISTORY1
key_len: 9
ref: agentlog.a.id
rows: 1
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: c
type: ref
possible_keys: IDX_CUST_ACCT_ID
key: IDX_CUST_ACCT_ID
key_len: 4
ref: agentlog.a.account_number
rows: 1
filtered: 100.00
Extra:
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: d
type: ref
possible_keys: IDX_Account_NO
key: IDX_Account_NO
key_len: 453
ref: agentlog.a.account_number
rows: 1
filtered: 100.00
Extra:
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: e
type: ref
possible_keys: idx_id_logdate,history_iqara14,IX_ID
key: idx_id_logdate
key_len: 9
ref: agentlog.a.id
rows: 1
filtered: 100.00
Extra:
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: f
type: eq_ref
possible_keys: IX_SOCID
key: IX_SOCID
key_len: 4
ref: agentlog.a.comp_source
rows: 1
filtered: 100.00
Extra:
*************************** 7. row ***************************
id: 1
select_type: PRIMARY
table: g
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: agentlog.e.rem_no
rows: 1
filtered: 100.00
Extra:
*************************** 8. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: history_iqara
type: ref
possible_keys: idx_id_logdate,history_iqara14,IX_ID
key: idx_id_logdate
key_len: 9
ref: func
rows: 1
filtered: 100.00
Extra: Using where; Using index
8 rows in set, 4 warnings (0.00 sec)


Thanks

Prakash GR

Options: ReplyQuote


Subject
Views
Written By
Posted
help me tune the query
3778
April 03, 2009 03:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.