Oracle functions' optimiser

Question

I have a query like:

SELECT id, value
FROM very_large_table -- over 5 million records 
WHERE foo(value) > 5 AND boo(value) IS NOT NULL

Assume that foo and boo are functions, that also makes a lot of selects on super large table without indexes (so it's execution costs a lot).

I (as a programmer) know, that foo in 99% time returns more than 5, but boo is 99,9% returns NULL.
It's obvious, that first of all boo should be calculated. And if it's NULL, we don't want this row in the result set. So we DON'T need to calculate foo, because boo is already NULL.

Are there any packages/articles on this theme, because, if I'm doing right - oracle doen't do this kind of optimization

---

The above is just a sample. In my case there are a lot of functions (~50) and I'm using them in various selects in various combinations. So rewriting the functions is not really and option becuse in real situation a have a lot of them: i just wanted to show that these requests are realy slow. I'm just thinkin of some kind of optimizer (in addition to oracle's one)

Answer 1

Oracle CAN do this sort of optimization but needs to be spoon fed It 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.

Answer 2

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));