4 Replies Latest reply on Dec 12, 2018 3:03 PM by 3850169

    SQL Question -Tunning- ODI Procedures


      Hello All,

      I am trying to create Procedure in ODI and one task is executing this SQL:


      select cust.department,count(cust_id)

      from customer

      where C_date between Start_date and End_date

            and status in (Status1,status2)

            and version = SELECT MIN(cus2.version )

                                         FROM customer cus2

                                         WHERE cust.Cust_id = cust2.Cust_id AND cust2.status  = cust.status )

      group by cust.department


      select cust.department, count(cust_id)

      from customer cust

      where created_date < End_date

            and exists ( select 'x'

                         from customer cust1

                         where cust1.cust_id = cust1.cust_id

                                AND cust.version  < cust1.version

                                and cust1.C_date between Start_date and End_date

                                and cust1.version = ( SELECT MIN(cust2.VERSION)                                                                            

                                                      FROM customer cust2                                                                      

                                                         WHERE cust2.cust_id = cust1.cust_id AND cust2.department != cust.department  )


      group by cust.department.


      Can any one help me to speed this SQL, it take very long time to be executed.

      the table contains more than 100 million records .


      What ideas you can suggest  ?


      Best regards,