O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required