Angus0011 (1) [Avatar] Offline
Hello Ian,

Could you please show some details about the 'Cache function values' in Table11.12. I googled but didn't get anything useful. May be a script example can help me out. Thanks a lot.

ian.stirk (27) [Avatar] Offline
Re: Cache function values
Hi Angus,

Let me try to explain in another way… If you have a function (e.g. one that returns a constant value), and that function is used in a SELECT statement, for example on the JOIN or WHERE clause, the function is called each time for every row the SELECT statement produces. This is very inefficient, and I have seen this is many code samples.

Instead, if the value of the function is obtained and stored in a local variable (i.e. it is cached), that cached value can then be used in the SELECT query instead of the function call. This means

I once saw a function that was part of a WHERE clause, it was called 47,000 times each second, for a whole day! But putting the function value in a local variable, and then using that local variable in the place of the function call in the WHERE clause improved the query dramatically.

You can see more information here:,or.r_gc.r_pw.r_qf.,cf.osb&fp=1821ba54f6c138b6&biw=1366&bih=653