One restriction is that you cannot define a nested function as deterministic. To show how works the Deterministic clause in Oracle we need to create Oracle function without the Deterministic clause and then another one with it. Ask Question Asked 6 years, 4 months ago. It simply looks for the value in the index and returns the data records matching the requested value. Whenever a function is free of side effects and deterministic, it is a good practice to add the DETERMINISTIC option. 1+1 is always equal to 2 but a function called Get_Customer_Name (4711) won't return the same value because it fetches data from the database which changes. You can create Function Based Index on a Deterministic Function … Deterministic functions can be created on a function having DML command on it, however you cannot refer such function from SELECT SQL. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data. In earlier releases of Oracle, deterministic is only used for function based indexes. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. In this case Oracle doesn't have to execute the function at all after it created the index. The same goes for materialized views with REFRESH FAST or ENABLE QUERY REWRITE. Also do not use ANALYZE TABLE, this command was deprecated in 10g, use DBMS_STATS instead: share | improve this answer.
This keyword is needed in order to create an index on a user written function. The values returned by a deterministic function should not change even when the function is rewritten or … The same goes for materialized views with REFRESH FAST or ENABLE QUERY REWRITE. See deterministic function caching which came in 10g here: A DETERMINISTICfunction may not have side effects. If the function is deterministic, for the same input parameter signature it gives the same output, and the number of unique parameter signatures used is low compared to the number of rows processed, this can represent a lot of wasted effort. One restriction is that you cannot define a nested function as deterministic.
When a query is passed to the server that could benefit from that index, the query is rewritten to allow the index to be used.
If function f is declared as DETERMINISTIC, then Oracle can do this query with only 50 function calls. No matter how trivial the function is, the mere act of calling PL/SQL that many times can slow down your query. ... so Oracle cannot use this index … If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. To encounter this, Oracle introduced function-based indexes.
Best How To : Yes, you have to rebuild the index.
This implies for example that you cannot index using the package «dbms_rand», the random … Ask Question Asked 6 years, 4 months ago.