Function Deterministic "Enough"?

Deterministic Function Question:

We have several functions that query database tables (based on a parameter) to return a value. These functions are used in Where clauses, and execute sometimes thousands of times for each different parameter. Very wasteful. I understand Deterministic functions would execute only once.

"Deterministic" implies "same output for given input". Obviously, a function that queries tables can't guarantee that, but within a transaction, in our application, those tables won't be changing during the execution of this single query.
=> Oracle assumes that the output of a deterministic function remains
constant for a given input. Is this assumption over the lifetime of a query, a transaction, or a session?
If the assumption's just for the query, then that's great for us. (If it's for the session, then not so great.)

I guess I'm asking if a function that accesses tables can be "deterministic enough".

Thanks,
Bob Burgess
Montreal Received on Tue Feb 17 2004 - 10:05:28 CST