Home » SQL & PL/SQL » SQL & PL/SQL » sql query tunning (oracle 9.0.2. and window xp)
sql query tunning [message #412905] |
Mon, 13 July 2009 06:21  |
lala_bcs
Messages: 4 Registered: January 2007 Location: N.W.F.P PAKISTAN
|
Junior Member |
|
|
i have the following queries when i run these every time from my system mean remotely to the db server. it will take long time.so my manager tell me to tune these queries .. so how can i tune these quries if any one help me ... on the tunning of these queries... pleae response me in as soon as possible ...
the queries are as follow........
---
insert into TABS_POSTPAID
select subno,prepost_paid from crm_user_info@HLR2TABS where prepost_paid='POST'and condate <='06-Jul-2009';
commit ;
--------------------------- reports
-- Prepaid in tabs and NOT IN
select subno,prepost_paid from crm_user_info@HLR2TABS
where subno in (select substr (msisdn,5,7) from HLR_POSTPAID) and prepost_paid='PREP'
and subno not in (select substr(msisdn,3,7) from sdp_image_2101 where SERVICECLASS !=230)
-- Prepaid in IN and PostPaid in HLR
select msisdn,'PREP' from sdp_image_2101
where MSISDN in (select substr(msisdn,3,9) from HLR_DMP_FULL WHERE OICK =0) AND SERVICECLASS !=230
-- Postpaid and in IN
select * from HLR_POSTPAID where msisdn in (select substr(msisdn,5,7) from sdp_image_2101 WHERE SERVICECLASS !=230)
create table hlr_not_in as
select substr(msisdn,3,9)msisdn ,0 exist from HLR_DMP_FULL WHERE OICK !=0
and
substr(msisdn,3,9) not in (select msisdn from sdp_image_2101 WHERE SERVICECLASS !=230)
--Update HLR_DMP_FULL a set existin=(select 1 from sdp_image_2101 b where substr(a.msisdn,3,9)=b.msisdn) WHERE OICK !=0
-- IN SDP1 on hlr and not in sdp1 on IN
select a.msisdn,sdp,oick from sdp_image_2101 a,HLR_DMP_FULL b
where SDP=1 and a.MSISDN= substr(b.msisdn,3,9) and OICK <>10 AND SERVICECLASS !=230
-- IN SDP2 on hlr and not in sdp2 on IN
select a.msisdn,sdp,oick from sdp_image_2101 a,HLR_DMP_FULL b
where SDP=2 and a.MSISDN= substr(b.msisdn,3,9) and OICK <>320 AND SERVICECLASS !=230
-- IN SDP3 on hlr and not in sdp3 on IN
select a.msisdn,sdp,oick from sdp_image_2101 a,HLR_DMP_FULL b
where SDP=3 and a.MSISDN= substr(b.msisdn,3,9) and OICK <>420 AND SERVICECLASS !=230
-- IN SDP4 on hlr and not in sdp4 on IN
select a.msisdn,sdp,oick from sdp_image_2101 a,HLR_DMP_FULL b
where SDP=4 and a.MSISDN= substr(b.msisdn,3,9) and OICK <>520 AND SERVICECLASS !=230
-- IN S hlr and not in sdp5 on IN
select a.msisdn,sdp,oick from sdp_image_2101 a,HLR_DMP_FULL b
where SDP=5 and a.MSISDN= substr(b.msisdn,3,9) and OICK <>620 AND SERVICECLASS !=230
-- IN S
select a.msisdn,sdp,oick from sdp_image_2101 a,HLR_DMP_FULL b
where a.MSISDN= substr(b.msisdn,3,9) and oick in ('440','340') AND SERVICECLASS !=230
---- HLR
select * from sdp_image_2101 where msisdn in (
select substr(msisdn,3,9) from HLR_DMP_FULL WHERE OICK =0 and
substr(msisdn,5,7) in (select subno from TABS_POSTPAID)) AND SERVICECLASS !=230
|
|
|
|
Re: sql query tunning [message #412929 is a reply to message #412905] |
Mon, 13 July 2009 07:54  |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
lala_bcs wrote on Mon, 13 July 2009 07:21 | i
insert into TABS_POSTPAID
select subno,prepost_paid from crm_user_info@HLR2TABS where prepost_paid='POST'and condate <='06-Jul-2009';
|
The first thing you want to do is use DATEs properly with a TO_DATE function. You are comparing and DATE column to a STRING, which is implicit conversion and plain sloppy.
|
|
|
Goto Forum:
Current Time: Tue Feb 11 03:42:20 CST 2025
|