Can you please let me know why the size of the primary key cannot exceed approximately one database block?
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.