SEQUENCEwith all defaults is good enough:
This sequence can then be used immediately in triggers when inserting new records in a table:
CREATE SEQUENCE my_sequence;
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
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;
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.