Optimizing PL/SQL Code - Oracle Database 11gR2 Performance Tuning - page 279

2. Create a table and call it LOANS :

CREATE TABLE sh.LOANS (
LOAN_ID INT NOT NULL,
PAYMENT NUMBER,
NUMBER_PAYMENTS NUMBER,
GROSS_CAPITAL NUMBER);

3. Create a trigger on the LOANS table to calculate the GROSS_CAPITAL field,

giving the number of payments and the amount of every single payment:
CREATE OR REPLACE TRIGGER TR_LOANS_INS
BEFORE UPDATE OR INSERT ON sh.LOANS
FOR EACH ROW
BEGIN
:new.GROSS_CAPITAL := :new.PAYMENT * :new.NUMBER_PAYMENTS;
END;
/

4. Insert several rows in the LOANS table and query against it, measuring the

execution time:
SET TIMING ON
INSERT INTO sh.LOANS (LOAN_ID, PAYMENT, NUMBER_PAYMENTS)
SELECT
ROWNUM, AMOUNT_SOLD, QUANTITY_SOLD
FROM SALES;
SELECT COUNT(*)
FROM sh.LOANS
WHERE GROSS_CAPITAL < 10000;
SET TIMING OFF

5. Create a table LOANS_VC with a virtual column for the GROSS_CAPITAL :

CREATE TABLE sh.LOANS_VC (
LOAN_ID INT NOT NULL,
PAYMENT NUMBER,
NUMBER_PAYMENTS NUMBER,
GROSS_CAPITAL AS (PAYMENT * NUMBER_PAYMENTS));