Oracle triggers if updating performance Milfshookup com boise

posted by | Leave a comment

Then because the view had to be updateable, I created 3 instead of triggers on that view so that users can query the view providing the concatenated compound key column instead of the individual columns. Analyze that trace file and it will tell you where it is slowing down. Because my statement is something like UPDATE v_cust_order_item SET acolumn = avalue WHERE cust_order_item_pk = ' NC/99999/2015/1', this statement does not use the composite key but the concatenated key which is of course not indexed!

Although the triggers work, the update trigger is very very slow despite the fact that its using all 4 compound keys in the where clause. I need somehow to create an index on the view on the computed cust_order_item_pk. If you are on Oracle 11g release 1 or later, create a virtual column for your concatenated compound key in the original table. You ought to be able to use this virtual column as a unique key.

SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- -------------------- -------------------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.

There are a couple of tables with additional triggers, which were separated from the previous one in order to make their intent more clear.

Do you have a proper index on those keys on that table?

All INSTEAD OF triggers are fired for each row and you cannot narrow down the event by column.

oracle triggers if updating performance-75oracle triggers if updating performance-29oracle triggers if updating performance-49

The returning clause retrieves the rows affected by a DML statement.The actions you're performing on the data is technically different between the insert/update portions, but what about the business logic difference?IMO, your best bet is to contain a functional unit of work inside of a trigger, so that there is no discrepancy in what that trigger does.I have experienced factors far greater the 8 associated with sequences. You need not specify the partition name when updating values in a partitioned table.

Leave a Reply

dating in louth lincolnshire