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 Go to next message
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 #412912 is a reply to message #412905] Mon, 13 July 2009 06:48 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Read the sticky post in the Performance Tuning forum first, and follow those guides on how to trace a query / session to find the bottleneck.
Re: sql query tunning [message #412929 is a reply to message #412905] Mon, 13 July 2009 07:54 Go to previous message
joy_division
Messages: 4644
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.
Previous Topic: how do i write a procedure
Next Topic: Updation
Goto Forum:
  


Current Time: Sat Dec 10 04:55:53 CST 2016

Total time taken to generate the page: 0.05049 seconds