1 Reply Latest reply: Mar 23, 2013 5:24 AM by Jim Smith RSS

    unable to tune the select query which is having 100 functions

    998713
      Hi,
      We have 100 functions in the select query, and it is running 50 hours for 75K records.
      Each function will compare main table to history table and get the flag if any change(Y/N)
      ex: emp_package.get_emp_info(Column_E) as emp_Flag,

      it compares basically emp and emp history table and if any change on the name it will return Y else N.

      We tried to tune this job -Splitting the query into multiple but no improvement, We are unable to tune the job. Any help greatly appriciated.
      select
      Column_a,
      Column_B,
      emp_package.get_emp_info(Column_E) as emp_Flag,
      emp_package.get_name_info(Column_E) as name_Flag,
      emp_package.get_sal_info(Column_F) as sal_Flag,
      emp_package.get_product_info(Column_H) as product_Flag,
      emp_package.get_productcost_info(Column_I) as productcost_Flag,
      -
      -
      -
      -
      -
      like this we have 100 funtions.

      from

      temp_table,
      emp,
      product,
      sales,
      -
      -
      -

      where
      -
      -
      -
      -
      -

      Thanks
      Smartpal
      -

      Edited by: 995710 on Mar 23, 2013 2:45 AM