Message-Id: <10584.114168@fatcity.com> From: "Jamadagni, Rajendra" Date: Wed, 9 Aug 2000 08:36:45 -0400 Subject: RE: deterministic functions This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C001FE.7A75D53C Content-Type: text/plain; charset="iso-8859-1" DETERMINISTIC is an optimization hint that allows the system to use a saved copy of the function's return result (if such a copy is available). The saved copy could come from a materialized view, a function-based index, or a redundant call to the same function in the same SQL statement. The query optimizer can choose whether to use the saved copy or re-call the function. The function should reliably return the same result value whenever it is called with the same values for its arguments. Therefore, do not define the function to use package variables or to access the database in any way that might affect the function's return result, because the results of doing so will not be captured if the system chooses not to call the function. A function must be declared DETERMINISTIC in order to be called in the expression of a function-based index, or from the query of a materialized view if that view is marked REFRESH FAST or ENABLE QUERY REWRITE. This straight from SQL manual ... HTH Raj ______________________________________________________ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com QOTD: Any clod can have facts, but having an opinion is an art ! Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. ------_=_NextPart_001_01C001FE.7A75D53C Content-Type: text/html; charset="iso-8859-1"

DETERMINISTIC is an optimization hint that allows the system to use a saved copy of the function’s return result (if such a copy is available). The saved copy could come from a materialized view, a function-based index, or a redundant call to the same function in the same SQL statement. The query optimizer can choose whether to use the saved copy or re-call the function. The function should reliably return the same result value whenever it is called with the same values for its arguments. Therefore, do not define the function to use package variables or to access the database in any way that might affect the function’s return result, because the results of doing so will not be captured if the system chooses not to call the function. A function must be declared DETERMINISTIC in order to be called in the expression of a function-based index, or from the query of a materialized view if that view is marked REFRESH FAST or ENABLE QUERY REWRITE.

This straight from SQL manual ...
 
HTH
Raj
______________________________________________________
Rajendra Jamadagni              MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
QOTD: Any clod can have facts, but having an opinion is an art !
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.