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.
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");
Mach> CREATE TABLESPACE tablespace2 DATADISK disk1 (DISK_PATH="tbs1_disk1", PARALLEL_IO = 5);
Mach> CREATE TABLESPACE tablespace3 DATADISK disk1 (DISK_PATH="tbs1_disk1", PARALLEL_IO = 10), disk2 (DISK_PATH="tbs1_disk2"), disk3 (DISK_PATH="tbs1_disk3");
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)
- 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.
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;
- DROP TABLESPACE
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;
Please sign in to leave a comment.