Databases Reference
In-Depth Information
There is another option to avoid manual inlining, which works without changing our code. We
can set the parameter
PLSQL_OPTIMIZE_LEVEL
to the value
3
, with the following statement:
ALTER SYSTEM SET PLSQL_OPTIMIZE_LEVEL = 3;
Even if we change the value of this parameter, we can still use the
PRAGMA
INLINE
directive. This parameter lets the database choose if it's a good idea to inline function
calls at compile time, while with the
PRAGMA
INLINE
directive, we ask the database to
use inlining (it's not a hint).
We can also use
ALTER
SESSION
to modify the
PLSQL_OPTIMIZE_
LEVEL
only in the current user session. The default value for the
PLSQL_OPTIMIZE_LEVEL
parameter is
2
in Oracle Database 11
g
.
Please note that the use of value
3
for
PLSQL_OPTIMIZE_LEVEL
can increase compile time,
and also affects other optimizations.
See also
F
Other recipes related to functions and procedures in this chapter are
Avoiding
recursion
,
Using native compilation
, and
Taking advantage of function result cache
Using triggers and virtual columns
In this recipe, we will see how to use virtual columns, a new feature in Oracle Database 11
g
,
to avoid the use of DML triggers, resulting in a performance gain in our applications.
Virtual columns can also be used in referential integrity, tables can
be partitioned by them, and statistics can be gathered on them.
How to do it...
The following steps will demonstrate the use of virtual columns:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
