Ask the Expert

Why can't size of primary key exceed one database block?

Can you please let me know why the size of the primary key cannot exceed approximately one database block?

    Requires Free Membership to View

Actually, the total size limit for any index as noted in the Oracle9i Reference guide is "75% of the database block size minus some overhead". Remember that in Oracle9i you can have multiple block sizes from 2K to 32K for use. That means your key sizes could vary greatly depending on the blocksize used by the tablespace in which you store your data/indexes.

Primary keys should be "short and dumb". The smaller they are the better they perform. Sequential, non-repeating number values that aren't meaningful in any way make the best primary keys.

Why do they have a limit? I didn't look up an "official" explanation but think about this: If you had to scan more than one block worth of data in order to determine uniqueness wouldn't that be a performance problem and thus defeat the purpose/concept of primary keys? Basically, row Chaining is always a performance issue to some degree.

Also remember that indexes contain the key columns and the rowid for every row of data in your table. If indexes were allowed to be larger than a block then the indexes would be highly unmanageable.

In the end, I guess my question back would be "why would a primary key ever need to be larger than one block?"

Fellow Oracle expert Brian Peasland adds:

I agree with Karen on why a primary key value should not exceed one Oracle block, especially when that block can be a very large size, up to 16K or 32K depending on the platform. A key value should not be that big. If it is, it might be time to rethink the design.

Oracle 9i makes this problem even easier to deal with when using multi-block support. Even if your default block size is 2K in your Oracle 9i database, and your primary key is 6K, you can create a tablespace which will hold segments with an 8K block size and place your index there.

There is a technical reason why a primary key value can not exceed one database block. In a table, one row can exceed one database block. If the row is too big to fit into one block, then the data is spread among multiple blocks. The database can find this row's data because there is a pointer in the block which tells the database where the rest of the row resides. This is called "row chaining". Oracle indexes simply do not allow chaining of any values. This functionality was never built into the B-tree index structure. The absence of the chaining mechanism in an index leaf node is the technical reason why a primary key value can not fit into more than one block. This applies to all indexes, not just those designated as the indexes to support the Primary Key constraint.

For More Information


This was first published in May 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: