Hi,
To know when a row of a table has been update/inserted I was thinking of using the ROWDEPENDENCIES feature witch can be set when a table is created.
Because I have no experience with this feature and because I couldn't find any interesting document about it I was wondering I anyone already used it.
I'm especially interested to know how big the performance impact is when ROWDEPENDENCIES is used (on an OLTP system using Oracle 10.2). During the quick performance tests (see below) I did, I only noticed a slowdown of about 4%.
Beside the 6 bytes per row needed to store the SCN and the performance impact are there any other disadvantages?
Regards
Maurice
PS: Test script
SET timing off
DROP TABLE test_table_rowdependencies;
CREATE TABLE test_table_rowdependencies(kasse VARCHAR2(1), vp_boid NUMBER(12, 0), datum_per DATE, produkt_gruppe VARCHAR2(6), kopf_internalname VARCHAR2(50), code_name_de VARCHAR2(50), kriterium_nummer NUMBER, datum_upto DATE) rowdependencies;
CREATE UNIQUE INDEX ui_test_table_rowdep ON test_table_rowdependencies(vp_boid, kasse);
BEGIN
dbms_stats.gather_table_stats(ownname => USER, tabname => 'TEST_TABLE_ROWDEPENDENCIES', estimate_percent => 100);
END;
/
SET timing ON
BEGIN
FOR i IN 1 .. 10000
LOOP
INSERT
INTO test_table_rowdependencies(kasse, vp_boid, datum_per, produkt_gruppe, kopf_internalname, code_name_de, kriterium_nummer, datum_upto)
VALUES('C', i, to_date('01.01.2006 00:00:00', 'DD.MM.RRRR hh24:mi:ss'), 'XXX', 'CT_YYY_ZZZ', 'Berlin', 1, to_date('31.12.2006 00:00:00', 'DD.MM.RRRR hh24:mi:ss'));
COMMIT;
UPDATE test_table_rowdependencies
SET kasse = 'V'
WHERE vp_boid = i;
COMMIT;
END LOOP;
END;
/
SET timing off
DROP TABLE test_table_norowdependencies;
CREATE TABLE test_table_norowdependencies(kasse VARCHAR2(1), vp_boid NUMBER(12, 0), datum_per DATE, produkt_gruppe VARCHAR2(6), kopf_internalname VARCHAR2(50), code_name_de VARCHAR2(50), kriterium_nummer NUMBER, datum_upto DATE) norowdependencies;
CREATE UNIQUE INDEX ui_test_table_norowdep ON test_table_norowdependencies(vp_boid, kasse);
BEGIN
dbms_stats.gather_table_stats(ownname => USER, tabname => 'TEST_TABLE_NOROWDEPENDENCIES', estimate_percent => 100);
END;
/
SET timing ON
BEGIN
FOR i IN 1 .. 10000
LOOP
INSERT
INTO test_table_norowdependencies(kasse, vp_boid, datum_per, produkt_gruppe, kopf_internalname, code_name_de, kriterium_nummer, datum_upto)
VALUES('C', i, to_date('01.01.2006 00:00:00', 'DD.MM.RRRR hh24:mi:ss'), 'XXX', 'CT_YYY_ZZZ', 'Berlin', 1, to_date('31.12.2006 00:00:00', 'DD.MM.RRRR hh24:mi:ss'));
COMMIT;
UPDATE test_table_norowdependencies
SET kasse = 'V'
WHERE vp_boid = i;
COMMIT;
END LOOP;
END;
/
SET timing off