In most cases, simply creating an Oracle
SEQUENCE with all defaults is good enough:
CREATE SEQUENCE my_sequence;
This
sequence can then be used immediately in triggers when inserting new records in a table:
CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT
ON my_table
FOR EACH ROW
-- Optionally restrict this trigger to
-- fire only when really needed
WHEN (new.id is null)
BEGIN
SELECT my_sequence.nextval
INTO :new.id
FROM DUAL;
END my_trigger;
But if your table has heavy throughput with millions of insertions per day (e.g. a log table), you better configure the sequence cache correctly.
The Oracle manuals state
Note: Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.
We say: Consider using it also in other situations. Why? Sequence values are generated in an autonomous transaction. By default, Oracle caches 20 values before generating new ones in a new transaction. When you have a lot of inserts and thus generate a lot of sequence values, that will result in a lot of I/O on the sequence. Your best technique would be to run benchmarks to find a good value for your sequence cache in high-throughput scenarios.
