DB2 Learning--Index structure

New __ Gradual DB2+DBA system Management, operation and application case

5.3 Index Design

Indexed design Learning notes, and some incomprehensible questions (red is questionable)

Logical table records and index structure for standard tables

1, the data logically, by the list of data pages to organize

2, the data page, according to the size of the extent, logically grouped together.

For example: Extent size is 4, then data page 0-3 makes up the first extent; Data page 4-7 makes up the second extent

3, the size of the data page is certain (4k, 8k, 16k, 32k), however, each record size may be different (even if the same table, the size of the individual data rows may be different). for a given data page (that is, the data page size is fixed.) However, each data row size may vary, and the page contains data items that may change

4, each NO. 500 data page has a FSCR, this fscr is used to map the next 500 data pages (until the next FSCR) for new data rows to use the space

5, the index also needs to be stored, and the index is stored on the index page. Logically, the index pages are organized into B + trees. For DMS table spaces, rids in index pages use relative tablespace page numbers instead of object-relative page numbers

6. Each data page has the same format.

A) Each data page has a page header

b) Behind the header is the slot directory

c) Each item in the slot directory corresponds to a record in the page (and of course, there are several items in the slot directory, there are a few records on this page, which is a few rows of data)

d) The value inside each item in the Slot directory indicates: The byte offset of the corresponding data row in this page relative to the start position

e) The entry with a value of 1 corresponds to the deleted record

For example, for the above figure (the two graphs above are actually the same), the explanation is as follows:

RID is: 473,0

1. The data executed by this index is located at: Data page 473

2, found 473 pages above, the No. 0 slot number

3, the value in this slot number (3800) indicates that the index points to the data row, the byte offset in this page is 3800

4. When the DBMS reads the data pointed to by the index, it can: on page 473, offset 3,800 bytes to read

However, I still have a problem, offset 3800 bytes to read, however, how many bytes to read AH (that is, what is the length of this data line, and, as I understand it, even if the same table, each row of data may be changed AH)

7, after the actual deletion of records, will be left on the data page embedded free space, for these spaces, when reorganizing the table, these embedded free space will be converted to free space (this also means that if the table is not reorganized, the embedded free space will be wasted)

8, tomorrow to understand the index of the B + tree structure