This is especially important for high update tables with more than 100 row changes per second."The most common issue with updates is the requirement to have a complex SELECT statement is the where clause to identify the rows to be updated.The best techniques for simplifying UPDATE where clauses include: If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
CREATE or REPLACE TRIGGER emp_after_insert AFTER INSERT ON emp FOR EACH ROW DECLARE BEGIN insert into emp_backup values (:new.empid, :new.fname, :new.lname); DBMS_OUTPUT.
If the procedure is expected to mostly insert new records and rarely update existing ones, then use the following pattern (assuming there are unique constraints on the database to prevent duplicate employees being created): This code relies of the database to tell you the record already exists based on the integrity constraints on the table, which is much more efficient and less error prone that attempting to do it yourself.
If the more likely case is that existing records will be updated, the code below is better: begin update employees set ....
His book entitled SQL Performance Explained has become standard reading.“Use The Index, Luke!
” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.