View Performance

From: Carol <carol.mcalear_at_pulsion.co.uk>
Date: 18 Jun 2004 03:32:44 -0700
Message-ID: <5fdaf21e.0406180232.642c7568@posting.google.com>

Hi All

I am developer currently looking at and 8.1.7 database running on unix where all the objects have been created by another dba, and i am looking for some advice. In the database there is a package which has a procedure that has the following code

DELETE FROM VIEW1 WHERE COL1=:A1 AND COL2=:A2 Both COL1 AND COL2 and COL6 are part of the primary key

VIEW1 has the following script

CREATE OR REPLACE FORCE VIEW VIEW1(COL1 COL2 COL3 COL4 COL5) AS

   SELECT COL1, COL2, COL3, COL4, COL5,
     FROM TABLE1
    WHERE COL6 = 0; CREATE OR REPLACE TRIGGER TRIGGER1_DELETE    INSTEAD OF DELETE
   ON TABLE1
   FOR EACH ROW
BEGIN
   DELETE FROM TABLE1

         WHERE COL1 = :OLD.COL1
           AND COL6 = 0
           AND COL2 = :OLD.COL2
           AND COL3 = :OLD.COL3;

END;
/

CREATE OR REPLACE TRIGGER TRIGGER1_INSERT    INSTEAD OF INSERT
   ON TABLE1
   FOR EACH ROW
BEGIN
   INSERT INTO TABLE1(COL1, COL6 , COL2, COL3, COL4, COL5)    VALUES(NEW.COL1, 0, NEW.COL2, NEW.COL3, NEW.COL4, NEW.COL5) END;
/

CREATE OR REPLACE TRIGGER TRIGGER1_UPDATE    INSTEAD OF UPDATE
   ON TABLE1
   FOR EACH ROW
BEGIN
   IF :NEW.COL4 <> :OLD.COL4 THEN

      UPDATE TABLE1
SET COL4 = :NEW.COL4

       WHERE COL1 = :OLD.COL1
         AND COL6= 0
         AND COL2= :OLD.COL2
         AND COL5 = :OLD.COL4;

   END IF;
END;
/

There are similar package procedures for both the insert

At present the table has 70K rows but this table is updated, insert and deleted from continually and both the view and the table and the package are in the same schema, my question is why would the previous DBA implement it this way, is there any performance gain, i would of thought this would of been slower using the view because the view performs and full tablescan because only part of the primary key is used and it is not the first column in the primary key and the values for COL6 have a low cardinality (about 6 distinct values)

Thanks in Advance Received on Fri Jun 18 2004 - 05:32:44 CDT