6 Replies Latest reply: Apr 10, 2013 1:55 AM by 797021 RSS

    Union all too slow for my query any alternative?

    797021
      Good Morning,

      The below query prints the desired results. What i am trying to accomplish is to addition to my below query is to also retrive information from different dates (for example 01.01.2012 - 30.4.2012) on top of the information below but with amounts 0. The only solution i could found was to duplicate the below query to a union and input the desired dates. The problem is that then the query is too slow.
      Any reccomendations are welcome
      SELECT s.trans_datetime,s.alloc_ref,s.accnt_code,s.treference,s.DESCRIPTN,sum(s.amount*(-1)) as amount,s.conv_code,sum(s.other_amt*(-1)) as other_amt,s.anal_t1,ss.descr,
      o.NTN_DESCR as flag,z.name as vessel_name
      FROM  accounts s, customers ss,vessel a,nation o, analysis  z,
      customers_anl_cat  z1
      where s.trans_datetime BETWEEN to_date('01.01.2013','DD.MM.YYYY') AND to_date('30.04.2013','DD.MM.YYYY')
      AND s.accnt_code=z1.acnt_code
      AND z1.anl_cat_id=17
      AND ss.ACNT_TYPE=2
      AND s.accnt_code=ss.acnt_code
      AND o.NTN_CODE=a.flg_code
      AND z.anl_code=s.anal_t1
      GROUP BY s.trans_datetime,s.alloc_ref,s.accnt_code,s.treference,s.DESCRIPTN,s.conv_code,s.anal_t1,ss.descr
      ,o.NTN_DESCR,z.name,z1.anl_code
      Thank you In advance
        • 1. Re: Union all too slow for my query any alternative?
          Karthick_Arp
          May be try like this
          select s.trans_datetime
               , s.alloc_ref
               , s.accnt_code
               , s.treference
               , s.descriptn
               , sum(s.amount*(-1)) as amount
               , s.conv_code
               , sum(s.other_amt*(-1)) as other_amt
               , s.****_t1,ss.descr
               , o.ntn_descr as flag
               , z.name as vessel_name
               , case when s.trans_datetime between to_date('01.01.2013','dd.mm.yyyy') and to_date('30.04.2013','dd.mm.yyyy') then 2013
                      when s.trans_datetime between to_date('01.01.2012','dd.mm.yyyy') and to_date('30.04.2012','dd.mm.yyyy') then 2012
                 end trans_year
            from accounts s
               , customers ss
               , vessel a
               , nation o
               , analysis  z
               , customers_anl_cat  z1
           where (
                    s.trans_datetime between to_date('01.01.2013','dd.mm.yyyy') and to_date('30.04.2013','dd.mm.yyyy')
                 or
                    s.trans_datetime between to_date('01.01.2012','dd.mm.yyyy') and to_date('30.04.2012','dd.mm.yyyy')
                 )
             and s.accnt_code=z1.acnt_code
             and z1.anl_cat_id=17
             and ss.acnt_type=2
             and s.accnt_code=ss.acnt_code
             and o.ntn_code=a.flg_code
             and z.anl_code=s.****_t1
           group 
              by s.trans_datetime
               , s.alloc_ref
               , s.accnt_code
               , s.treference
               , s.descriptn
               , s.conv_code
               , s.****_t1
               , ss.descr
               , o.ntn_descr
               , z.name
               , z1.anl_code
               , case when s.trans_datetime between to_date('01.01.2013','dd.mm.yyyy') and to_date('30.04.2013','dd.mm.yyyy') then 2013
                      when s.trans_datetime between to_date('01.01.2012','dd.mm.yyyy') and to_date('30.04.2012','dd.mm.yyyy') then 2012
                 end;
          • 2. Re: Union all too slow for my query any alternative?
            Purvesh K
            Wouldn't this give you correct results?
             where ((s.trans_datetime BETWEEN to_date('01.01.2013','DD.MM.YYYY') AND to_date('30.04.2013','DD.MM.YYYY') and s.amount > 0)
                      or (s.trans_datetime BETWEEN to_date('01.01.2012','DD.MM.YYYY') AND to_date('30.04.2012','DD.MM.YYYY') and s.amount = 0) )
             Other conditions follow thsi
            If you could provide us Sample data and the expected results, it might be possible to help with the query that could help you achieve the results.

            If you have problems with Query performance then refer to {message:id=3292438}
            • 3. Re: Union all too slow for my query any alternative?
              797021
              Thank you for your replies.

              Unfortunately the new date rows have values in the amount and needed to converted to 0 or null
              • 4. Re: Union all too slow for my query any alternative?
                Purvesh K
                794018 wrote:
                Thank you for your replies.

                Unfortunately the new date rows have values in the amount and needed to converted to 0 or null
                Okay.... So that does not qualify for a situation to replicate the Query to use UNION ALL clause. You can simply do following in select:
                Select  column_list1... column_listN,
                case when s.trans_datetime BETWEEN to_date('01.01.2013','DD.MM.YYYY') AND to_date('30.04.2013','DD.MM.YYYY') then s.amount
                       when s.trans_datetime BETWEEN to_date('01.01.2012','DD.MM.YYYY') AND to_date('30.04.2012','DD.MM.YYYY') then 0
                end amount
                from table_names
                where (s.trans_datetime BETWEEN to_date('01.01.2013','DD.MM.YYYY') AND to_date('30.04.2013','DD.MM.YYYY') or s.trans_datetime BETWEEN to_date('01.01.2012','DD.MM.YYYY') AND to_date('30.04.2012','DD.MM.YYYY'))
                Other conditions Follow
                • 6. Re: Union all too slow for my query any alternative?
                  Karthick_Arp
                  794018 wrote:
                  Thank you for your replies.

                  Unfortunately the new date rows have values in the amount and needed to converted to 0 or null
                  You just have to use the same CASE statement that i have used and make the amount as 0 for the date that falls in the year 2012.