Databases Reference
In-Depth Information
8. Populate the
TRAVELS
table for measuring performance:
SET TIMING ON
INSERT INTO HR.TRAVELS(EMPLOYEE_ID, LOCATION_ID,
START_DATE, END_DATE)
SELECT E.EMPLOYEE_ID, L.LOCATION_ID, SYSDATE, SYSDATE + ROWNUM
FROM HR.EMPLOYEES E, HR.LOCATIONS L;
SET TIMING OFF
How it works...
The relevant part of the code is included in steps 5 and 7. The trigger populates the primary
key field of the
TRAVELS
table. In the first implementation, we use the
TRAVELS_COUNTER
table to obtain a sequential value, while in the second implementation, we use the
TRAVEL_SEQ
sequence to obtain the next sequential number.
The following contain the timing for inserts in steps 6 and 8:
ELAPSED: 1.02
ELAPSED: 0.26
It is easy to see that sequence implementation is faster than using the
TRAVELS_COUNTER
table.
There's more...
In the example used, measuring the time elapsed to insert 2461 (107 employees multiplied
by 23 locations) records in the table, you may wish to use a Statspack report to get more
details from the database statistics. Let's use the following script:
CONNECT hr@TESTDB/hr
CREATE OR REPLACE TRIGGER HR.TR_TRAVELS_INS
BEFORE INSERT ON HR.TRAVELS FOR EACH ROW
WHEN (NEW.TRAVELID IS NULL)
DECLARE MYID HR.TRAVELS.TRAVELID%TYPE;
BEGIN
SELECT ID + 1 INTO MYID FROM TRAVELS_COUNTER FOR UPDATE;
UPDATE TRAVELS_COUNTER SET ID = MYID RETURNING ID INTO :NEW.TRAVELID;
END;
/
