5.6.1 Points to consider when designing a data DB area

(1) Points to consider about the data DB area for storing tables and indexes

Consider the following points when storing tables and indexes in a data DB area:

• If a table to be stored in a data DB area has a large amount of data, store only the table in the data DB area.

• If an index to be stored in a data DB area has a large amount of data, store only the index in the data DB area.

• If tables or indexes to be stored in data DB areas have a small amount of data (for example, several megabytes), storing multiple tables or indexes in a single data DB area causes no problem.

• When defining a column store table, prepare a dedicated data DB area for storing only that column store table.

We do not recommend that you store multiple tables or indexes that have a large amount of data in a single data DB area. If you do so and perform a search such as shown below, I/O operations might concentrate on the same DB area, thus degrading search performance:

• Multiple tables are stored in a single data DB area and a search using an SQL statement that joins tables is executed

• Multiple indexes are stored in a single data DB area and a search that uses indexes is executed

Issues like the following might also arise:

• While an operation (SQL statement or command) is being executed on a particular table, a lock placed on a data DB area prevents operations from being executed on other tables stored in the same data DB area.

• When sharing a global buffer, execution of an SQL statement for a particular table impacts the data retrieval performance for other tables stored in the same data DB area.

  The performance of data retrieval in row store tables or by using indexes might be degraded, especially in the following case: the same global buffer is allocated to a data DB area that stores column store tables and to a data DB area that stores row store tables and indexes.

To Page Top

(2) Points to consider about the data DB area for storing range indexes

Consider the following points, which are unique to range indexes:

We recommend that you not store any other tables or indexes in a data DB area that stores tables for which range indexes are defined. Storing other tables or indexes might increase the range index size.

To Page Top

(3) Points to consider about the data DB area files that comprise a data DB area

If one data DB area consists of multiple data DB area files, the workload can be distributed because the data DB area files are accessed in parallel. For this reason, consider using multiple data DB area files for a data DB area that stores tables and indexes that are accessed frequently. A guideline for the number of data DB area files to make up one data DB area is 20 to 40 percent of the CPU cores in the machine on which the HADB server is installed.

The following are benefits of storing multiple data DB areas files in one data DB area:

• When executing the adbimport command to import data

  One processing real thread is allocated to each data DB area file, so data storage processes and index creation processes are performed in parallel.

• When allocated segments are released

  One processing real thread is allocated to each data DB area file, so segment release processes are performed in parallel.

To Page Top