The mystery of rdb$db

Home
Up
Next

The mystery of rdb$db_key I.

First, let me state in plain words that db_keys are glorified record positions. Relational theory says nothing about implementation details. Conceptually, a relational database knows nothing about a record number, it only works with set of records. However, it's clear the implementation must deal with these details. IB makes explicit to the user such record position if the user requests this information.

In Paradox and Dbase, using absolute record numbers was a common practice due to the desktop and single user nature of these products, although Paradox has multi user capabilities that makes dangerous assuming too much about absolute positions in a networked environment, because another user can drop a record that was known to exist ten minutes before. In a desktop database, a record number is a fast way to reach the desired record. Almost no translation is needed: given a record-num, the size of each record is known, so the position of the target record is easily calculated and the position to read inside the file holding the record is sent to the operating system if such information is not already in the buffer of the application. When I say "almost no translation" I'm writing from the point of view of the database. Obviously, the operating system must translate a virtual disk address in its file system and pass the raw address to the IDE/SCSI controller than in turn passes such address to the firmware of the hard disk... and you still think it's easy to identify the culprit when a read error happens?

The only official information about db_key that I could find in the IB6 documentation is in Language reference and it's on chapter "System tables and views", where the fields of rdb$relations are detailed:

RDB$DBKEY_LENGTH SMALLINT Length of the database key. Values are:
• For tables: 8.
• For views: 8 times the number of tables referenced in the view definition.
Do not modify the value of this column.

Not too much, after all. But you know that for tables, db_key uses 8 bytes and for a view, if such view joins three tables, then its db_key uses 24 bytes. This is important if you are working with stored procedures and want to keep db_key in a variable. You must use a char data type of the correct length. How does a db_key look in a query? Let's use isql.exe, the command-line utility with an arbitrary table:

SQL> select rdb$db_key from correlativo;
DB_KEY
================
0000008600000001
0000008600000002
0000008600000003
0000008600000004
0000008600000005

So, at first glance, there would be some error in the documentation. To clear the confusion, we will use another table:

SQL> select rdb$db_key from images;
DB_KEY
================
000000B600000002
000000B600000004
000000B600000006
000000B600000008
000000B60000000A

Then you can realize that the trick is only visual: by default, db_keys are shown as hex values, so you need two hex digits to represent a byte and you have 16 hex digits, 8 bytes. The documentation is correct. One implication is that the largest position of a record is (using unsigned numbers) is
256^8=16^16, namely, 2^64 ~ 1.84E19. Knowing that the maximum IB database in theory is
32 TB=2^45 and that one single table can eat these 32 TB, if at first glance you naively think that each db_key value where mapped to a physical location in secondary storage (you will see that this is not true), the range suffices. However, given that databases must be split into files of 2 GB or 4 GB (depending on file system), the calculation is not so simple. In some sense, Interbase is using 64-bit internal addressing, but not at the operating system level: it relies on 32-bit file systems yet.

In some tables, each db_key is one more than the previous while in other tables, each db_key is two more than the previous, for example, so this may be due to the record's length. See the two examples above. While personal digressions may be funny, they may convey wrong ideas to the readers, so I decided to ask an expert and here's the response from David Schnepper:

I think you have a pretty good understanding here. "raw record positions" seem to fit better. (This also leads well to a discussion why you cannot rely on the record position to persist after a backup/restore or after your transaction has committed).
DB Keys are built from <relation number><page number><slot number>
Where page # is relative to relation, and slot # relative to page. This is why the difference between DBKEY values is not uniform.

So, the first lesson to learn is that db_keys don't have to be monotonically increasing, that they are raw positions related to the database itself and not to the operating system's addresses and that they change after a backup and subsequent restore.

If you are interested in an analogy for db_keys, you should read about RIDs (Record Indentifiers) in SqlServer and RowIDs in Oracle. However, in SqlServer, the record identifier is only available to database drivers and not to stored procedures, whereas in Oracle it's exposed to general programmers as it's the case with Interbase.