Database System Concept 12-organization of documents

A database is mapped to a number of different files, which are maintained by the underlying operating system. Each file is divided into a fixed-length storage unit called a block (Bolck), which is the basic unit of storage allocation and data transfer. The default block of the database is between 4-8k. Usually no records are larger than blocks (large files such as picture audio are not considered first), and each record is also required to be saved in a single block.

First, the fixed length record
The attributes of the instructor table are: ID char (5); Name Char (20). Assuming that each character occupies 1 bytes, each record occupies 25 bytes, the records cannot be stored sequentially, otherwise there is a cross-block situation, and when the record is deleted, the subsequent records move forward in a very expensive way. To solve the cross-block problem, each block stores only the entire number of records, and the extra space is temporarily unused. When you delete a record, you can move the last record to the location of the deleted record, rather than moving all subsequent records forward in turn. Also, considering that the insert operation is often more frequent than the delete operation, you can take the space left after the record is deleted for the next insert data.
But when inserting records, how to find the location of deleted records: You can leave a certain space in the file head to store the location of the first deleted record, after the second record is deleted, its position is recorded in the first delete record location, in turn, to form a diagram of the situation:

The deleted records form a linked list (linked list), which is also known as the free lists. Each time new data is inserted, the address pointed to by the header is inserted, and the address in the header is updated to the address of the next deleted record.

Second, variable length record

A) Storage mode
When a data table contains variable-length properties, it involves the storage of variable-length records. Regardless of the variable length storage technology, you need to solve these two basic problems: how to describe a record, you can easily get its properties; How to store a record in a block makes it easy to get to.
A record with a variable-length property usually has two parts: the initial part of the fixed length, and then the variable-length part immediately following it. The fixed-length portion is expressed in such a format (offset, length) that the offset indicates the starting position of the data, and the length is the length of the variable-length part. The variable-length portion is continuously stored after the fixed-length section.

Example is instructor (ID varchar (5), name varchar;d ept_name varchar, salary numeric (8,2)) storage, id name Dept_ The name is variable length (assuming an offset and a length of 2 bytes respectively); The salary is fixed-length, so it is stored directly.
Also demonstrates the use of the empty bitmap (null bitmap), if the attribute salary is empty, the 4th bit of the empty bitmap is set to 1, and the 12-19 bits of the stored salary are ignored. A 1-byte empty map can handle a table structure of up to 8 properties, and if there are more attributes, the length of the empty graph needs to be expanded. This method saves storage space at the expense of additional empty graph reads, and is useful in tables with many attributes and many of these properties being null.

B) storing variable length records in blocks
When storing variable-length records in a block, the slot structure (slotted-page structure) is typically used for paging, with headers in each block:

The header contains information about the number of entries in the block, the end of the free space, the entry for each record (entry, which contains the record location and size). Records are stored continuously in blocks, but are saved from the end of the block. Each time a new record is inserted, it is placed at the end of the free space, and the entry information of the record is saved in the header, and when a record is deleted, the records before the deleted record move sequentially to occupy the space of the deleted record. Since the volume of each block is not too large between 4-8k, the overhead of this movement is relatively small. In a paged slot structure, the data pointer does not point directly to the data, but to the entry (entry) of the data, so the data can be moved arbitrarily in the block, which avoids the fragmentation in the block.

Learning materials: Database System concepts, by Abraham Silberschatz, Henry F.korth, S.sudarshan

Database System Concept 12-organization of documents