CODEWITHSUNDEEP
sqldatabasehibernaterelational-database
gavenkoa
gavenkoa

Reputation: 48903

What reason to use sequences in databases?

I new to DB/Hibernate and found code:

@SequenceGenerator(name = "entSeq", allocationSize = 5, sequenceName = "CODE_SEQ")
...
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "entSeq")

which set sequence for primary key.

Why was sequences used for values of primary key? Which goals was addressed:

I read about syntax and usage in:

but doesn't found answer for my question.

UPDATE:

I enjoyed reading:

where shown that there is problem in DB theory how to get unique ID for primary keys. That mean that I can make insert into table without providing value for primary key from my own:

    INSERT INTO suppliers
    (supplier_id, supplier_name)
    VALUES
    (supplier_seq.nextval, 'Kraft Foods');

But I expect that this feature must be present in all DB without forcing me to supply primary key values...

Do I think right?

UPDATE2:

Answer for why use START WITH:

This clause can be useful when adding sequences to existing databases. When an older scheme was in use by the application and has already consumed some values from the legal range this clause can be used to skip those consumed values. MINVALUE and MAXVALUE are used to specify the legal range but START WITH would initiate the sequence usage within that range so that previously generated values would not reappear.

UPDATE3: *sequences* provide http://en.wikipedia.org/wiki/Surrogate_key

Upvotes: 3

Views: 1516

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95761

Historically, there were two main reasons.

  • Avoid performance problems with ON UPDATE CASCADE in big tables.
  • Avoid performance problems with joins on wide, natural keys.

Oracle doesn't even support ON UPDATE CASCADE, so updating a value that's used in foreign key references is more troublesome than on other platforms.

Those performance "problems" are much less severe nowadays than they were 20 years ago, given tables of the same size. (Hardware's a lot faster now.) But we seem to deal with much bigger tables now than we did 20 years ago.

There are some undesirable side-effects for this kind of performance tuning.

  • You typically need many more joins than you might with carefully chosen natural keys and ON UPDATE CASCADE. You might need so many that the joins are more costly than the disk read.
  • It's easier to get lost in the joins when you have 20 or 30 of them.
  • Rows are harder to quickly understand. (A row that reads {1, 7, 13, 255, 438} is harder to understand than a row that reads {1, library, checkout, 255, 'A book is your friend'}.)
  • Often, a database designer assigns an ID number as the primary key, but doesn't set any other UNIQUE constraints. That makes the ID number a row identifier, not an identifier for the real-world thing the row represents. That can be a big problem.

Upvotes: 1

Anshu
Anshu

Reputation: 7853

You cannot rely on auto generated keys for all the databases. Unlike most other databases, Oracle does not provide an auto-incrementing datatype that can be used to generate a sequential primary key value.
However, the same effect can be achieved with a sequence and a trigger.

Upvotes: 0

Related Questions