0 Replies Latest reply on Feb 2, 2016 9:54 AM by 4a1be7d6-f43f-4bf9-9d41-0e5ff35853f7

    optimization of cte

    4a1be7d6-f43f-4bf9-9d41-0e5ff35853f7

      Hi,

       

      I am trying to optimize the below query code to get same output.

       

      with cte as
      (
      select a.related_serial_number,
      a.serial_number,
      b.role_id,
      c.role_name,
      D.Customer_Name,
      b.cmat_customer_id,
      b.CMAT_site_id,
      f.system_status,
      f.orgnl_sales_order_number,
      a.creation_date,
      a.instance_relationship_id

      from eim_instance_relationships a,
      eim.EIM_LATEST_SYS_PARTY_ROLE b,
      eim.EIM_Roles c,
      eim.EIM_Party D,
      EIM.EIM_System f
      where a.Pkey between '31' and '45'
      and a.p_pkey between '1' and '2'

      and A.serial_number=b.system_serial_number
      and D.cmat_customer_id=b.cmat_customer_id
      and A.serial_number=f.system_serial_number
      and b.role_id=c.role_id

      and related_serial_number='01c33679-a5b2-11e4-82f6-123478563412'
      and b.role_id in ('1','4','10','3','19')
      and a.related_component_type='CLUSTER'
      and a.component_type='CONTROLLER'
      and a.related_serial_number in
                                      (
                                          select related_serial_number from eim.eim_instance_relationships e
                                           where e.related_component_type='CLUSTER'
                                           and e.component_type='CONTROLLER'
                                           and e.p_pkey between '1' and '2'                    
                                           and e.Pkey between '31' and '45'
                                           group by e.related_serial_number
                                           having count(*)>1
                                      ) order by 1,2
      )
      ,cte2 as
      (
      select
      row_number() over (partition by role_name,cmat_site_id order by instance_relationship_id desc) R,instance_relationship_id,
      related_serial_number,serial_number,role_id,role_name,Customer_Name,cmat_customer_id,CMAT_site_id,system_status,orgnl_sales_order_number,creation_date
      from cte
      where (related_serial_number,role_name) in
      (
        select related_serial_number,role_name from
          (
            select related_serial_number,role_name,count(*) from
          (
            select related_serial_number,serial_number,role_id,role_name,customer_name,CMAT_Customer_ID,cmat_site_id,system_status,orgnl_sales_order_number,count(*) from cte
            group by related_serial_number,serial_number,role_id,role_name,customer_name,CMAT_Customer_ID,cmat_site_id,system_status,orgnl_sales_order_number
          )
         
      group by related_serial_number,role_name
      HAVING COUNT(distinct CMAT_Site_ID)>1
      )
      )

      )
      select * from cte2
      where r=1  order by related_serial_number,serial_number