I’ve been listening scary stories about all kind of issues related to LOB data types (there are many data types based on the LOB concept like Spatial, CLOB, BLOB, XML…).
In this article I’ll put some light on one of the lesser known consequences related to mechanism how LOB works under the hood.
To solve performance/security or any king of issues related to LOB data type, you really need to know how it is implemented, as LOB’s are very different from any other data type.
Main point to understand is to understand the concept of LOB data and LOB locator.
There are two main types of LOB operations:
– Copy semantic – which is used when you copy LOB data and new LOB locator is created for the new LOB.
– Reference semantic – which is used when you copy only LOB locator, with no changes to the underlying data (no new LOB will be created)
As maximum LOB size is (in 11g) 128 TB, Oracle is using LOB locator whenever possible to pass LOB (similar to passing variables by reference), instead of copy the whole content of LOB data (similar to passing variables by value) mainly for performance (and space) reasons.
Side effect of the reference semantic (which is based on LOB locator) is not well known.
Namely DML triggers for locator based operations won’t fire.
The following demo will confirm that fact.
--create test table
create table ttest
(pk_id number
, test_clob clob)
tablespace users;
--create table where trigger will inserts audit data
create table ttest_trigg_tbl
(operation varchar2(100))
tablespace users;
--create row level DML trigger
create or replace trigger ttest_trigger
before insert or delete or update on ttest for each row
begin
if inserting then insert into ttest_trigg_tbl (operation) values ('INSERTING');
elsif updating then insert into ttest_trigg_tbl (operation) values ('UPDATING');
elsif deleting then insert into ttest_trigg_tbl (operation) values ('DELETING');
end if;
end;
--insert initial value into the ttest table to confirm DML row level trigger is working as expected
insert into ttest (pk_id, test_clob)
values (1, 'Test 1');
commit;
--check content of the ttest table
select * from ttest;
PK_ID TEST_CLOB
---------- ----------
1 Test 1
--check to confirm DML trigger has recorded INSERT operation
select * from ttest_trigg_tbl;
OPERATION
---------
INSERTING
--anonymous block of PL/SQL code to simulate reference (LOB locator) based operation
declare
l_test_clob clob;
l_temp varchar2(100);
begin
l_temp = '- FOR UPDATE';
select test_clob into l_test_clob from ttest where pk_id = 1 for update;
dbms_lob.writeAppend(l_test_clob, 12, l_temp);
commit;
end;
--check again content of the ttest table after updating CLOB based column
select * from ttest;
PK_ID TEST_CLOB
---------- ---------------------
1 Test 1- FOR UPDATE
--check to confirm DML trigger did not fire
select * from ttest_trigg_tbl;
OPERATION
----------
INSERTING
-- cleaning all created objects
drop table ttest purge;
drop table ttest_trigg_tbl purge;
Summary:
In the previous demo you can clearly see that DML row level trigger has really missed to record reference/LOB locator based operations on LOB columns.
If you are building some kind of security audit apps, you definitely should be aware of that fact.
But the main message that you can learn is to avoid concept of – implement first, later regret (try to fix).
Instead you should first need to understand how some functionality is implemented (how it works), to be aware of all pros and cons in advance.
Such approach ask for constant learning, but it always pays off, although it seems that you are working slower than other developers who just start typing a new code without analyzing the task on the first place, but later will spend 100’s of hours to fixing design/performance and other issues.
Comments