Home » SQL & PL/SQL » SQL & PL/SQL » Optimizing help appreciated
Optimizing help appreciated [message #248832] Mon, 02 July 2007 04:35 Go to next message
patber
Messages: 9
Registered: May 2007
Junior Member
Hi,
Have this query and would really appreciate to get some tips and trick how to speed it up a bit, thinks to think about and so on.

SELECT ac.org_id,
	   tsp.serial_no,
	   tsp.objid servic_id,
   	   tsp.instance_name service_type,
	   tsp.part_status service_status,
	   acc.objid access_id,
	   acc.instance_name access_type,
	   acc.part_status access_status,
	   pc.objid pc_id,
	   pc.instance_name pc_type,
	   pc.x_data_quality,
	   pc.x_recurring_cost_end_date
FROM sa.table_bus_org_for_ke@osclfp ac,--ACCOUNT INFORMATION
	 table_bus_site_role@osclfp tbsr,--LINK BETWEEN ACCOUNT INFORMATION AND SITES
	 table_site@osclfp ts,--SITES
	 table_site_part@osclfp tsp,--SERVICE
	 table_site_part@osclfp acc,--ACCESS
 	 table_site_part@osclfp pc,--PROVIDER CONNECTION
	 table_prt_prt_role@osclfp tppr,--LINK BETWEEN SERVICES AND ACCESSES
	 table_sit_prt_role@osclfp tspr--LINK BETWEEN SITES AND SERVICES
WHERE ac.org_id = '1346842' --IN (SELECT account_id FROM smat_accounts_initial) 
AND tbsr.bus_site_role2bus_org = ac.objid
AND tbsr.bus_site_role2site = ts.objid
AND tspr.prt_role2site = ts.objid
AND tspr.prt_role2site_part = tsp.objid
AND tbsr.role_name = 'Equipment'
AND tsp.part_status IN ('Deinstalled','To be Deinstalled','Cancelled','To be Cancelled')--1.1.1
AND (tsp.instance_name LIKE 'Indirect Telephony%'
 AND NOT EXISTS (SELECT *
                 FROM table_fa_site_part@osclfp,
                      subsequip@prop 
                 WHERE fa_site_part2site_part = ts.objid
                 AND attribute_name = 'IA-number'
                 AND area = SUBSTR(attribute_value,1,4)
                 AND subno = SUBSTR(attribute_value,6,5)
                 AND discon IS NOT NULL)
	OR (NOT EXISTS (SELECT *
 			       FROM subsequip@prop 
 				   WHERE subno = tsp.serial_no)
 	   OR EXISTS (SELECT *
 			     FROM subsequip@prop 
 				 WHERE subno = tsp.serial_no 
 				 AND discon IS NOT NULL)
	   AND (tppr.role_for2site_part = tsp.objid
		AND tppr.player2site_part = acc.objid
		AND acc.part_status IN ('Deinstalled','To be Deinstalled','To be deinstalled','Cancelled','To be cancelled')--1.3.1
		AND acc.objid = pc.site_part2site_part
		AND (pc.x_recurring_cost_end_date != TO_DATE('1753-01-01', 'YYYY-MM-DD')--1.4.1
		    OR (pc.x_data_quality IS NULL
			   OR pc.x_data_quality != 'Checked')--1.4.2
		 	OR EXISTS (SELECT * 
		 	          FROM table_case@osclfp tc,
		 		       	   table_gbst_elm@osclfp tge 
		 		      WHERE tc.s_title LIKE 'SOM%DE%INSTALL%' 
		 			  AND case_prod2site_part = tsp.objid
		 			  AND tge.s_title='SOLVING'
		 			  AND tc.casests2gbst_elm=tge.objid
		 			 )--1.4.4
		 	)))
 	)--1.2.1 


Cheers

Re: Optimizing help appreciated [message #248848 is a reply to message #248832] Mon, 02 July 2007 07:54 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
patber wrote on Mon, 02 July 2007 05:35

AND (pc.x_recurring_cost_end_date != TO_DATE('1753-01-01', 'YYYY-MM-DD')--1.4.1



This is such a poor programming method. Not sure what you're doing, but if your column contains this value because you don't want it to have a NULL Date and you need to assure that you have a date that will never be used, there are better, cleaner more efficient ways to do it. Yuk.

As for speeding it up, run an explain plan and tkprof it if you must. That will give you a clue, because to me, it runs really fast.
Re: Optimizing help appreciated [message #248857 is a reply to message #248848] Mon, 02 July 2007 08:32 Go to previous messageGo to next message
patber
Messages: 9
Registered: May 2007
Junior Member
joy_division wrote on Mon, 02 July 2007 07:54
patber wrote on Mon, 02 July 2007 05:35

AND (pc.x_recurring_cost_end_date != TO_DATE('1753-01-01', 'YYYY-MM-DD')--1.4.1



This is such a poor programming method. Not sure what you're doing, but if your column contains this value because you don't want it to have a NULL Date and you need to assure that you have a date that will never be used, there are better, cleaner more efficient ways to do it. Yuk.

As for speeding it up, run an explain plan and tkprof it if you must. That will give you a clue, because to me, it runs really fast.


yeah I know, but I don't have anything to do with the inserts... just selecting it out so I know what you're saying but it's out of my hand
Re: Optimizing help appreciated [message #249089 is a reply to message #248857] Tue, 03 July 2007 06:09 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are querying tables on multiple remote databases. See here for some tips.

Ross Leishman
Previous Topic: Materialized View Build Time
Next Topic: forall for performance
Goto Forum:
  


Current Time: Wed Dec 07 20:17:38 CST 2016

Total time taken to generate the page: 0.05333 seconds