Table of Contents
This chapter explains 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.
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.
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)
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.
It defines the disk for the tablespace. Each disk has properties below.
It specifies a name of a disk. It is used for changing properties of a disk with "ALTER TABLESPACE" statement later.
It specifies properties of a disk.
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.
It determines how many disk I/O requests can be allowed in parallel. (DEF: 3, MIN: 1, MAX: 128)
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.
ALTER TABLESPACE tablespace_name MODIFY DATADISK disk_name SET PARALLEL_IO = parallel_io_value;
Mach>ALTER TABLESPACE tbs1 MODIFY DATADISK disk1 SET PARALLEL_IO = 10;
It drops the specified tablespace. If objects are still existed in the tablespace, it cannot be dropped.
DROP TABLESPACE tablespace_name;
Mach>DROP TABLESPACE tbs_data; Dropped successfully.