A partitioned table is a base table whose data is stored in a number of user spaces. The table data contained on a single space is referred to as a partition. All partitions in a table must be located on different spaces. 2 to 16 partitions are possible per table. In SESAM/SQL the data is distributed to the partitions row by row; the allocation criterion is the primary key of a row.
Partitioned tables have the following advantages over non-partitioned tables:
Particular criteria, e.g. by month, can be used to structure the user data in a straightforward manner and to store it in different partitions or user spaces. A partition then contains the user data which is currently accessed.
When suitable structuring/partitioning is used, the size of the user spaces which are currently accessed can be reduced.
Access to the user spaces which are currently required is possible even when other partitions or user spaces of the table are not available, see the section “Partial availability”.
The backup and repair times can be reduced since the individual user spaces are smaller.
The table can be larger than 64 GB.
The table below shows the most important properties of non-partitioned and partitioned tables:
Property | Non-partitioned table | Partitioned table |
Number of user spaces | 1 | 2 to 16 |
Primary key | Optional | Mandatory |
Modifying the primary key | Permitted | Not permitted |
Maximum number of rows | Approx. 4.3 billion | Approx. 268 million per partition, with |
Smallest backup and repair | Complete table | One partition |
Metadata | INFORMATION_SCHEMA:
| INFORMATION_SCHEMA:
|
SYS_INFO_SCHEMA:
| SYS_INFO_SCHEMA:
| |
CALL DML only tables | Possible | Not possible |
Password protection with | Possible | Not possible |
Table 15: Properties of non-partitioned and partitioned tables
1“_PARTITIONS_” is entered for the space name
Further properties of partitioned tables
The partitioning of a partitioned table can be altered or canceled with the utility statement ALTER PARTITIONING FOR TABLE, see section “Changing the partitioning of a base table” and the “ SQL Reference Manual Part 2: Utilities”.
Partition boundaries can also be altered by exporting the table into an export file, deleting the existing table with DROP TABLE, and then importing the export file as a partitioned table with the same or a different name and different partition boundaries.
The definition of columns, integrity constraints, indexes, and default values always relates to all partitions.
Indexes created implicitly or explicitly without the USING SPACE clause are always stored on the user space of the first partition.
In the case of CHECK FORMAL, error files which are created by SESAM/SQL are created for each space affected. In the case of LOAD and UNLOAD, one error file is created for all partitions.
Other base tables and indexes or a partition of another table may be stored on a user space on which a partition is stored. However, this procedure is not recommended as the benefit of “small backup and repair units” is reduced.
Partial availability
For many types of access to a suitably partitioned table only the user spaces which are currently required need be available. Other partitions or user spaces in the table do not have to be available. This is referred to as partial availability of partitions.
In addition, a distinction is made between physical and logical availability of a partition.
Physical and logical availability
Physically available means that the space belonging to the partition can be accessed physically and that the space is in the “space o.k.” status.
Logically available means that the space belonging to the partition is flagged as physically available within SESAM/SQL. This flag is used to expedite the accesses as only the flag is checked and not the space itself. The flag is created or updated when a partitioned table is accessed for the first time in a DBH session using a DML statement. The same applies for the first access after a database has been entered in the SQL table catalog or after a DBH start or DBH restart.
The physical and logical availability plays a role particularly in DML, DDL, and utility statements:
DML statements require that the partitions affected should be logically available. Decisive here is the data that is searched, not the number of hits. In other words all partitions which are involved in a search operation must be logically available. When you add a row it is, for example, possible that an adjacent partition must also be searched.
DDL statements require that the partitions affected should be physically available. With the CREATE INDEX, DROP INDEX, ALTER TABLE and DROP TABLE statements all partitions must be physically available. With the CREATE TABLE statement all spaces on which the partitioned table is to be created must be physically available.
In most cases utility statements require that the partitions affected should be physically available. The LOAD ONLINE, UNLOAD ONLINE ... WHERE, EXPORT ... WHERE and CHECK CONSTRAINTS statements are an exception. Details on the availability of partitions for utility statements are provided in the “ SQL Reference Manual Part 2: Utilities”.
Changing logical availability
The logical availability of a partitioned table remains unchanged during a DBH session even if the physical availability of partitions changes. Only after RECOVER SPACE is the logical and physical availability of all specified user spaces redefined.
The following administration statements enable you to obtain information on and change the logical availability:
SHOW-PARTITIONS enables you to obtain information on the logical availability of the individual partitions of a partitioned table.
CLOSE-SPACE enables you to set all the partitions located on this space to “logically not available”.
REUSE-PARTITIONS enables you to update the logical availability of the partitions of a partitioned table. Partitions which were until now physically but not logically available are then set to “logically available”.