Navicat Blog

Navicat 16 and Tablespaces - Part 2 Dec 6, 2022 by Robert Gravelle

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:

DB2 Tablespace RAM and Disk

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:

Oracle Table in a Tablespace

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.

Navicat Blogs
Feed Entries
Blog Archives
Share