Databases Reference
In-Depth Information
See also
F
The
Direct
path
inserting
and
Enabling
parallel
SQL
recipes earlier in this chapter
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 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 (COUNTRY_ID);
CREATE INDEX IX4_MY_CUSTOMERS
ON MY_CUSTOMERS (CUST_STREET_ADDRESS, CUST_POSTAL_CODE,
CUST_CITY, CUST_STATE_PROVINCE);
