A deterministic function gives always the same result to given parameters. The result of a deterministic function depends only from the input. It can have more than one parameter.
Samples of deterministic functions: length, to_upper, trunc, substr
Oracle uses deterministic functions building function based indexes. Be deterministic is necessary because oracle stores the function result into a normal index. At query time the optimizer calls the function again for query parameters but not for all table values.
answered Nov 27, 2009 at 05:45 AM