Re: One sequence or many sequences?

"Jack.Lee" <gzjacklee_at_gmail.com> wrote in message news:4086ecc5-6e13-44ee-ae27-babc89c09227_at_x19g2000prg.googlegroups.com...
>I have many tables which need sequences.I think there are two
> solutions.One is that I create each sequence for each table.The other
> is that I create only one sequence for many tables.Which is the better
> one(or another solution)?And Why?
>
> Thanks in advance!
> Jack.Lee

In general I create a sequence for each table because when I am writing code to insert into that table I have an easy method to remember the name of the sequence (due to a naming convention we use). Another consideration is that Oracle has to take a lock out each time it has to get the next set of cached values. For example if you have a cache of 20 on the sequence then every 20th value will need to obtain a lock on the table that deals with sequences, increment that row and release. (to get the next 20 values to dole out.) You can really see this if you use sql loader and make the cache size small and then rerun the test with a large cache size. (cache size refers to the sequence cache size) It can make a huge difference. So if you have every sequence going through 1 sequence you may get some contention that could slow things down. Also I vary the cache size somewhat ont he expected use. If I have a table subject to high insert activity (eg sql loader ) then I have a high cache value for that sequence. If it is a table with very low insert activity. (eg a fairly static look up table with a synthetic primary key) I use the default of 20 values.

Hope that helps.
Jim Received on Wed Jun 25 2008 - 23:11:53 CDT