Oracle functions' optimiser

Oracle CAN do this sort of optimization but needs to be spoon fedIt is called the Oracle Extensible Optimizer and associate statistics

But the easy way to do it in this case is something like this

where case when boo(value) is null then 0 else foo(value) end > 5

which forces the boo function to be evaluated before the foo.

The advanced stuff would be applicable if you don't have control over the query (eg using some BI tool). Another reason is if you have a bunch of coders where it would be excessive to develop that sort of understanding and it is easier to have one or two 'database guys' manage that aspect of things.

Just write function boofoo that runs boo, then foo only if boo was not null.

And to tune that further, you could add a function-based index on that table/column:

create index vlt_boofoo on very_large_table (boofoo(value));

In case you are using Oracle 11 Enterprise, Result Cache could help. This would cache the results of your functions once executed, and would not execute them again unless the data in the underlying tables changes.

If this does not work, you could try to replace your functions by VIEWs to that tables (assuming that you call your functions from more than one place - otherwise you could just join your tables).

This would allow to join these views instead of using the functions, which might allow the optimizer to query your big tables only once instead of once in each call of your functions.

So instead of

CREATE FUNCTION foo( in_value IN very_large_table.value%TYPE )  RETURN PLS_INTEGERAS  v_count PLS_INTEGER;BEGIN  SELECT COUNT(*)  INTO v_count  FROM some_other_large_table  WHERE value = in_value;  RETURN v_count;END foo;

you could

CREATE VIEW view_foo AS  SELECT value, COUNT(*)  FROM some_other_large_table  GROUP BY value;

and join that

SELECT t.id, t.valueFROM very_large_table t -- over 5 million recordsJOIN view_foo foo ON ( foo.value = t.value )JOIN view_boo ...