Everyone knows that bind variables are an important aid to achieving scalability in OLTP systems. Here’s an example to show how important they are with insert statements – particularly if you believe in using constraints to enforce data correctness.
create table t1 ( n1 number check (n1 > 0), v1 varchar2(10) check (v1 = upper(v1)) ) ; begin for i in 1..1000 loop execute immediate 'insert into t1 values(' || i || ',''XXX'')'; end loop; end; /
The pl/sql loop is going to produce 1,000 statements like: insert into t1 values(1,’XXX’);
When this has run, take a look in v$sql for statement looking like: select condition from cdef$ where rowid=:1; you’ll find that it has been run 2,000 times. That’s twice for each statement you executed – once for each constraint.
Oracle doesn’t keep long columns cached in the dictionary cache (rowcache) so every time it optimises a new statement that uses a check constraint (and the condition column is a long column) it has to re-read the constraint definition from the database – just as it does with the view definition when you optimise a statement that uses a view.
So if you have lots of constraints in place, and do lots of separate insert statements – make sure you use bind variables.