How They Work
"What is it? It's it" - Epic, Faith No More
Welcome back to this series on working with tablespaces in Navicat 16. Part 1 presented some advantages offered by tablespaces, including Recoverability, Ease of Adding More Tables, Automatic Storage Management, and the Ability to Isolate Data in Buffer Pools for Improved Performance or Memory Utilization. This second instalment will provide more information on what tablespaces are, how they work and the types of default tablespaces you'll find in the various relational database products. The next and final part of the series will focus on how to manage tablespaces in Navicat 16.
Tablespaces As Containers
You can think of tablespaces as containers. These can be a directory name, a device name, or a file name. A single tablespace can have several containers. And, although it is possible for multiple containers (from one or more tablespaces) to be created on the same physical storage device, you will get the best performance if each container you create utilizes a different storage device. The figure below illustrates the relationship between tables and tablespaces within a database:
Tablespaces and the Database Manager
The database manager's role is to balance the data load across containers. As a result, all containers are used to store data to a lesser of greater degree. At the same time, the database manager does not always start storing table data in the first container. The number of pages that the database manager writes to a container before using a different container is called the "extent size".
The figure below shows the components of a tablespace, including the extent size:
Default Tablespaces
Most relational databases come with their own built-in tablespaces. Here are a few examples:
Oracle
Oracle comes with the following default tablespaces: SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP:
- The SYSTEM and SYSAUX tablespaces store system-generated objects such as data dictionary tables. You should not store any objects in these tablespaces.
- The USERS tablespace is helpful for ad-hoc users.
- The UNDOTBS1 holds the undo data.
- The TEMP is the temporary tablespace which is used for storing intermediate results of sorting, hashing, and large object processing operations.
MySQL
Only the InnoDB engine supports tablespaces, as follows:
- The System Tablespace
- File-Per-Table Tablespaces
- Undo Tablespaces
DB2
When you create a new database, the database manager creates some default tablespaces for the database. These tablespaces are utilized as a storage for user and temporary data. Each database must contain at least three tablespaces as given here:
- Catalog tablespace
- User tablespace
- Temporary tablespace
Going Forward
That concludes the second instalment on tablespaces. This instalment provided some information on what tablespaces are, how they work and the types of default tablespaces you'll find in the various relational database products. The next and final part of the series will focus on how to manage tablespaces in Navicat 16.
