Jun 14, 2010

Pragma RESTRICT_REFERENCES

The fewer side-effects a function has, the better it can be optimized within a query, particular when the PARALLEL_ENABLE or
DETERMINISTIC  hints are used. The same rules that apply to the function itself also apply to any functions or procedures that  it calls.If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is
parsed).To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES.

Pragma RESTRICT_REFERENCES is a compiler directive.
It makes sure that the function maintains the purity rules and code in fuction creation satisfy the purity rules.
There are 4 values for it.WNDS,RNDS,WNPS,RNPS.



WNDS - Write No Database state


RNDS - Read No Database state


WNPS - Write No Package state


RNPS - Read No Package state


TRUST - the function body is not checked for violations of the constraints listed in the pragma

You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec.You can specify up to four
constraints (RNDS, RNPS, WNDS, WNPS,TRUST) in any order. To call a function from parallel queries, you must specify all four
constraints. No constraint implies another.

When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function
is trusted not to violate them. Skipping these checks can improve performance.
If you specify DEFAULT instead of a subprogram name, the pragma applies to all subprograms in the package spec or object
type spec (including the system-defined constructor for object types). You can still declare the pragma for individual
subprograms, overriding the default pragma.A RESTRICT_REFERENCES pragma can apply to only one subprogram declaration. A pragma that references the name of overloaded
subprograms always applies to the most recent subprogram declaration.
Typically, you only specify this pragma for functions. If a function calls procedures, then you need to specify the pragma for
those procedures as well.

Examples

This example asserts that the function BALANCE writes no database state (WNDS) and reads no package state (RNPS). That is, it does not issue any DDL or DML statements, and does not refer to any package variables, and neither do any procedures or functions that it calls. It might issue queries or assign values to package variables.

CREATE PACKAGE loans AS
   FUNCTION balance(account NUMBER) RETURN NUMBER;
   PRAGMA RESTRICT_REFERENCES (balance, WNDS, RNPS);
END loans;

No comments: