| Tuning of query (Not DBA Level Tuning) [message #268558] |
Wed, 19 September 2007 01:38  |
nawshot
Messages: 12
Registered: May 2007
|
Junior Member |
|
|
hi,
i got a query regarding how to tune this for least possible execution time.i want to make it sure that i don't have DBA rights.
Other things are tables 'Plan1_Call_Detail' and 'Plan2_Call_Detail' are very big ones around 500,000 records are inserted daily and takes lot of time.These tables have an index on DAY column.
This report shows mobile_numbers which have activated with $20 but not used i.e no calls within 60 to 30 days i.e. person activated on 1st of month have 60 days and last day of a month have 30 day limit.
The SQl is as follows
select ad.mobile_number,e.equipment_name,ad.activation_date from
top_ups t,
additons ad,
equipment_models e
where
ad.activation_date>=to_date('01-Jun-07')
and ad.activation_date<to_date('30-Jun-07')+1
and t.mobile_number=ad.mobile_number
and ad.addition_equipment=e.equip_model_seq
and t.top_up_amount=20
and ad.retailer_id = '12345'
and ad.report_retailer_id not in ('-777','-888','-999','-444','-333','-222','-666')
and not exists (select 1 from Plan1_Call_Detail cd
where day>=to_date('01-Jun-07')
and day<to_date('30-Jun-07')+31
and cd.account_id=ad.account_id
and cd.call_type in ('SMS','VOICE')
union all
select 1 from Plan2_Call_Detail cd
where day>=to_date('01-Jun-07')
and day<to_date('30-Jun-07')+31
and cd.account_id=ad.account_id
and cd.call_type in ('SMS','VOICE')
)
group by ad.mobile_number,e.equipment_name,ad.activation_date
having
trunc(ad.activation_date)=trunc(min(t.Topup_pymt_created));
pls do help me as i am new to sql.
thanks and regards,
naw
|
|
|
|
| Re: Tuning of query (Not DBA Level Tuning) [message #268560 is a reply to message #268558] |
Wed, 19 September 2007 01:43  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If you start on tuning queries, the think you should NOT do is replace variables by constants.
For examples the dates and the ids you replaced by constants, I bet you these are variables in the actual query. Replacing them by constants and then tuning the query will lead to COMPLETELY different outcomes.
And a to_date takes two arguments: the date in string-notation with four digits for the year (didn't you learn from the Y2K problem??) and the date-format.
Would it not be more logical for a phone-application to add the account_id to the index??
[Updated on: Wed, 19 September 2007 01:45] Report message to a moderator |
|
|
|
| Re: Tuning of query (Not DBA Level Tuning) [message #268621 is a reply to message #268560] |
Wed, 19 September 2007 03:21 |
nawshot
Messages: 12
Registered: May 2007
|
Junior Member |
|
|
thanks for ur reply frank.
yes ur right the are variables but i removed them just for understanding purpose.
regarding to_date i dint get u. whether the year part should be of 4 digit r of 2 digit. please be clear.
yes there is index on account_id also . sry i forgot to mention it.
regards,
naw
|
|
|
|
| Re: Tuning of query (Not DBA Level Tuning) [message #268681 is a reply to message #268621] |
Wed, 19 September 2007 06:19 |
nawshot
Messages: 12
Registered: May 2007
|
Junior Member |
|
|
Here is the explain plan for it:
TABLE ACCESS(FULL) TOP_UPS Cost:1222206 Cardinality:109876997 Bytes:2637047928
INDEX(RANGE SCAN) PLAN1_CALL_DETAIL_PK Cost:3757832 Cardinality: 3948622 Bytes: 276403540
INDEX(RANGE SCAN) PLAN2_CALL_DETAIL_PK Cost:1736684 Cardinality: 1142674 Bytes: 79987180
And also there is index on Top_ups table i.e. on columns topup_pymt_created,account_id,ref_row_id combinely.but they are not getting used, even if i include them i.e. changed code is
T.ACCNT_ID IS NOT NULL
AND T.PYMT_CREATED>=TO_DATE('01-JUN-07')
AND T.REF_ROW_ID IS NOT NULL
select ad.mobile_number,e.equipment_name,ad.activation_date from
top_ups t,
additons ad,
equipment_models e
where
T.ACCouNT_ID IS NOT NULL
AND T.topup_PYMT_CREATED>=TO_DATE('01-JUN-07')
AND T.REF_ROW_ID IS NOT NULL
and ad.activation_date>=to_date('01-Jun-07')
and ad.activation_date<to_date('30-Jun-07')+1
and t.mobile_number=ad.mobile_number
and ad.addition_equipment=e.equip_model_seq
and t.top_up_amount=20
and ad.retailer_id = '12345'
and ad.report_retailer_id not in ('-777','-888','-999','-444','-333','-222','-666')
and not exists (select 1 from Plan1_Call_Detail cd
where day>=to_date('01-Jun-07')
and day<to_date('30-Jun-07')+31
and cd.account_id=ad.account_id
and cd.call_type in ('SMS','VOICE')
union all
select 1 from Plan2_Call_Detail cd
where day>=to_date('01-Jun-07')
and day<to_date('30-Jun-07')+31
and cd.account_id=ad.account_id
and cd.call_type in ('SMS','VOICE')
)
group by ad.mobile_number,e.equipment_name,ad.activation_date
having
trunc(ad.activation_date)=trunc(min(t.Topup_pymt_created));
pls help me out
thanks and regards,
naw
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
