-1

I have a project which, in essence, is a batch of computations. It depends on quite a few parameters that, while they are not constants (they might change over time) there is no way they are going to change in the batch context.

To make myself clear, think of VAT rate: it might change over time, but when one closes an accounting period, it behaves like a constant on what concerns the closing itself.

Because these parameters are all over the place, I would like to find a way to limit DB lookup as much as possible. Ideally, I would implement a DETERMINISTIC function, but, this is out of the question - as suggested by relative documentation.

Any ideas / suggestions?

Thank you in advance.

EDIT: Keep also in mind that these values are stored in database - as we may keep VAT rate so that we may know its value at a given point in time. Though it wouldn't be expected, it is possible that a batch concerning some previous period will run again - and will need to know the value of its parameters as they were then.

The benefit of a DETERMINISTIC function is that, given the fact that it produces consistent results (same input always gives the same output) is what I would do if these values were constants and I wouldn't want to keep track of them. But the documentation states clearly that, if a function does db lookups, it must never be DETERMINISTIC.

theodojo
  • 206
  • 2
  • 9
  • Suggested by relative documentation? What do you mean? You could create a package with a function called `get_parameter` which would return the value. This way, when you'll want to change the returned value, you won't invalidate objects dependent on that function, because recompilation of package body doesn't cause that. – Przemyslaw Kruglej Nov 13 '13 at 12:14
  • I changed it, hope it makes more sense now. Your suggestion is interesting, but, in fact, the value is looked up in the database. To put it another way, it is a requirement that these values will be changed in a form and I find it risky to create package bodies dynamically. – theodojo Nov 13 '13 at 13:10
  • So basically you want to have a value that may have different values over time. So, the change is possible. What you want is to forbid change of a value that is no longer "actual" (it's for a previous period), or even for the current period as well. You could create a trigger that doesn't allow update on that column, and you could write a function (best in package) that will query the table and return value for any given period (I guess the period will be a paremeter here). Would that work for you? After all, you will never be sure that someone won't fiddle with the data behind your back. – Przemyslaw Kruglej Nov 13 '13 at 13:24
  • Not exactly. I can be sure that no one will be messing with the parameters during the batch run (but your idea makes sense and maybe I should implement it anyhow). I am just trying to avoid database lookups. You can imagine that any of these parameters will be multiplied around 100K times in a single query. Can I rely on Oracle to cache its value? – theodojo Nov 13 '13 at 13:38

1 Answers1

1

You can't create an "almost" deterministic function. You can create a deterministic function if you call it correctly. If we assume you're creating a simple function to calculate the amount of VAT you can do it two ways; firstly by referencing the table directly in the function:

create or replace function calculate_vat is ( 
       P_Sale_Value in number ) return number is

   l_vat number;

begin

   select trunc(vat_rate * P_Sale_Value, 2) into l_vat
     from vat_table
    where ...

   return l_vat;

end;
/

This would be called something like this:

select sale_value, calculate_vat(sale_value)
  from sales_table

You cannot create this function as deterministic because the value in the table might change. As the documentation says:

Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function

However, you can create the function differently if you pass in the VAT value as a parameter:

create or replace function calculate_vat is ( 
       P_Sale_Value in number
     , P_VAT_Rate in number
       ) return number deterministic is

begin    
   return trunc(P_VAT_Rate* P_Sale_Value, 2);    
end;
/

You can then call it with a JOIN on the VAT table, to give you your, valid, deterministic function.

select s.sale_value, calculate_vat(s.sale_value, v.vat_rate)
  from sales_table s
  join vat_table v
    on ...
 where ...
Ben
  • 51,770
  • 36
  • 127
  • 149
  • Well, that's what I would like to avoid because, most probably, the JOIN condition on VAT table will be like 'nvl(vat.end_date,sysdate+1) > sysdate' - which, in turn may have considerable negative effect on performance. I know I can't use a deterministic function. What I am looking for, it is a way to write a get_vat function that would ideally behave like a deterministic. – theodojo Nov 13 '13 at 13:47
  • 1
    The join condition shouldn't have a negative performance, it'll be better than doing individual look-ups anyway. I've just given you one way @theodojo... the other way is [sub-query caching or global variables](http://stackoverflow.com/q/7270467/458741). – Ben Nov 13 '13 at 13:49
  • That's what I was looking for. I guess subquery caching will work with a WITH clause also? – theodojo Nov 13 '13 at 13:51
  • Yes, it will @theodojo. – Ben Nov 13 '13 at 13:54