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. nsert 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 three B-tree indexes on the MY_CUSTOMERS table:

   CREATE UNIQUE INDEX IX1_MY_CUSTOMERS
    ON MY_CUSTOMERS (CUST_ID);
   CREATE INDEX IX2_MY_CUSTOMERS
    ON MY_CUSTOMERS (CUST_LAST_NAME, CUST_FIRST_NAME);
   CREATE INDEX IX3_MY_CUSTOMERS
    ON MY_CUSTOMERS...