| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Behavior of DETERMINISTIC functions
I use the word 'hash' here to relate to the object stored somewhere in Oracle server which stores previously calculated function calls.
Couple of questions:
1. Is there are any way to updated the hash table? (will alter func
recompile do or drop and recreate, or a better method)
2. Where is the hash table stored?
3. Does the hash table have to refresh upon db reboot? (doesn't seem
so from the docs)
4. If I delete and insert a row into a table upon which the function
is executed, will the old value be removed from the hash?
Thanx.
Defenitions from Oracle site:
DETERMINISTIC Functions
In some cases the optimizer can use a previously calculated value
rather than executing a user-written function. This is only safe for
functions that behave in a restricted manner. The function must always
return the same output return value for any given set of input
argument values.
The function's result must not differ because of differences in the content of package variables or the database, or session parameters such as the NLS parameters. And if the function is redefined in the future, its output return value must still be the same as that calculated with the prior definition for any given set of input argument values. Finally, there must be no meaningful side-effects such that using a precalculated value instead of executing the function again would matter to the correctness of the application.
The creator of a function can promise to the Oracle server that the function behaves according to these restrictions by using the keyword DETERMINISTIC when declaring the function with a CREATE FUNCTION statement or in a CREATE PACKAGE or CREATE TYPE statement. The server does not attempt to verify this declaration--even a function that obviously manipulates the database or package variables can be declared DETERMINISTIC. (See "DETERMINISTIC Functions".) It is the programmer's responsibility to use this keyword only when appropriate.
Calls to a DETERMINISTIC function may be replaced by the use of an already calculated value when the function is called multiple times within the same query, or if there is a function-based index or a materialized view defined that includes a relevant call to the function.
Additional Information:
See the description of the pragma RESTRICT_REFERENCES in the Oracle8i
Application Developer's Guide - Fundamentals and the descriptions of
CREATE FUNCTION, CREATE INDEX, and CREATE MATERIALIZED VIEW in the
Oracle8i SQL Reference. Also see "Function-Based Indexes" for a
description of function-based indexes, and see Oracle8i Tuning for
detailed information about materialized views.
Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 750-3630 AOL: NetComrade ICQ: 11340726 remove NSPAM to emailReceived on Tue Nov 21 2000 - 16:17:56 CST
 |
 |