In this article, we will explore a crucial data storage structure in the GBase 8s database—Partition Page.
Overview
In the first chunk of each dbspace, the pages following the chunk free list mark the beginning of the tblspace tblspace (partition table).
Tables (or indexes) can be either non-fragmented or fragmented. For non-fragmented tables, there is only one partition. For fragmented tables, there are multiple partitions.
A partition (also referred to as a "fragment") is essentially a tblspace. The metadata for this tblspace is stored in the tblspace tblspace page. The tblspace tblspace manages the metadata for all partitions, similar to how a table manages multiple rows of data. Hence, it is called tblspace tblspace, or the table space that manages table spaces.
The layout of the tblspace tblspace is as follows:
By configuring the TBLTBLFIRST and TBLTBLNEXT parameters in the onconfig file, you can set the size of the first and subsequent extents for the tblspace tblspace. A default value of 0 indicates that the database server will determine the appropriate extent size based on the initial dbspace chunk size.
Each tblspace in the system has a tblspace tblspace page that describes it. For simplicity, we often refer to this special page type as a partition page.
Partition Number (Partnum)
- First 12 bits: dbspace number
- Last 20 bits: Logical page number within the tblspace tblspace
The purpose of the Partnum (a 4-byte hexadecimal code) is to guide GBase8s to the partition page of the table within the tblspace-tblspace. The high 12 bits of the partnum indicate which dbspace contains the table. GBase8s uses these 12 bits to locate the tblspace-tblspace of the target dbspace. A page within the tblspace tblspace describes the target table. GBase8s uses the low 20 bits of the partnum to locate the correct partition page, which contains a logical page number—essentially, the page number within the tblspace tblspace.
Partition Page Layout
General Overview
Each partition page uses the following 5 slots to describe the structure, location, and content of a table within the dbspace:
- Slot 1: Contains 92 bytes of general table information, including the partition number.
- Slot 2: Contains information identifying the partition by database name, table owner, table name, and NLS collation sequence (if applicable).
- Slot 3: Contains descriptive entries for each special column in the table, representing Blob and VARCHAR types.
- Slot 4: Contains key descriptor entries for each index key present in the table. Errors like "Illegal key descriptor: too many parts or too long" refer to issues in Slot 4 of the partition page, not the index itself. Deleting an index requires its key descriptor, so a faulty key descriptor may sometimes require intervention from GBase 8s technical support to fix.
-
Slot 5: Contains extent information. Each 8-byte entry in this slot includes:
- The logical page number of the first page of the extent in the tblspace (4 bytes)
- The page offset of the extent within the dbspace (4 bytes)
- This slot also includes an entry for the logical page number of the next allocated extent.
Tables can also have a sixth slot, but it does not appear on the primary partition page of the table. The sixth slot is used to describe different versions of extents due to in-place table alterations. This slot appears on a separate page within the tblspace tblspace.
Preparing Data and Metadata
DROP DATABASE IF EXISTS hyqdb;
CREATE DATABASE hyqdb WITH LOG;
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 CHAR(10), c3 VARCHAR(10));
CREATE INDEX i1_t1 ON t1(c2, c3);
INSERT INTO t1 VALUES(1, '001', 'v001');
INSERT INTO t1 VALUES(2, '002', 'v002');
Finding the Partition Page
You can use the oncheck -pt database_name:table_name command to find all partitions related to a table. Each partition has its own information.
For t1, which is a non-partitioned table with a primary key index and another index, there are 3 partitions:
- The partition where the data resides.
- The partition where the primary key index resides.
- The partition where the other index resides.
[root@node2 ids]# oncheck -pt hyqdb:t1
TBLspace Report for hyqdb:root.t1
Physical Address 1:11862 // This address will be used later
Creation date 09/13/2024 11:14:56
TBLspace Flags 902 Row Locking
TBLspace contains VARCHARS
TBLspace use 4 bit bit-maps
Maximum row size 26
Number of special columns 1
Number of keys 0
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 8
Number of pages allocated 8
Number of pages used 2
Number of data pages 1
Number of rows 2
Partition partnum 1049253 // This value will be used later
Partition lockid 1049253
Extents
Logical Page Physical Page Size Physical Pages
0 1:13496 8 8
Index 100_1 fragment partition rootdbs in DBspace rootdbs
Physical Address 1:11863 // Used when displaying the primary key index
Creation date 09/13/2024 11:14:56
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 26
Number of special columns 0
Number of keys 1
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 4
Next extent size 4
Number of pages allocated 4
Number of pages used 2
Number of data pages 0
Number of rows 0
Partition partnum 1049254
Partition lockid 1049253
Extents
Logical Page Physical Page Size Physical Pages
0 1:6421 4 4
Index i1_t1 fragment partition rootdbs in DBspace rootdbs
Physical Address 1:11864 // Used when displaying index i1_t1
Creation date 09/13/2024 11:14:56
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 26
Number of special columns 0
Number of keys 1
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 7
Next extent size 7
Number of pages allocated 7
Number of pages used 2
Number of data pages 0
Number of rows 0
Partition partnum 1049255
Partition lockid 1049253
Extents
Logical Page Physical Page Size Physical Pages
0 1:13489 7 7
You can also use the partition number of the table:
oncheck –pt 1049253
The output will be identical to the previous one.
Slot 1: The Partition Structure
The output from the previous section consists of the partition structure (Slot 1) and the extent structure (Slot 5) of the partition page. This section describes the contents of the partition structure (in Slot 1).
The partition structure is stored in the first slot of each partition page and holds general information about the corresponding table. You can view the partition structure of any table by running the following command:
You might recognize some of the general tblspace information stored in the partition structure as table statistics, which are also stored in the system catalog for the optimizer to use. In fact, during some UPDATE STATISTICS operations, some information from the table's partition page is copied to the system catalog, while other information must be collected by reading the tblspace pages themselves. Although the system catalog information may become outdated as the table grows and changes, the information on the partition page should always be accurate. However, the optimizer code is designed to be portable across GBase8s servers, so it does not know how to access partition pages. As far as the optimizer is concerned, the only available table statistics are those stored in the system catalog.
We will now describe the contents of the partition structure in detail.
Physical Address (decimal:decimal)
This is the physical location of the partition page.
You can display the contents of this page using oncheck –pP.
Table Data:
[root@node2 ids]# oncheck -pP 1 11862
addr stamp chksum nslots flag type frptr frcnt next prev
1:11862 5942545 831c 5 2 PARTN 216 1808 0 0
slot ptr len flag
1 24 136 0
2 160 24 0
3 184 12 0
4 196 0 0
5 196 20 0
slot 1:
0: a5 2 10 0 2 9 0 0 1a 0 0 0 1 0 0 0 %...............
16: 1 0 0 8 30 ae e3 66 1 0 0 0 8 0 0 0 ....0.cf........
32: 8 0 0 0 8 0 0 0 1 0 0 0 0 0 0 0 ................
48: ff ff ff ff a5 2 10 0 1 0 0 0 0 0 0 0 ....%...........
64: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ................
80: 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 ................
96: 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 ................
112: 1 0 0 0 89 0 0 0 80 d0 77 0 0 0 0 0 .........Pw.....
128: 0 0 0 0 0 0 0 0 ................
slot 2:
0: 68 79 71 64 62 0 72 6f 6f 74 0 74 31 0 65 6e hyqdb.root.t1.en
16: 5f 55 53 2e 38 31 39 0 _US.819.........
slot 3:
0: e 0 0 0 0 0 a 0 0 0 0 5 ................
slot 4:
slot 5:
0: 0 0 0 0 0 1 0 0 34 b8 0 0 0 8 0 0 ........48......
16: 0 0 0 0 ................
Primary Key Index
[root@node2 ids]# oncheck -pP 1 11863
addr stamp chksum nslots flag type frptr frcnt next prev
1:11863 5942445 82a1 5 2 PARTN 244 1780 0 0
slot ptr len flag
1 24 136 0
2 160 28 0
3 188 0 0
4 208 36 0
5 188 20 0
slot 1:
0: a6 2 10 0 2 8 0 0 1a 0 0 0 0 0 1 0 &...............
16: 1 0 0 8 30 ae e3 66 1 0 0 0 4 0 0 0 ....0.cf........
32: 4 0 0 0 4 0 0 0 2 0 0 0 0 0 0 0 ................
48: ff ff ff ff a5 2 10 0 1 0 0 0 0 0 0 0 ....%...........
64: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ................
80: 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 ................
96: 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 ................
112: 1 0 0 0 89 0 0 0 74 d2 77 0 0 0 0 0 ........tRw.....
128: 0 0 0 0 0 0 0 0 ................
slot 2:
0: 68 79 71 64 62 0 72 6f 6f 74 0 20 31 30 30 5f hyqdb.root. 100_
16: 31 0 65 6e 5f 55 53 2e 38 31 39 0 1.en_US.819.....
slot 3:
slot 4:
0: 98 2 4 1 1 0 4 0 0 0 0 0 a5 2 10 0 ............%...
16: 1 0 0 0 0 0 0 0 4 0 2 0 0 0 0 0 ................
32: 0 0 0 0 ................
slot 5:
0: 0 0 0 0 0 1 0 0 19 15 0 0 0 4 0 0 ................
16: 0 0 0 0 ................
Index i1_t1
[root@node2 ids]# oncheck -pP 1 11864
addr stamp chksum nslots flag type frptr frcnt next prev
1:11864 5942518 82f5 5 2 PARTN 260 1764 0 0
slot ptr len flag
1 24 136 0
2 160 28 0
3 188 0 0
4 208 52 0
5 188 20 0
slot 1:
0: a7 2 10 0 2 8 0 0 1a 0 0 0 0 0 1 0 '...............
16: 1 0 0 8 30 ae e3 66 1 0 0 0 7 0 0 0 ....0.cf........
32: 7 0 0 0 7 0 0 0 2 0 0 0 0 0 0 0 ................
48: ff ff ff ff a5 2 10 0 1 0 0 0 0 0 0 0 ....%...........
64: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ................
80: 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 ................
96: 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 ................
112: 1 0 0 0 89 0 0 0 50 0 78 0 0 0 0 0 ........P.x.....
128: 0 0 0 0 0 0 0 0 ................
slot 2:
0: 68 79 71 64 62 0 72 6f 6f 74 0 69 31 5f 74 31 hyqdb.root.i1_t1
16: 0 65 6e 5f 55 53 2e 38 31 39 0 49 .en_US.819.I....
slot 3:
slot 4:
0: 1 2 0 1 2 0 16 0 0 0 0 0 a5 2 10 0 ............%...
16: 1 0 0 0 4 0 0 0 a 0 0 0 0 0 0 0 ................
32: 0 0 0 0 e 0 0 0 c 0 5 0 0 0 0 0 ................
48: 0 0 0 0 ................
slot 5:
0: 0 0 0 0 0 1 0 0 34 b1 0 0 0 7 0 0 ........41......
16: 0 0 0 0 ................
Creation date
This is the date and time when the table was created.
TBLSpace Flags (decimal)
These flags are operated similarly to page flags, dbspace flags, and block flags, and are combined into an integer through logical OR. Each tblspace flag has the following values and meanings:
- 0x0001: Page-level locking
- 0x0002: Row-level locking
- 0x0004: Tblspace is Bundlespace (online security)
- 0x0008: Marked as DDR replicated partition
- 0x0010: Partition is deleted (shared memory only)
- 0x0020: System-defined temporary table
- 0x0040: User-defined temporary table
- 0x0080: Tblspace used for sorting
- 0x0100: Contains VARCHAR columns
- 0x0200: Contains BLOB space BLOB columns
- 0x0400: Contains BLOB columns resident in the partition
- 0x0800: Requires 4-bit bitmap
- 0x1000: Contains optical BLOB columns
- 0x2000: Partition required for system operation - do not delete
- 0x4000: Temporary table used for special functions - do not update
- 0x8000: Partition is being appended to
Maximum row size (decimal)
For tables with fixed row lengths, this value is simply the row size in bytes. The concept of maximum row size is only required when a tblspace contains VARCHAR columns. Remember that VARCHAR columns define minimum and maximum sizes in characters, which correspond to bytes. The maximum size of a VARCHAR column, plus the 2-byte overhead (for storing the actual size of the VARCHAR data), and the sizes of other columns in the schema, form the maximum row size.
Number of special columns (decimal)
BLOB and VARCHAR columns are considered special and are exclusive column types in GBase 8s.
Number of keys (decimal)
The total number of indexes defined for the table. Composite indexes can consist of several columns but are still counted as a single index key.
Number of extents (decimal)
The number of individual data blocks allocated to the table. The number of extents tends to remain low due to GBase 8s admin extensions and good tblspace management. As the number of data blocks in tblspace increases, not only does data become unpredictably dispersed (causing performance issues with sequential reads), but extension slots on the partition page also increase. Data block slots can only contain so many entries before space runs out. Since other slots on partition pages (like slot 4) can also grow dynamically, the exact maximum number of tblspace extension data blocks cannot be predicted with precision. Verified evidence suggests that the maximum number of extents on a 2K page is about 190.
Current serial value (decimal)
Tblspace can only contain one serial-type column. If it exists, this is the next value used for insertions. If there is no serial column in the table, this value remains 1.
Serial-related types include: serial, serial8, bigserial.
First extent size (decimal)
This is the configured EXTENT SIZE, measured in pages of GBase 8s. The unit here may be confusing because the EXTENT SIZE can be specified in kilobytes through SQL.
The default EXTENT SIZE is 8 pages, independent of page size. The minimum extent size is 4 pages.
Next extent size (decimal)
This is the configured NEXT SIZE, also measured in pages of GBase 8s. Note that at the SQL level, NEXT SIZE is specified in kilobytes, so the value here is initially half or a quarter of the number specified in the SQL NEXT SIZE clause.
Since data block sizes double, the next data block size may increase over time. Unless the table is intentionally changed, the size will never decrease. The default value for the next data block size is 8 pages. The minimum extent size is 4 pages.
Number of pages allocated (decimal)
This is the total number of pages contained within the extended data blocks allocated to the tblspace, regardless of usage.
Number of pages used (decimal)
This is the total number of pages that have been used within the tblspace.
Number of data pages (decimal)
This is the number of data pages currently in use in the tblspace. When all rows are removed from a data page, it will be released to be reused within the tblspace, and the partition structure's data page count will decrease.
Number of rows (decimal)
This indicates the number of rows in the tblspace.
Partition partnum (decimal)
This indicates the partition number of the tblspace.
Partition lockid
In the past, when you locked a table, you were actually locking a partition number. This worked as long as there was a one-to-one correspondence between database tables and partition numbers. However, partitioned tables allow many partitions to be associated with a single database table. GBase 8s uses this lockid value to represent all table fragments, rather than associating each partition number with a single table lock.
Slot 2: partition name
In slot 2:
0: 68 79 71 64 62 0 72 6f 6f 74 0 74 31 0 65 6e hyqdb.root.t1.en
16: 5f 55 53 2e 38 31 39 0 _US.819.........
The PT_NAME in slot 2 is divided by '\0' into four pieces of information:
- dbsname
- owner
- tabname
- nlscollname
Slot 3: column descriptors
In slot 3:
0: e 0 0 0 e 0 a 0 0 0 0 5
The details for each byte are as follows:
-
e 0 0 0:ein hexadecimal (14) represents the offset of this column within the row. -
a 0: This indicates that the maximum length of the VARCHAR field is 10 bytes. -
0 0: This indicates the minimum length of the VARCHAR field is 0 bytes. -
5: The base data type (5 represents VARCHAR).
Slot 4: key descriptors
For the primary key corresponding to the partition partition page:
0: 98 2 4 1 1 0 4 0 0 0 0 0 a5 2 10 0 ............%...
16: 1 0 0 0 0 0 0 0 4 0 2 0 0 0 0 0 ................
32: 0 0 0 0 ................
For the i1_t1 partition partition page:
0: 1 2 0 1 2 0 16 0 0 0 0 0 a5 2 10 0 ............%...
16: 1 0 0 0 4 0 0 0 a 0 0 0 0 0 0 0 ................
32: 0 0 0 0 e 0 0 0 c 0 5 0 0 0 0 0 ................
48: 0 0 0 0 ................
Slot 5: The Extent Slot
Using oncheck -pt hyqdb:t1, the content displayed for each partition's final section represents extent-related information. For example:
Extents
Logical Page Physical Page Size Physical Pages
0 1:13496 8 8
This represents the physical starting position of the data and the number of pages.
According physical address displayed data part (Note: need to jump the bitmap page, so use 1 13497)
[root@node2 ids]# oncheck -pP 1 13497
addr stamp chksum nslots flag type frptr frcnt next prev
1:13497 5942555 99f9 2 1 DATA 64 1972 0 0
slot ptr len flag
1 24 20 0
2 44 20 0
slot 1:
0: 0 0 0 1 30 30 31 20 20 20 20 20 20 20 0 4 ....001 ..
16: 76 30 30 31 v001............
slot 2:
0: 0 0 0 2 30 30 32 20 20 20 20 20 20 20 0 4 ....002 ..
16: 76 30 30 32 v002............
Slot 6: Page Versioning (In-Place-Alter Required)
When performing an In-Place-Alter, this slot tracks which version of metadata the data is based on.
"In-Place-Alter" means modifying the table without immediately updating the data. Data is updated when it changes in the future. During this process, the data will have different metadata versions, and this slot records that version.
Conclusion
Through the analysis of Partition Pages in GBase 8s (GBase数据库), we have not only understood the definition, role, structure, and composition of partition pages, but also mastered their creation, management, and practical applications. Partition pages, as a key data storage structure in GBase 8s, play an important role in improving database storage efficiency, optimizing query performance, and simplifying data maintenance. In the future of database technology development, the application and optimization of partition pages will remain a key research focus. We hope this article provides valuable reference and inspiration to database professionals and enthusiasts, and together, we can explore the infinite possibilities of database technology.
Top comments (0)