TABLESPACE

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.

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.

results matching ""

    No results matching ""