Re: View Performance

carol.mcalear_at_pulsion.co.uk (Carol) wrote in message news:<5fdaf21e.0406180232.642c7568_at_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

Well first, are you sure this is the correct source? An instead of trigger should be defined on a view and redirect DML to one of the base tables in the view. You show the triggers defined on the table and redirecting the same DML against themselves.

In general instead of triggers would be defined where the view does not present a key preserved view of the data, that is, there is not one to one correlation to the PK of any of the base tables or the key preserved base table is not the target of the DML.

The previous DBA may have implemented a view with instead of triggers because he or she understands something about the application use of the view and the table that is not apparent to you.

HTH -- Mark D Powell -- Received on Fri Jun 18 2004 - 11:39:14 CDT