Re: sequence number

db2group88_at_yahoo.com wrote:
> before, we use DB2 identity column to generate auto number for each
> row for unique number.our application has thousands table and each one
> required to have one identity column. now we switch to oracle db 10g,
> as i know we can use sequence number to generate the number. but i
> would like to know from performance standpoint. is it better to give
> sequence to each table or use one sequence for the whole database.
> thanks

As far as performance goes, a single sequence is going to create greater latch contention. Here's an asktom thread where he tried to quantify it some.

http://tinyurl.com/6yqh8

"So, I would say there will be a difference based on this, a potentially big difference"

However, I've done benchmarks myself with complete applications that showed almost no measurable difference at all. It's going to depend somewhat on how you're using the sequences (bulk loads vs. discrete transactions for example) and what other work is going on in between. If sequences represent a tiny fraction of the work required then making them a little slower is not going to be of much consequence.

One interesting argument in favor of using a single index is in an ad-hoc environment where casual users will be constructing queries. Since every key will be unique, any incorrect joins will result in no rows returned. Now invalid results might be easily detected by the user but I have seen cases where they weren't and that's a fairly compelling argument to me.

Another argument I've heard is that it makes combining entities into a single table easier since you don't have to assign new keys and fix references. This is primarily geared towards a data warehouse/data mart environment where this type of dimensional refactoring is more likely to be considered.

On the counter side, a single sequence is going to have higher values than separate sequences and that could lead to greater storage requirements. That may be inconsequential for your system but in data warehouses where you may have really huge fact tables that you try and keep very narrow, it can mean a significant increase in size and performance. However, these types of environments also tend to use bitmap indexes on those keys so the values aren't stored repeatedly in the indexes and this argument isn't as compelling in that regard.

--
Richard Kuhler