Variable-Length Records Variable-length records arise in database systems in several ways: Storage of multiple record types in a file. Record types that allow variable lengths for one or more fields such as strings(varchar) Record types that allow repeating fields (used in some older data models). Attributes are stored in order Variable length attributes represented by fixed size (offset,length),with actual data stored after all fixed length attributes Null values represented by null-value bitmap Null bitmap (stored in 1 byte) 0000 21,5 26,10 36,10 65000 10101 Srinivasan Comp.Sci. Bytes 0 4 8 12 2021 26 36 45 Database System Concepts-7th Edition 13.7 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 13.7 ©Silberschatz, Korth and Sudarshan th Edition Variable-Length Records ▪ Variable-length records arise in database systems in several ways: • Storage of multiple record types in a file. • Record types that allow variable lengths for one or more fields such as strings (varchar) • Record types that allow repeating fields (used in some older data models). ▪ Attributes are stored in order ▪ Variable length attributes represented by fixed size (offset, length), with actual data stored after all fixed length attributes ▪ Null values represented by null-value bitmap
Variable-Length Records:Slotted Page Structure Block Header Records Size Entries Free Space Location End of Free Space Slotted page header contains: number of record entries end of free space in the block location and size of each record ■ Records can be moved around within a page to keep them contiguous with no empty space between them;entry in the header must be updated. Pointers should not point directly to record-instead they should point to the entry for the record in header. Database System Concepts-7th Edition 13.8 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 13.8 ©Silberschatz, Korth and Sudarshan th Edition Variable-Length Records: Slotted Page Structure ▪ Slotted page header contains: • number of record entries • end of free space in the block • location and size of each record ▪ Records can be moved around within a page to keep them contiguous with no empty space between them; entry in the header must be updated. ▪ Pointers should not point directly to record — instead they should point to the entry for the record in header
Storing Large Objects E.g.,blob/clob types Records must be smaller than pages Alternatives: Store as files in file systems Store as files managed by database Break into pieces and store in multiple tuples in separate relation ·PostgreSQL TOAST Database System Concepts-7th Edition 13.9 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 13.9 ©Silberschatz, Korth and Sudarshan th Edition Storing Large Objects ▪ E.g., blob/clob types ▪ Records must be smaller than pages ▪ Alternatives: • Store as files in file systems • Store as files managed by database • Break into pieces and store in multiple tuples in separate relation ▪ PostgreSQL TOAST
Organization of Records in Files Heap-record can be placed anywhere in the file where there is space ■ Sequential-store records in sequential order,based on the value of the search key of each record ■ In a multitable clustering file organization records of several different relations can be stored in the same file Motivation:store related records on the same block to minimize 1/O B+-tree file organization Ordered storage even with inserts/deletes More on this in Chapter 14 Hashing-a hash function computed on search key;the result specifies in which block of the file the record should be placed More on this in Chapter 14 Database System Concepts-7th Edition 13.10 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 13.10 ©Silberschatz, Korth and Sudarshan th Edition Organization of Records in Files ▪ Heap – record can be placed anywhere in the file where there is space ▪ Sequential – store records in sequential order, based on the value of the search key of each record ▪ In a multitable clustering file organization records of several different relations can be stored in the same file • Motivation: store related records on the same block to minimize I/O ▪ B+ -tree file organization • Ordered storage even with inserts/deletes • More on this in Chapter 14 ▪ Hashing – a hash function computed on search key; the result specifies in which block of the file the record should be placed • More on this in Chapter 14