Home » SQL & PL/SQL » SQL & PL/SQL » please help to optimize my sql
please help to optimize my sql [message #397664] Mon, 13 April 2009 03:56 Go to next message
didxga
Messages: 2
Registered: April 2009
Location: ChengDu
Junior Member

I was recently assigned a task to maintain an old system,which is related to telecom domain. In the process i need to optimize a SELECT sql which is as this:


--------------------------------------------------------------------------------

SELECT *
FROM (SELECT T1.ID AS ID,
T3.FULL_NAME AS PORT_FULL_NAME,
T1.CODE AS CODE,
T4.FULL_NAME AS FULL_NAME,
T2.LCODE AS LCODE,
T1.RATE AS RATE,
T1.DEVICE_ID AS DEVICE_ID
FROM TN_MULT_UNIT T1,
TD_DEVICE T2,
TD_PORT T3,
TG_RES_NAMING T4,
TN_MULT_UNIT T5
WHERE T1.DEVICE_ID = T2.ID(+)
AND T2.ID = T4.RES_ID
AND T1.STATUS = 'Free'
AND T1.Parent_Id = T5.id
AND T1.UNIT_TYPE = 'ExTime'
AND T1.RATE = '2M'
AND T1.SUBPART_ID = T3.ID
AND T3.SPECIAL = 'Exchange'
AND T3.PORT_TYPE = 'SwitchRelay'
UNION
SELECT T1.ID AS ID,
T3.FULL_NAME AS PORT_FULL_NAME,
T1.CODE AS CODE,
T4.FULL_NAME AS FULL_NAME,
T2.LCODE AS LCODE,
T1.RATE AS RATE,
T1.DEVICE_ID AS DEVICE_ID
FROM TN_MULT_UNIT T1, TD_DEVICE T2, TD_PORT T3, TG_RES_NAMING T4
WHERE T1.DEVICE_ID = T2.ID(+)
AND T2.ID = T4.RES_ID
AND T1.STATUS = 'Free'
AND T1.UNIT_TYPE = 'LogicPort'
AND T1.RATE IN ('2M', '155M', '34M')
AND T1.SUBPART_ID = T3.ID
AND T3.SPECIAL = 'Exchange'
AND T3.PORT_TYPE = 'SwitchRelay') T

--------------------------------------------------------------------------------

the sql statement above is to retrieve all Ports where its UNIT_TYPE is 'LogicPort' or 'ExTime'(exchange timeslot), with these two kind of port i need use additional different conditions respectively.so i choose to use UNION statement. but this sql is seem to be too slow to run.plz help me to find a more efficient way.
Thanks in advance!
Re: please help to optimize my sql [message #397674 is a reply to message #397664] Mon, 13 April 2009 06:15 Go to previous messageGo to next message
lraok
Messages: 5
Registered: April 2009
Location: Bangalore
Junior Member
Please use UNION ALL operator instead of UNION in your query to optimize it for a better performance.

-Lakshman
Re: please help to optimize my sql [message #397676 is a reply to message #397664] Mon, 13 April 2009 06:18 Go to previous messageGo to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
Try Left join instead of equi join.

Chintan
Re: please help to optimize my sql [message #397679 is a reply to message #397676] Mon, 13 April 2009 06:33 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hope this can help you ..

WITH main_q AS
     (
        SELECT t1.ID AS ID
              ,t3.full_name AS port_full_name
              ,t1.code AS code
              ,t4.full_name AS full_name
              ,t2.lcode AS lcode
              ,t1.rate AS rate
              ,t1.device_id AS device_id
              ,t1.unit_type
          FROM tn_mult_unit t1, td_device t2, td_port t3, tg_res_naming t4
         WHERE t1.device_id = t2.ID(+)
           AND t2.ID = t4.res_id
           AND t1.status = 'Free'
           AND t1.unit_type = ANY ('LogicPort', 'ExTime')
           AND t1.rate IN ('2M', '155M', '34M')
           AND t1.subpart_id = t3.ID
           AND t3.special = 'Exchange'
           AND t3.port_type = 'SwitchRelay')
SELECT ID
  FROM main_q
 WHERE t1.unit_type = 'LogicPort'
UNION ALL
SELECT ID
  FROM main_q a, tn_mult_unit b
 WHERE unit_type = 'ExTime' AND rate = '2M' AND a.parent_id = b.ID


Thanks
Trivendra

[Updated on: Mon, 13 April 2009 06:34]

Report message to a moderator

Re: please help to optimize my sql [message #397711 is a reply to message #397679] Mon, 13 April 2009 08:34 Go to previous message
didxga
Messages: 2
Registered: April 2009
Location: ChengDu
Junior Member

Nice done.
Thanks a lots everyone help me,Thank you ,Trivendra. it's really helpful!
Previous Topic: table too fragmented to build bitmap index (67182634,104,104)
Next Topic: Session browser access
Goto Forum:
  


Current Time: Sat Dec 10 18:17:42 CST 2016

Total time taken to generate the page: 0.27819 seconds