Home » SQL & PL/SQL » SQL & PL/SQL » Other Possibilities - SQL Query (Oracle Database 10g)
Other Possibilities - SQL Query [message #434281] Tue, 08 December 2009 06:23 Go to next message
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 #434284 is a reply to message #434281] Tue, 08 December 2009 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The other possibility is to push the condition on irregularity_type_code outside the UNION ALL.

Regards
Michel
Re: Other Possibilities - SQL Query [message #434285 is a reply to message #434284] Tue, 08 December 2009 06:37 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Thanks.

Please guide me, how do I implement this.

Regards,
Marlon.
Re: Other Possibilities - SQL Query [message #434287 is a reply to message #434285] Tue, 08 December 2009 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from
( select from without where
union all
select from without where
)
where
/


Regards
Michel
Re: Other Possibilities - SQL Query [message #434290 is a reply to message #434287] Tue, 08 December 2009 06:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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 Go to previous messageGo to next message
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.
Re: Other Possibilities - SQL Query [message #434297 is a reply to message #434281] Tue, 08 December 2009 07:39 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is there any actual point to the inline view in example 1?
Re: Other Possibilities - SQL Query [message #434362 is a reply to message #434296] Tue, 08 December 2009 23:30 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Thanks JRowBottom.

Please explain me about the WITH Clause SELECT query.
I need to select the columns from other tables and join with
the UNION ALL inline view.

Regards,
Marlon
Re: Other Possibilities - SQL Query [message #434365 is a reply to message #434362] Tue, 08 December 2009 23:58 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is called subquery_factoring_clause, read the link.

Regards
Michel
Previous Topic: FTP package by Tim Hall
Next Topic: Problem in incrementing the column value
Goto Forum:
  


Current Time: Wed Sep 28 05:44:23 CDT 2016

Total time taken to generate the page: 0.09190 seconds