:old.clob and :new.clob in triggers
in [Oracle]
|
Prev: rman issue
Next: Data Pump and GRANTs
From: Timur Akhmadeev on 17 Jun 2010 12:06 On Jun 17, 12:58 pm, Deb <debashish.majum...(a)gmail.com> wrote: > Hi, > > I've a table with a clob column. In a trigger that I need to write, I > need to compare the old and new values for this column. > > The code inside the trigger body looks like this: > > CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR > DELETE > ON tbl_clob > REFERENCING NEW AS NEW OLD AS OLD > FOR EACH ROW > > IF UPDATING THEN > IF :NEW.clob_col <> :OLD.clob_col > THEN > INSERT INTO ... > END IF; > END IF; > ..... > > END; > / > > Is this the right way of comparing the old and new values for a clob, > inside the trigger? Please suggest. > > -- > Thanks > DeB Hi, you can not write a trigger which will handle all kinds of updates to LOBs. For details refer to the documentation: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/create_trigger.htm#sthref1814. Timur Akhmadeev
From: Vladimir M. Zakharychev on 18 Jun 2010 07:21 On Jun 17, 8:06 pm, Timur Akhmadeev <timur.akhmad...(a)gmail.com> wrote: > On Jun 17, 12:58 pm, Deb <debashish.majum...(a)gmail.com> wrote: > > > > > Hi, > > > I've a table with a clob column. In a trigger that I need to write, I > > need to compare the old and new values for this column. > > > The code inside the trigger body looks like this: > > > CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR > > DELETE > > ON tbl_clob > > REFERENCING NEW AS NEW OLD AS OLD > > FOR EACH ROW > > > IF UPDATING THEN > > IF :NEW.clob_col <> :OLD.clob_col > > THEN > > INSERT INTO ... > > END IF; > > END IF; > > ..... > > > END; > > / > > > Is this the right way of comparing the old and new values for a clob, > > inside the trigger? Please suggest. > > > -- > > Thanks > > DeB > > Hi, > > you can not write a trigger which will handle all kinds of updates to > LOBs. For details refer to the documentation:http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/create.... > > Timur Akhmadeev That's correct, though a bit out of scope of OP's question and thus I omitted this issue. Things get tricky when you alter the LOB contents via OCI or DBMS_LOB. Additional code should be written to get around this "OCI and DBMS_LOB changes to LOBs do not fire triggers" behavior. Possible solution could be to always create a temporary LOB, copy the source LOB into it, alter the temporary LOB and then do an update with SET clob_col=temp_lob, which will fire triggers. Depending on the source LOB size, this copy to temp approach may add significant overhead, but there seem to be no other way to catch any and all LOB changes in triggers. Regards, Vladimir M. Zakharychev N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com
From: Mladen Gogala on 18 Jun 2010 08:48 On Thu, 17 Jun 2010 05:04:42 -0700, Vladimir M. Zakharychev wrote: > On Jun 17, 12:58 pm, Deb <debashish.majum...(a)gmail.com> wrote: >> Hi, >> >> I've a table with a clob column. In a trigger that I need to write, I >> need to compare the old and new values for this column. >> >> The code inside the trigger body looks like this: >> >> CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR >> DELETE >> ON tbl_clob >> REFERENCING NEW AS NEW OLD AS OLD >> FOR EACH ROW >> >> IF UPDATING THEN >> IF :NEW.clob_col <> :OLD.clob_col >> THEN >> INSERT INTO ... >> END IF; >> END IF; >> ..... >> >> END; >> / >> >> Is this the right way of comparing the old and new values for a clob, >> inside the trigger? Please suggest. >> >> -- >> Thanks >> DeB > > Comments embedded in the code below. > > ... > -- this is to limit the scope of the following IF - we don't want -- to > waste CPU cycles and I/O bandwidth comparing contents of old -- and new > CLOB values when it definitely was not touched by the -- update and thus > didn't change. > IF UPDATING('CLOB_COL') THEN > -- this is the right way of comparing LOBs. When you compare like -- > clob1 <> clob2, Oracle implicitly converts your CLOBs to -- VARCHAR2s > (cutting off everything past the first 32k bytes) and -- compares > strings. If the difference is somewhere past the first -- 32k bytes, > this comparison will yield equality which is -- obviously not correct. > DBMS_LOB.COMPARE() compares LOBs fully -- and returns 0 if the are > equal. It does not throw exceptions -- for BLOB and CLOB comparisons, > so it's pretty safe to use in -- triggers. Note that it can return > NULL for some non-equal LOBs, -- so we need to use a variable and test > if it's 0 or NULL - both -- cases indicate the LOBs are different. > DECLARE > RES INTEGER := DBMS_LOB.COMPARE(:NEW.CLOB_COL, :OLD.CLOB_COL); > BEGIN > IF RES IS NULL OR RES != 0 THEN > -- do your stuff, they are different > ... > END; > ... > > Hth, > Vladimir M. Zakharychev > N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com The "right way" of comparing CLOB values depends of how long the CLOB values are. Comparison is usually byte-wise, doing something like this: char *p,*q; while (*p++) { if (*p != *q++) { return(-1); } } return(0); If CLOB fields are large, creating MD5 sums and comparing them will actually be much faster and yet equally reliable as the comparison of the CLOB variables themselves. -- http://mgogala.byethost5.com
From: Vladimir M. Zakharychev on 18 Jun 2010 14:56 On Jun 18, 4:48 pm, Mladen Gogala <gogala.mla...(a)gmail.com> wrote: > On Thu, 17 Jun 2010 05:04:42 -0700, Vladimir M. Zakharychev wrote: > > On Jun 17, 12:58 pm, Deb <debashish.majum...(a)gmail.com> wrote: > >> Hi, > > >> I've a table with a clob column. In a trigger that I need to write, I > >> need to compare the old and new values for this column. > > >> The code inside the trigger body looks like this: > > >> CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR > >> DELETE > >> ON tbl_clob > >> REFERENCING NEW AS NEW OLD AS OLD > >> FOR EACH ROW > > >> IF UPDATING THEN > >> IF :NEW.clob_col <> :OLD.clob_col > >> THEN > >> INSERT INTO ... > >> END IF; > >> END IF; > >> ..... > > >> END; > >> / > > >> Is this the right way of comparing the old and new values for a clob, > >> inside the trigger? Please suggest. > > >> -- > >> Thanks > >> DeB > > > Comments embedded in the code below. > > > ... > > -- this is to limit the scope of the following IF - we don't want -- to > > waste CPU cycles and I/O bandwidth comparing contents of old -- and new > > CLOB values when it definitely was not touched by the -- update and thus > > didn't change. > > IF UPDATING('CLOB_COL') THEN > > -- this is the right way of comparing LOBs. When you compare like -- > > clob1 <> clob2, Oracle implicitly converts your CLOBs to -- VARCHAR2s > > (cutting off everything past the first 32k bytes) and -- compares > > strings. If the difference is somewhere past the first -- 32k bytes, > > this comparison will yield equality which is -- obviously not correct. > > DBMS_LOB.COMPARE() compares LOBs fully -- and returns 0 if the are > > equal. It does not throw exceptions -- for BLOB and CLOB comparisons, > > so it's pretty safe to use in -- triggers. Note that it can return > > NULL for some non-equal LOBs, -- so we need to use a variable and test > > if it's 0 or NULL - both -- cases indicate the LOBs are different. > > DECLARE > > RES INTEGER := DBMS_LOB.COMPARE(:NEW.CLOB_COL, :OLD.CLOB_COL); > > BEGIN > > IF RES IS NULL OR RES != 0 THEN > > -- do your stuff, they are different > > ... > > END; > > ... > > > Hth, > > Vladimir M. Zakharychev > > N-Networks, makers of Dynamic PSP(tm)http://www.dynamicpsp.com > > The "right way" of comparing CLOB values depends of how long the CLOB > values are. Comparison is usually byte-wise, doing something like this: > > char *p,*q; > while (*p++) { > if (*p != *q++) { > return(-1); > }} > > return(0); > > If CLOB fields are large, creating MD5 sums and comparing them will > actually be much faster and yet equally reliable as the comparison of the > CLOB variables themselves. Creating MD5 sums and comparing them will require the same amount of I/ O and is more computationally intensive than byte-wise compare (which most modern CPUs do at superclock speeds thanks to quite a few architectural tricks,) so I don't see how they can be faster. Consider this: a) to compute MD5 hashes, you need to read sequentially every byte, possibly in blocks, of both source and target LOBs, same as with byte-wise comparison, so these operations are equally I/O- intensive; b) to compute an MD5 hash, you need to do fixed amount of work to initialize the hash, put every 16-byte block of input through several rounds of intensive computations (padding the last block if necessary), and do some more fixed amount of work to finalize the hash. You don't need to do all this when you simply compare two memory regions (and some CPUs even have special instructions to facilitate such comparisons.) Amount of CPU time required for byte-wise comparison of two memory regions is a few times less than for computation of MD5 hashes of these regions and additional comparison of the two resulting hashes. Checksumming never was free and this case is no exclusion. Choose any other hash algorithm, it will never beat straight byte-wise comparison because it requires extra work and saves none. Regards, Vladimir M. Zakharychev N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com
From: Vladimir M. Zakharychev on 18 Jun 2010 15:10
On Jun 18, 4:48 pm, Mladen Gogala <gogala.mla...(a)gmail.com> wrote: > On Thu, 17 Jun 2010 05:04:42 -0700, Vladimir M. Zakharychev wrote: > > On Jun 17, 12:58 pm, Deb <debashish.majum...(a)gmail.com> wrote: > >> Hi, > > >> I've a table with a clob column. In a trigger that I need to write, I > >> need to compare the old and new values for this column. > > >> The code inside the trigger body looks like this: > > >> CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR > >> DELETE > >> ON tbl_clob > >> REFERENCING NEW AS NEW OLD AS OLD > >> FOR EACH ROW > > >> IF UPDATING THEN > >> IF :NEW.clob_col <> :OLD.clob_col > >> THEN > >> INSERT INTO ... > >> END IF; > >> END IF; > >> ..... > > >> END; > >> / > > >> Is this the right way of comparing the old and new values for a clob, > >> inside the trigger? Please suggest. > > >> -- > >> Thanks > >> DeB > > > Comments embedded in the code below. > > > ... > > -- this is to limit the scope of the following IF - we don't want -- to > > waste CPU cycles and I/O bandwidth comparing contents of old -- and new > > CLOB values when it definitely was not touched by the -- update and thus > > didn't change. > > IF UPDATING('CLOB_COL') THEN > > -- this is the right way of comparing LOBs. When you compare like -- > > clob1 <> clob2, Oracle implicitly converts your CLOBs to -- VARCHAR2s > > (cutting off everything past the first 32k bytes) and -- compares > > strings. If the difference is somewhere past the first -- 32k bytes, > > this comparison will yield equality which is -- obviously not correct. > > DBMS_LOB.COMPARE() compares LOBs fully -- and returns 0 if the are > > equal. It does not throw exceptions -- for BLOB and CLOB comparisons, > > so it's pretty safe to use in -- triggers. Note that it can return > > NULL for some non-equal LOBs, -- so we need to use a variable and test > > if it's 0 or NULL - both -- cases indicate the LOBs are different. > > DECLARE > > RES INTEGER := DBMS_LOB.COMPARE(:NEW.CLOB_COL, :OLD.CLOB_COL); > > BEGIN > > IF RES IS NULL OR RES != 0 THEN > > -- do your stuff, they are different > > ... > > END; > > ... > > > Hth, > > Vladimir M. Zakharychev > > N-Networks, makers of Dynamic PSP(tm)http://www.dynamicpsp.com > > The "right way" of comparing CLOB values depends of how long the CLOB > values are. Comparison is usually byte-wise, doing something like this: > > char *p,*q; > while (*p++) { > if (*p != *q++) { > return(-1); > }} > > return(0); > > If CLOB fields are large, creating MD5 sums and comparing them will > actually be much faster and yet equally reliable as the comparison of the > CLOB variables themselves. > --http://mgogala.byethost5.com And in addition to what I just wrote consider this: to compute the hashes, you will inevitably need to fully read both LOBs. When doing byte-wise comparison, you stop reading and comparing at the first mismatch, which might be close to the beginning. So hashing and byte- wise comparison are equally I/O-intensive only in the worst case when both LOBs are of the same length and the very last byte is different, in other cases byte-wise comparison is cheaper. Regards, Vladimir M. Zakharychev N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com |