Databases Reference
In-Depth Information
6.
Execute the same insert statement as in step 3 with the indexes in place, measuring
elapsed time:
SET TIMING ON
INSERT INTO MY_CUSTOMERS SELECT * FROM CUSTOMERS;
SET TIMING OFF
7.
Truncate the
MY_CUSTOMERS
table and drop indexes:
TRUNCATE TABLE MY_CUSTOMERS;
DROP INDEX IX1_MY_CUSTOMERS;
DROP INDEX IX2_MY_CUSTOMERS;
DROP INDEX IX3_MY_CUSTOMERS;
DROP INDEX IX4_MY_CUSTOMERS;
8.
Add a
MAX_CREDIT
field to the
MY_CUSTOMERS
table:
ALTER TABLE MY_CUSTOMERS ADD MAX_CREDIT NUMBER;
9.
Create a trigger on the
MY_CUSTOMERS
table to calculate the
MAX_CREDIT
value
when inserting and updating records on
MY_CUSTOMERS
:
CREATE OR REPLACE TRIGGER TR_MY_CUSTOMERS_BINS
BEFORE INSERT OR UPDATE ON MY_CUSTOMERS
FOR EACH ROW
BEGIN
IF ((:NEW.CUST_GENDER = 'M')
AND (:NEW.CUST_YEAR_OF_BIRTH > 1975)) THEN
:NEW.MAX_CREDIT := :NEW.CUST_CREDIT_LIMIT * 0.95;
ELSE
:NEW.MAX_CREDIT := :NEW.CUST_CREDIT_LIMIT * 1.05;
END IF;
END;
/
10. Insert records in the
MY_CUSTOMERS
table, measuring elapsed time:
SET TIMING ON
INSERT INTO MY_CUSTOMERS (
CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME,
CUST_GENDER, CUST_YEAR_OF_BIRTH, CUST_MARITAL_STATUS,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY,
CUST_CITY_ID, CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID,
