Home » RDBMS Server » Performance Tuning » How to tune UNION (Oracle,10g,Windows XP)
How to tune UNION [message #352681] Thu, 09 October 2008 00:05 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi

Below shown is the problem query

Select enty_name,enty_last_name
From ENTITY_ADDRESS_MASTER,POLICY_REGISTER
Where (ENTY_GROUP_CODE = 'BROKER' And ENTY_CODE = PREG_BROKER)
Or
(ENTY_GROUP_CODE = 'INSURED' And ENTY_CODE = PREG_INSURED)
And ENTY_STATUS = 'Y'
Union
Select enty_name,enty_last_name
From ENTITY_ADDRESS_MASTER A,POLICY_REGISTER,POLICY_ENTITY_REGISTER C
Where A.ENTY_CODE = C.ENTITY_CODE
And B.PREG_ENTITY_CODE = A.ENTY_CODE
And A.ENTY_STATUS = 'Y'

It is taking 1 min 3 secs. to execute and it is unacceptable.The 1st statement before UNION is taking milliseconds to execute and the statement after UNION is taking 3 secs. to execute individually.

Please suggest some expert tuning tips

Thanks in advance

[Updated on: Thu, 09 October 2008 00:07]

Report message to a moderator

Re: How to tune UNION [message #352692 is a reply to message #352681] Thu, 09 October 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use UNION ALL

Regards
Michel
Re: How to tune UNION [message #352718 is a reply to message #352692] Thu, 09 October 2008 02:46 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi

But UNION ALL gives me all rows including duplicate rows right?


Please give me advantages of UNION ALL over UNION
in performance improvement.
Re: How to tune UNION [message #352719 is a reply to message #352692] Thu, 09 October 2008 02:46 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
UNION removes duplicates, which requires a sort. A sort requires every row to be identified before the first row is returned.

When you run ONE of the SQLs independently, it does NOT need to find every row to return the first one. It APPEARS to run in milliseconds because it returns the first row so quickly, but it is actually still searching the database to find more rows to return.

If you want to know the REAL time of such a query, you measure the time it takes to return the LAST row.

UNION ALL does not remove duplicates, so it has the same apeearance of speedyness as the individual SQLs.

Ross Leishman
Re: How to tune UNION [message #352724 is a reply to message #352719] Thu, 09 October 2008 03:39 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi

But i dont want duplicate rows in output in that case i'have to use DISTINCT in both Select statements and using Distinct will degrade my performance right?

So tell me what to do

Thanks,
Ritesh
Re: How to tune UNION [message #352735 is a reply to message #352724] Thu, 09 October 2008 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Right.

There is nothing to do.
You want Oracle to make some job, you have to pay the price for it.

Regards
Michel
Re: How to tune UNION [message #352751 is a reply to message #352681] Thu, 09 October 2008 05:42 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
The second part of UNION:

Select enty_name,enty_last_name
From ENTITY_ADDRESS_MASTER A,POLICY_REGISTER,POLICY_ENTITY_REGISTER C
Where A.ENTY_CODE = C.ENTITY_CODE
And B.PREG_ENTITY_CODE = A.ENTY_CODE
And A.ENTY_STATUS = 'Y'


has to perform full table scan of ALL referenced tables(unless I'm mistaken and
Quote:
A.ENTY_STATUS = 'Y'
selects very small number of rows).

As Michel wrote:
You want Oracle to make some job, you have to pay the price for it.
Re: How to tune UNION [message #352778 is a reply to message #352681] Thu, 09 October 2008 09:26 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I suspect there is more here than we see.

I can't see two queries taking 63 seconds to union all when the two alone take only 3 seconds to get their data.

Please post some information about these queries.

1) how long does each query take when run alone
2) how many rows come back from each query
3) what are query plans from both querys
4) what is plan from the composite query

I would also ask

Have you collected statistics on these tables?

Kevin
Previous Topic: Need Assistance in Fine tuning the SQL
Next Topic: Index creation- Performance impact
Goto Forum:
  


Current Time: Wed Dec 07 18:28:28 CST 2016

Total time taken to generate the page: 0.11971 seconds