Databases Reference
In-Depth Information
Oracle database doesn't have a specific
IDENTITY
field, to achieve the same result
developers have to write a trigger for the table to assign a value to the "autoinc" field, using
a sequence. This behavior, however, allows developers to implement whatever policy they
want while generating the autoinc field. Sequences can also be used for purposes other than
generating the value for an autoinc field.
Without such a mechanism, a common approach to solve the problem would be to maintain
a counter in a separate table—when a new sequential number is needed, the user locks the
counter table, increments the counter (and uses a new value), and unlocks the counter table.
This approach has serious limitations—every request to the counter table is serialized, and if an
application keeps many counters in the same table, the serialization generates a new record
for every request. If we use the counters to generate the primary key of all (or nearly all) the
tables, our database will insert a single row for one table at a time. This is called a bottleneck,
because each new insert in each table in the database needs to use the counter table.
In this recipe, we use both the sequence and the counter table approach, populated with a
script, to generate the primary keys of a table.
How to do it...
The following steps will demonstrate how to reduce the number of requests to a databse:
1.
Connect to SQL*Plus with user HR:
sqlplus hr@TESTDB/hr
2.
Create the
TRAVEL_SEQ
sequence:
CREATE SEQUENCE TRAVEL_SEQ START WITH 10000 INCREMENT BY 1
CACHE 1000;
3.
Create the
TRAVELS
table:
CREATE TABLE HR.TRAVELS (
TRAVELID NUMBER(9) NOT NULL,
EMPLOYEE_ID NUMBER(6) NOT NULL,
LOCATION_ID NUMBER(4) NOT NULL,
START_DATE DATE,
END_DATE DATE,
CONSTRAINT PK_TRAVELS PRIMARY KEY (TRAVELID),
CONSTRAINT FK_TRAVELS_EMPLOYEES
FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES,
CONSTRAINT FK_TRAVELS_LOCATIONS
FOREIGN KEY (LOCATION_ID) REFERENCES LOCATIONS);
