Oracle PL/SQL for DBAs

Other Classes of Dynamism

Perhaps the most important enhancement to RLS in Oracle Database 10g is the support of new levels of dynamism, implemented as policy types and intended to improve performance.

First, let’s review the difference between static and dynamic policies . With a dynamic policy type, the policy function is executed to create a predicate string every time the policy places filters on access to the table. Although using a dynamic policy guarantees a fresh predicate every time it is called, the additional overhead resulting from multiple executions of the policy function can be quite substantial. The fact is that, in most cases, the policy function does not need to be re-executed, because the predicate will never change inside a session, as we showed earlier in the discussion of static policies .

The best approach, from a performance point of view, would be to design the policy function so that if some specific value changes, the policy function will be re-executed. Oracle Database 10g offers such a feature: if an application context on which the program depends is changed, the policy forces re-execution of the function; otherwise, the function will not be run again. We’ll see how this works in the following sections.

As with Oracle9i Database, in Oracle Database 10g, you can set the static_policy parameter in the ADD_POLICY procedure to TRUE (indicating a static policy) or FALSE (indicating a dynamic policy). If this parameter is TRUE, then the value of a new Oracle ...