Tids
Every row on every data page is uniquely identified by its page and row, known as its tid, or tuple identifier. Tids are designed to be used internally by the data manager. They are not supported for use in user-written programs.
Note: Tids were not designed to provide unique row identifiers for user data or to provide quick access. Tids are not stored, but are only calculated addresses, so they are unreliable row markers and are likely to change; in short, they must not be used by user programs or queries. We advise that you use tids for informational debugging purposes only. For more information, see the chapter “Understanding the Locking System.”
Tids can be used for direct access into tables. B-tree leaf pages use tids to locate rows on data pages. Also, secondary indexes use tids to indicate which row the key value is associated with in the base table. When a secondary index is used to access a base table, the tid found in the tidp column is used to locate the row immediately in the base table. (The tidp column corresponds to the tid value of the object in the base table.) The base table’s index structure is ignored and access is directly to the page and row.
A listing of the tids in the employee table illustrates tid numbering. The employee table is 500 bytes wide, so four rows fit on each 2048-byte data page.
Tid values start at 0 and jump by 512 each time a new page is encountered. In a page, each row is sequentially numbered:
0, 1, 2, 3, 512, 513, 514, 515, 1024, 1025, and so on.
For example, the relationship of tids to empno’s in the employee table is illustrated as follows:
|empno |tid|
|---------------------------|
| 1| 0| Page 0 Row 0
| 2| 1| Row 1
| 3| 2| Row 2
| 4| 3| Row 3
| 5| 512| Page 1 Row 0
| 6| 513| Row 1
| 7| 514| Row 2
| 8| 515| Row 3
| 9| 1024| Page 2 etc.
| 10| 1025|
| 11| 1026|
| 12| 1027|
| 13| 1536| Page 3
Tids are not stored as part of each row; they are calculated as the data page is accessed.
If overflow pages are encountered, the tid values increase by more than 512; after the overflow chain, they again decrease. Overflow chains are particular to main data pages; however, they are always allocated at the end of the file as they are needed.
To illustrate overflow, assume the employee table was hashed with maxpages = 5. Given the following MODIFY and SELECT statements, the tid numbering is as shown here:
modify emp to hash on empno
where maxpages = 5;
select name, tid from emp;
|name |tid|
|---------------------------------|
|Clark | 0| Page 0
|Green | 1|
|Mandic | 2|
|Robinson | 3|
|Smith |2560| OVERFLOW for Page 0
|Verducci |2561|
|Brodie | 512| Page 1
|Giller | 513|
|Kay | 514|
|Ming | 515|
|Saxena |3072| OVERFLOW for Page 1
Every tid value is unique. When a table is heap, tids always increase in value, because the pages always follow each other. B-tree data pages are not accessed directly, so tid values are not accessed sequentially (data is always sorted by key).
Tid values change as rows move; if a compressed row is expanded, its tid can change; if a key value is updated, the row is moved and the row’s tid changes. Although tids are retrievable, their values are unreliable in application programs. Use tids only to help to understand the structure of tables.
Last modified date: 11/09/2022