 SQL Tuning Urgent [message #265286] |
Thu, 06 September 2007 01:21  |
ronjubd
Messages: 2
Registered: August 2007
Location: Bangladesh
|
Junior Member |
|
|
Dears,
I have a query like bellow:
SELECT a.contrno, a.NAME contract_name, b.NAME subscriber_name, b.subno,a.id_type, a.id_no,
s.codetext_lang status,b.PREPOST_PAID,e.EQUIPID Package_Plan, c.IMSI_NUMBER, b.creation_date,b.CHANGED,
b.reservation_code, d.description, a.billgroup,
a.createdby,d.ADDRESS , d.ADDRESS2 , d.ADDRESS3, a.CATEGORY CCATEGORY,ccm.PARENT_CC_CATEGORY, ccm.CC_LIMIT,
------------------Newly added --------------------------------------------
(select sum(nvl(R.ar_am_loc,0)) past_due
from IVM_INVOICE_DETAIL R
where r.contrno=a.contrno
group by r.contrno) past_due,
(select unbilled from ARM_DUE_UNBILL_AMT where contrno=a.contrno) unbilled
-------------------Newly added --------------------------------------------
FROM crm_departement a,
crm_user_info b,
crm_commdevice c,
crm_retailer_info d,
som_tabs_codes s,
som_tabs_codes ss,
CRM_USER_EQUIPMENTS e,
crm_cust_addrs cc,
crm_address d
,(select c.USER_CODE USER_CODE, c.PARENT_CC_CATEGORY PARENT_CC_CATEGORY, cc.CC_LIMIT CC_LIMIT from CCM_USER_INFO c,
CCM_USER_INFO cc
where c.parent_entity!='NON'
and c.PARENT_ENTITY=cc.USER_CODE
and cc.USER_TYPE='CONT') ccm
WHERE a.contrno = b.contrno
AND b.cardno = c.cardno
AND b.reservation_code = d.retailer
AND s.codename = 'SUSTAT'
AND b.status = s.code
and ss.CODENAME = 'SPC_MAINSERV'
and b.SUBNO=e.SUBNO(+)
and e.EQUIPID = ss.CODE
and a.cust_no= cc.cust_no
and cc.address_id=d.address_id
AND trunc(B.CREATION_DATE) between to_date('01-09-07','DD-MM-YY') and to_date('06-09-07','DD-MM-YY')
and d.ADDRESS_TYPE='BILL'
and b.SUBNO=substr(ccm.user_code,6,10)
--and b.contrno=ccm.user_code
--and ccm.parent_entity!='NON'
--and ccm.user_type ='CONT'
and b.PREPOST_PAID='POST'
ORDER BY b.creation_date,a.CONTRNO
/
It is too slow and i believe it can be tuned more. Please advice how can i tune this one?
The Explain plan is here...
Thanks in advance
|
|
|
|
|
|
| Re: SQL Tuning Urgent [message #265359 is a reply to message #265320] |
Thu, 06 September 2007 03:07  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
What is the fascination with scalar sub-queries in the SELECT clause? Everybody seems to be using them without any comprehension of the damage they have on performance.
Don't use sub-queries in the SELECT clause. EVER! That's not quite true - there are rare cases where they're helpful - but you need the experience to spot those cases.
When you JOIN tables in the FROM clause, Oracle has the option to either NEST or UNNEST the lookup on that table/view/inline view. When you place sub-queries in the SELECT clause, Oracle has no choice - it must NEST. Nesting means that the sub-query is run once for every row returned. Unnesting means it is run once - period.
Ross Leishman
|
|
|
|
|
|
| Re: SQL Tuning Urgent [message #265415 is a reply to message #265388] |
Thu, 06 September 2007 05:04 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Which bit is not clear?
Pretend that scalar sub-queries in the SELECT clause are not permitted. How would you write your SQL now?
Ross Leishman
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
