Home » RDBMS Server » Performance Tuning » How to avoid union? (Oracle 10g,Windows 2003)
How to avoid union? [message #361018] Mon, 24 November 2008 15:39 Go to next message
newToPlsql
Messages: 4
Registered: October 2008
Junior Member
Hi All

i have a script which has almost 12 different such selects,and gets inserted into a table.The issue is the INDEX defined on the columns in select staments are not getting used because i am using a ditinct(i am not sure of this).Is there any way i can improve the perfomance of this script?

Will spliting these script into individual 12 diffrent insert statment yield a better perfomance?



INSERT /*+append*/ INTO table (
     	name,
	number,	
	columnname	
		)
		(
			SELECT 
				  DISTINCT t.Proname, 
				  t.ProNumber  
				  'ProdName',				  
			FROM 
				  AnotherTable t				  	
			GROUP BY 
			  	  t.Proname
		)
		UNION
		(
			SELECT 
				DISTINCT t.Custname, 
				t.CustNumber  
				'CustName',				  
			FROM 
				 AnotherTable t				  	
			GROUP BY 
			  	  t.Custname
		)


Thanks in advance
Re: How to avoid union? [message #361021 is a reply to message #361018] Mon, 24 November 2008 20:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's not the DISTINCT that is stopping index use. You are not restricting the query with a WHERE clause, so all rows are candidates for the query. Because you are reading every row, Oracle figures (correctly) that a full scan will be faster.

You don't need the DISTINCTs or GROUP BYs anyway because the UNION will remove duplicates. Still, Oracle should detect this and optimise it out. It wouldn't hurt to remove them though.

Otherwise, what you are doing is the quickest way.

How many rows are in AnotherTable?
And how many distinct combinations are inserted for each SQL?

Ross Leishman
Re: How to avoid union? [message #361120 is a reply to message #361018] Tue, 25 November 2008 01:49 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Ask yourself,..

Do you REALLY need the distincts.

Do you really need to use UNION or will UNION ALL suffice (much quicker than union).
Previous Topic: Template to capture Details about successful Oracle Performance Improvement
Next Topic: SQL Tuning Advisor Feature
Goto Forum:
  


Current Time: Sat Dec 03 00:52:46 CST 2016

Total time taken to generate the page: 0.13458 seconds