Re: Behavior of DETERMINISTIC functions

Jonathan Lewis wrote:
>
> 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

Agreed - I was hoping that deterministic functions would obviate the need for code such as inline views with groupings etc to ensure that the non-function predicates would go first...or using ORDERED_PREDICATES... ...but alas...no.

Cheers

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"