Databases Reference
In-Depth Information
COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID,
CUST_SRC_ID, CUST_EFF_FROM, CUST_EFF_TO, CUST_VALID)
SELECT 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,
COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID,
CUST_SRC_ID, CUST_EFF_FROM, CUST_EFF_TO, CUST_VALID
FROM CUSTOMERS;
SET TIMING OFF
11. Drop the trigger and the
MAX_CREDIT
column from the
MY_CUSTOMERS
table and
truncate the table to empty data:
TRUNCATE TABLE MY_CUSTOMERS;
DROP TRIGGER TR_MY_CUSTOMERS_BINS;
ALTER TABLE MY_CUSTOMERS DROP COLUMN MAX_CREDIT;
12. Recreate the
MAX_CREDIT
field as a virtual column, reproducing the same effect
as the previous trigger:
ALTER TABLE MY_CUSTOMERS ADD MAX_CREDIT AS (CASE
WHEN CUST_GENDER = 'M' AND CUST_YEAR_OF_BIRTH > 1975 THEN
CUST_CREDIT_LIMIT * 0.95
ELSE CUST_CREDIT_LIMIT * 1.05
END);
13. Execute the same insert as in step 10, 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,
COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL,
CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID,
CUST_SRC_ID, CUST_EFF_FROM, CUST_EFF_TO, CUST_VALID)
