Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Behavior of DETERMINISTIC functions

Re: Behavior of DETERMINISTIC functions

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 23 Nov 2000 13:32:29 -0000
Message-ID: <974982643.6966.0.nnrp-14.9e984b29@news.demon.co.uk>

There is no stored form, or 'hash table' for the results of a deterministic function.

The description makes it sound as if a session will 'remember' that the current call to a function is using the same input values as the last call to the function, and therefore use the last result; however, as far as 8.1.5 is concerned, I have never been able to demonstrate that this happens.

The only critical thing that clearly works is that Oracle can create an index on a user-defined function, or recognise that the result of a function is already stored in a materialized view.

So if you create a materialized view of the form:

    select my_deterministic_fun(col1, col2) result_col,

        etc. .....
    from my_raw_table;

and later run a query:

    select odd_cols, my_deterministic_fun(col1, col2) from my_raw_table;

Oracle may decide that the results are already stored in the materialized view BECAUSE the function has been declared as deterministic.

In conclusion, this seems to be purely an optimizer directive, and not something that will affect run-time frequencies of the function call.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



NetComrade wrote in message <3a1af362.1751470341_at_news.earthlink.net>...

>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.
>
>
>
>---------------
>In case I forgot to mention:
>We use Oracle 7.3.4 and 8.1.6 on Solaris 2.6, 2.7 boxes
>---------------
>Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 750-3630
>AOL: NetComrade ICQ: 11340726 remove NSPAM to email
Received on Thu Nov 23 2000 - 07:32:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US