Inspecting indexes and triggers overhead

In this recipe we will see the overhead introduced by indexes and triggers on DML operations. We will explore alternative ways to implement calculated fields using virtual columns instead of triggers.

How to do it...

The following steps will demonstrate the index and trigger overheads:

  1. Connect to the SH schema:
    CONNECT sh@TESTDB/sh
    
  2. Create an empty table MY_CUSTOMERS, copying the CUSTOMERS table structure:
    CREATE TABLE MY_CUSTOMERS AS
    SELECT * FROM CUSTOMERS WHERE ROWNUM < 1;
    
  3. Insert all of the records from CUSTOMERS to MY_CUSTOMERS, measuring time:
    SET TIMING ON
    INSERT INTO MY_CUSTOMERS SELECT * FROM CUSTOMERS;
    SET TIMING OFF
    
  4. Truncate the MY_CUSTOMERS table:
    TRUNCATE TABLE MY_CUSTOMERS;
    
  5. Add a unique index and ...

Get Oracle Database 11gR2 Performance Tuning Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.