| Other Possibilities - SQL Query [message #434281] |
Tue, 08 December 2009 06:23  |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Hi All,
Suppose I have a query like below,
SELECT i.credit_request_id, i.irregularity_id, i.dol_request_id
FROM *IRREGULARITY* i
WHERE i.irregularity_type_code IN ('UNACTIONED_DOL', 'DECLINED_DOL')
UNION ALL
SELECT ih.credit_request_id, ih.irregularity_id, ih.dol_request_id
FROM *IRREGULARITY_HISTORY* ih
WHERE ih.irregularity_type_code IN ('UNACTIONED_DOL', 'DECLINED_DOL')
In the above query, first table contains the current month data and the second contains only history data.
The requirement is to have both current and histroy data for some validations. Tell me, is this the only way(LIKE UNION ALL) to get both details or any other possibility available.
Because this takes more time when added as an inline view in a SQL query.
Please advice.
Thanks & Regards,
Marlon
[Mod Edit: Added code tags, please do this yourself in future]
[Updated on: Tue, 08 December 2009 06:29] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Other Possibilities - SQL Query [message #434290 is a reply to message #434287] |
Tue, 08 December 2009 06:51  |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Thanks Michel.
But this one also takes the same elapsed time.
Can you please suggest me any other alternative.
And also these two tables has got indexes but am not able to use or join with other tables because of this UNION ALL approach.
SAMPLE QUERY
-------------
SELECT * FROM TABLE T1,TABLE_TWO T2,(
SELECT I.CREDIT_REQUEST_ID, I.IRREGULARITY_ID, I.DOL_REQUEST_ID
FROM IRREGULARITY I
WHERE I.IRREGULARITY_TYPE_CODE IN ('UNACTIONED_DOL', 'DECLINED_DOL')
UNION ALL
SELECT IH.CREDIT_REQUEST_ID, IH.IRREGULARITY_ID, IH.DOL_REQUEST_ID
FROM IRREGULARITY_HISTORY IH
WHERE IH.IRREGULARITY_TYPE_CODE IN ('UNACTIONED_DOL', 'DECLINED_DOL')
) T3
WHERE T1.COL1 = T2.COL1
AND T2.COL2 = T3.COL2
Thanks & Regards,
Marlon
|
|
|
|
| Re: Other Possibilities - SQL Query [message #434292 is a reply to message #434290] |
Tue, 08 December 2009 07:10 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
How about posting the explain plan for that query along with a list of indexes available for each table.
Also what's the performance like if you don't use an inline view and just do the union all in the main query?
|
|
|
|
| Re: Other Possibilities - SQL Query [message #434296 is a reply to message #434290] |
Tue, 08 December 2009 07:27 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can rewrite the three table query:SELECT *
FROM (SELECT I.CREDIT_REQUEST_ID
,I.IRREGULARITY_ID
,I.DOL_REQUEST_ID
FROM IRREGULARITY I
,TABLE T1
,TABLE_TWO T2
WHERE I.IRREGULARITY_TYPE_CODE IN ('UNACTIONED_DOL', 'DECLINED_DOL')
AND T1.COL1 = T2.COL1
AND T2.COL2 = I.COL2
UNION ALL
SELECT IH.CREDIT_REQUEST_ID
,IH.IRREGULARITY_ID
,IH.DOL_REQUEST_ID
FROM IRREGULARITY_HISTORY IH
,TABLE T1
,TABLE_TWO T2
WHERE IH.IRREGULARITY_TYPE_CODE IN ('UNACTIONED_DOL', 'DECLINED_DOL')
AND T1.COL1 = T2.COL1
AND T2.COL2 = I.COL2
) T3
or
WITH T AS (SELECT *
FROM TABLE T1
,TABLE T2
WHERE T1.COL1 = T2.COL1)
SELECT *
FROM (SELECT I.CREDIT_REQUEST_ID
,I.IRREGULARITY_ID
,I.DOL_REQUEST_ID
FROM IRREGULARITY I
,T
WHERE I.IRREGULARITY_TYPE_CODE IN ('UNACTIONED_DOL', 'DECLINED_DOL')
AND T.COL2 = I.COL2
UNION ALL
SELECT IH.CREDIT_REQUEST_ID
,IH.IRREGULARITY_ID
,IH.DOL_REQUEST_ID
FROM IRREGULARITY_HISTORY IH
,T
WHERE IH.IRREGULARITY_TYPE_CODE IN ('UNACTIONED_DOL', 'DECLINED_DOL')
AND T.COL2 = I.COL2
) T3
Both of those should allow better index use on the two unioned queries.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
