How to avoid multiple call to function:
291229Jul 12 2007 — edited Jul 13 2007In our datawarehouse we have a huge receipt row table where all metrics ar stored in the local currency. On top over that we have views which calculate metrics to the desired currency.
So basically all views looks like this
select geo_region,
product_group,
customer_group,
metric1 * (select get_exchange_rate(currency_id) from dual) metric1,
metric1 * (select get_exchange_rate(currency_id) from dual) metric2,
-----------------------------------
metric1 * (select get_exchange_rate(currency_id) from dual) metricx,
group by..
As we have about 20 metrics we notices that the function is called 20 times per row.
Is there really anyway to avoid that? Shouldn't it be it's just the exact same call with the same in-parameters over and over again.
We've tried with local sys_context and the performance is better but the call to the context is still performed 20 times. Any Ideas?