If you need to write the contents of a variable, or a
group of variables, to a relational table, you would
normally use the SQL INSERT command. Normal practice is to
loop round all of the variables' dimension values, inserting
the variable values into the relational tables one by one,
until the variable has been completely loaded into your
database table.
Oracle 9i OLAP however introduces two new commands,
SQL PREPARE and SQL EXECUTE, that allow us to prepare
our INSERT statement in such a way that it uses
bind
variables to pass values to the Oracle tables. Bind
variables are generally a 'good thing' and reduce the amount
of time Oracle has to spend parsing your SQL insert
statements. In addition, you can specify additional options
with SQL PREPARE to specify 'direct path' insertions
(quicker as they bypass the normal SQL engine and directly
load data into Oracle blocks), nologging (to eliminate redo
log generation), and to nominate individual partitions to
load data in to. It's worth noting that there's an error in
the current OLAP DML documentation that suggest that any
OLAP DML insert operation into an Oracle table locks the
entire table, preventing other AW processes from inserting
into the table until you commit. This is actually untrue,
and full-table locking only occurs if you use the DIRECT=YES
option, which locks the table in the same way that
SQL*Loader locks the table as they both use the Direct Path
API.
However, an even better solution than using SQL EXECUTE
and SQL PREPARE is to use the
OLAP_TABLE feature in Oracle 9i to create a view against
your AW variable, then use this view as the source for a
"INSERT INTO table SELECT * FROM source" SQL statement,
optionally using the /*+ INSERT APPEND */ option if you want
to carry out direct path insertions. By using OLAP TABLE and
having the SQL engine insert multiple variable values into
our target table, rather than having an OLAP DML program
loop through the variable and carry out multiple single-row
insertions, we were able to increase our write performance
by an order of magnitude compared to our earlier SQL INSERT
command. One thing to bear in mind though is that, if you
are running many copies of the program concurrently, using
direct path insertions may well cause lock contention, as
each process will obtain an exclusive table lock while the
direct insertion takes place. In the case of concurrent
processes, it may be better to use conventional path
insertions (but still use SQL PREPARE and EXECUTE, or OLAP
TABLE) as these only require row-level exclusive locks.
