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.
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.