This discussion is archived
1 Reply Latest reply: Mar 23, 2013 3:24 AM by Jim Smith RSS

unable to tune the select query which is having 100 functions

998713 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points