RE: deterministic functions

From: Jamadagni, Rajendra <rajendra.jamadagni_at_espn.com>
Date: Wed, 9 Aug 2000 08:36:45 -0400
Message-Id: <10584.114168@fatcity.com>

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"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 5.00.2919.6307" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV>
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.</DIV>
<DIV>This straight
from SQL manual ...</DIV>
<DIV><SPAN

class=305323112-09082000> </DIV>
<DIV><SPAN

class=305323112-09082000>HTH</DIV>
<DIV><SPAN

class=305323112-09082000>Raj</DIV>
<DIV><SPAN

class=305323112-09082000><FONT

size=2>______________________________________________________</FONT>

 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. </DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px"></BLOCKQUOTE></BODY></HTML>
Received on Wed Aug 09 2000 - 07:36:45 CDT