TABLESPACE

TABLESPACE

This page shows how to create, modify, and drop tablespaces.
You can create a tablespace for data or index with multiple disks. By doing that, you can improve insert and select performance due to the distributed disk I/O.

• CREATE TABLESPACE

This statement creates a tablespace where the log table or the index of log table are stored. A tablespace can have several disks. When partition files are stored, it is distributed and stored data in multiple disks.

Syntax:
CREATE TABLESPACE tablespace_name DATADISK datadisk_list;
datadisk_list : (data_disk, data_disk,...)
data_disk : (disk_name data_disk_property)
data_disk_property : (DISK_PATH = "path", PARALLEL_IO = parallel_io)

Example:
Mach> CREATE TABLESPACE tablespace1 DATADISK disk1 (DISK_PATH="tbs1_disk1");
Created successfully.

Mach> CREATE TABLESPACE tablespace2 DATADISK disk1 (DISK_PATH="tbs1_disk1", PARALLEL_IO = 5);
Created successfully.

Mach> CREATE TABLESPACE tablespace3 DATADISK disk1 (DISK_PATH="tbs1_disk1", PARALLEL_IO = 10), disk2 (DISK_PATH="tbs1_disk2"), disk3 (DISK_PATH="tbs1_disk3");
Created successfully.

In partition files, tables and indexes are stored respectively. If more than 2 disks are used, several indexes and table files are distributed and stored in disks. Moreover, I/O are performed in each device in parallel and thus, when the number of disks are increased, the disk I/O throughput increases as well. Therefore it has an advantage of saving a large amount of data quickly on disks. When creating the tablespace of the table and index separately and defining each disk, without reconstruction of physical disks, it can separate I/Os of the table and index logically.

DATA DISK
It defines the disk for the tablespace. Each disk has properties below.

disk_name
It specifies a name of a disk. It is used for changing properties of a disk with "ALTER TABLESPACE" statement later.

data_disk_property
It specifies properties of a disk.

DISKPATH
It specifies a directory path for a disk. However, it is required to create the directory beforehand. When setting up a path with a relative path, you have to find the path based on $MACHBASE_HOME/dbs. For example, if the condition is "PATH=disk1", it recognizes the disk path as $MACHBASE_HOME/dbs/disk1.

PARALLEL_IO
It determines how many disk I/O requests can be allowed in parallel. (DEF: 3, MIN: 1, MAX: 128)

• ALTER TABLESPACE

The ALTER TABLESPACE statement is used for changing the information associated with the specified tablespace.
This statement is used for changing the properties of DATADISK of tablespace.

Syntax;
ALTER TABLESPACE tablespace_name MODIFY DATADISK disk_name SET PARALLEL_IO = parallel_io_value;

Example:
Mach>ALTER TABLESPACE tbs1 MODIFY DATADISK disk1 SET PARALLEL_IO = 10;

• DROP TABLESPACE

It drops the specified tablespace.
If objects are still existed in the tablespace, it cannot be dropped.

Syntax:
DROP TABLESPACE tablespace_name;

Example:
Mach>DROP TABLESPACE tbs_data;
Dropped successfully.