| How to tune UNION [message #352681] |
Thu, 09 October 2008 00:05  |
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 #352719 is a reply to message #352692] |
Thu, 09 October 2008 02:46  |
rleishman
Messages: 3728
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 #352751 is a reply to message #352681] |
Thu, 09 October 2008 05:42 |
michael_bialik
Messages: 621
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 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
