JPH052515A - Optimized reorganization system for data base - Google Patents

Abstract

PURPOSE:To automatically execute the performance tuning of the data structure of a relational data base, an index, a buffer, etc., at reorganization timing. CONSTITUTION:An access pass at the time of executing the relational data base 1 is traced by an ATP file 4 and a DB body 2 and the index 3 are optimized by an optimized reorganization utility 5. Since optimization is automatically executed by tuning at the time of data rearrangement of the data base which is called as reorganization, an optimum data base for a pattern to be made access can be obtained.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to an optimized reorganization for automatically improving the performance of relational database data structures, indexes, buffers and the like in accordance with past usage patterns.

[0002]

2. Description of the Related Art Conventional database access optimization is performed by query optimization.
J Date, Addison Wesley, 1986, pp. 333-340 (An Introduction to Databas
e Systems, CJ Date, ADDISON-WESLEY, 1986, pp333-p
p340).

Optimization in database access is
It is a method of query optimization, and in the current database structure and index allocation situation, the cost of multiple existing access paths is evaluated and the optimum path is selected.

[0004]

In the above-mentioned conventional technique, the storage of the database is given, and only the access method is optimized, and the storage itself of the database is optimized. However, there was a problem in optimization when there was a problem in the storage method or storage format.

An object of the present invention is to optimize a database storage method, and further, to optimally arrange indexes and optimally allocate a buffer amount to bring a database system close to an optimal state and utilize learning effects. And maintain the optimum state.

[0006]

SUMMARY OF THE INVENTION An object of the present invention is to provide a relational database that manages data as a relation, with means for accumulating, as trace information, each access method to data of a data operation request issued from each user program. When performing database reorganization at regular intervals, referring to the trace information, means for converting to an optimum storage order, necessity of index, necessary amount of buffer, storage definition of the database from the converted information, This can be achieved by having a means for creating an index definition and a buffer amount designation parameter.

[0007]

[Operation] Information on the data access method of the database is
At the time of access, it is accumulated in the access path trace buffer and stored in the access path trace file on the external storage device at regular intervals. The information is converted into an optimum storage order, necessity of index, and required amount of buffer at the time of database reorganization, and a database storage definition, index definition, and buffer amount designation parameter are created. Optimal database storage can be obtained by storing data in a database created based on the definition, creating an index, and changing parameters. Further, a learning effect can be obtained by repeating the optimization reorganization.

[0008]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS An embodiment of the present invention will be described in detail below with reference to the drawings.

FIG. 1 shows an overall configuration diagram of an optimized reorganization system embodying the present invention.

In FIG. 1, the optimized reorganization system is
Relational database management system 1
(Hereinafter, simply referred to as a DBMS), a database entity 2 and an index 3 under it, an access path trace file 4 (hereinafter, referred to as an APT file) that stores information on a data access method, and a main storage device. It comprises an APT file buffer 41, a utility 5 for reorganization, and a backup medium 6 for storing data for backup.

In this embodiment, an optimized reorganization system for optimizing the database 2 for reorganization based on the data operation utilization information of the relational database 1 is shown.

In the logical view of a relational database, the column order and the row order are independent of the storage method, but when actually storing data, the column order and the row order greatly affect the data access performance. Therefore, necessary information is acquired when the data operation is performed.

FIG. 2 shows the relationship between the logical structure of the relational database (the structure visible from the user program) and the physical arrangement. In a relational database, the data looks like a table and is represented as a set of rows 201 composed of multiple columns 202. Although these data are stored in the external storage device, the order of the row 201 is stored in the order of the clustering key 203, and the order of the columns is the order at the time of definition. Each row 201 maps to a record 204. Furthermore, a plurality of records 204 are assigned to a storage unit called an page 205 on an external device, and a page is used as an input / output unit.

Index 3 can be set for each record 204 as required. The index has a tree structure of key values using a specific column as a key, and it is possible to obtain a corresponding record from the key value at high speed.

FIG. 3 shows the structure of the APT file 4. APT
The file 4 is a column that is used as information stored in the APT file 4 that is used as information for optimization when the database access path to the database in response to a data operation request from the user program is traced. There are position information 401, line order information 402, index required information 403, index usage information 404, index update information 405, table combination information 406, and buffer usage information 407, which can be added as necessary.

FIG. 4 is a block diagram of the optimization reorganization utility 5. The optimization reorganization utility takes as input the access path information stored in the database entity 2, index 3, and APT file 4. The backup unit 501 first backs up the contents of the current database on the backup medium 6. Next, the optimization unit 502 generates optimized definition information and optimized parameter information 504. Finally, the reorganization unit 503 reloads the information on the backup medium 6 into the database created based on the new definition. Finally, the list 505 of the change information list changed for optimization is output.

FIG. 5 shows a flow of determining an access path when executing a data operation. First, the data operation statement (SQL statement) passed from the user program is received by the SQL statement input processing 601, the syntax analysis processing 602 checks the grammar, and then the semantic analysis processing 603 executes the existence of the table 200 and the column 202. To confirm. After that, in the access path optimizing process 604, the optimum one of the current indexes is selected, the execution order is determined, and unnecessary processes are eliminated.

Finally, in execution process 605, actual data access is performed based on the access path optimized in the access path optimization process 604.

When an access path for searching all records is created because there is no index in the access path optimizing process 604, the access path information indicating the index requirement is APT.
Output to the file buffer 41. Also, the execution process 60
5, column position information 401, row order information 402, index required information 403, index usage information 404,
The index update information 405, the table combination information 406, and the buffer use information 407 are accumulated in the APT file buffer 41 and output to the APT file 4 at regular intervals.

The column position information 401 is composed of the order of the column when the column name to be accessed is accessed at the same time. The row order information 402 is composed of column (key) names used when sequentially accessing a specific column when accessing the table.

The index required information 403 is made up of a column name and a target table name that will improve performance if an index exists. The index usage information 404 includes the column name and table name of the index actually used. The index update information 405 includes the name of the indexed column in which the update has occurred and the table name.

When the table join occurs, the table join information 406 includes the column (join key) used for the join and the access count of the join key.

The buffer use information 407 includes the number of buffer planes required for each data operation statement and the name of the accessed table.

The access path information collected in the APT file is used when the optimizing reorganization utility 5 is executed.

FIG. 6 shows the flow of execution of the optimized reorganization utility. First, in the backup creation processing 701, the contents of the database are read and written in the backup medium 6. Optimize after the backup is completed. In the optimization, the contents of the APT file are read (process 702) and organized for each information. From the column position information 401, Table 2
The columns are arranged in descending order of access frequency for each 00 (process 704, process 705).

From the row order information 402, the largest order of the access order for each table 200 is set as a clustering key (process 706, process 707).

From the buffer use information 407, the amount of buffer required during a certain period is calculated to be 90%.
The parameter is output so that the amount that can satisfy the required amount in the period of is set as the new buffer designation value (process 708, process 70).
9).

A key to be defined as an index and its expected usage frequency are obtained from the index information 403 and index usage information 404, and the index update information 40 is obtained.
The unnecessary index key and its update frequency are obtained from 5 (process 710, process 711).

If the index usage frequency is higher than a certain level and the key has no index, a new index is created. If the frequency of updating unnecessary indexes is above a certain level and the usage frequency is below a certain level, delete the index.

From the number of times the combined key is accessed in the table combination information 406, if the access frequency is at a certain level or higher, an index is set.

Finally, a new optimum parameter is created based on the information set in the optimization phase (process 712, process 713) and the data that has been downloaded as a backup is stored in a new database (process 714). In addition, index parameters are created (process 715), and indexes are collectively created (process 716). A parameter for buffer definition is also created (process 717).

This completes the execution of the optimization reorganization utility. The reorganized database has a more effective database structure that reflects the access tendency so far.

If it is dangerous to automatically perform the optimized reorganization, the points to be optimized may be output to a report (process 718), it may be judged whether the change is possible, and then the reorganization may be performed. ..

[0034]

According to the present invention, a more optimal database storage method can be automatically obtained by using the past access patterns to the database.

Further, the database storage design and the buffer design which require high technology can be automatically performed while learning the past movements, and the optimum database storage method can be obtained.

[Brief description of drawings]

FIG. 1 Overall configuration diagram of the optimized reorganization system

[Figure 2] Logical configuration diagram of relational database

[Figure 3] APT file structure diagram

[Figure 4] Configuration diagram of the optimization reorganization utility

FIG. 5: Flow chart for determining access path of relational database

FIG. 6 is a flowchart of execution of the optimizing reorganization utility.

[Explanation of symbols]

1 ... Relational database management system 2 ... Database entity 3 ... Index 4 ... Access path trace file (APT file) 5 ... Utility for reorganization 6 ... Backup medium 41 ... APT file buffer 200 ... Table 201 ... Row 202 ... Column 203 ... Clustering key 204 ... Record 205 ... Page 401 ... Column position information 402 ... Row order information 403 ... Index required information 404 ... Index usage information 405 ... Index update information 406 ... Table join information 407 ... Buffer usage information 501 ... Backup section 502 ... Optimal Optimization unit 503 ... Reorganization unit 504 ... Optimized definition information and optimization parameter information 505 ... Change information list list 601 ... SQL statement input process 602 ... Syntax analysis process 603 ... Semantic analysis process 604 ... Access path optimization processing 605 ... Execution processing 701 ... Backup creation processing 702 ... APT file content reading processing 703 ... APT file end determination processing 704 ... Column optimization determination processing 705 ... Columns in descending order of access frequency Reordering process 706 ... Clustering optimization determination process 707 ... Process for setting the largest order from the row order information as a clustering key 708 ... Buffer optimization determination process 709 ... Change buffer parameter output process 710 ... Index optimization determination process 711 ... Index Process for obtaining unnecessary index key and its update frequency from update information 712 ... Optimal parameter creation process 713 ... Table definition execution process 714 ... Process of storing siphoned data 715 ... Index parameter creation process 716 ... IN Batch creation processing 717 ... also to create processing 718 ... configuration change report output processing parameters for the buffer definition of box

Claims (1)

Claim: What is claimed is: 1. In a relational database that manages data as a relation, means for accumulating, as trace information, a method of accessing a database for a data operation request issued from each user program, and for a fixed period. When performing database reorganization, a buffer amount specification parameter is used to determine the optimal storage order of databases and the necessity of indexes by referring to the accumulated trace information, and to secure the buffer amount required for database reorganization. An optimized reorganization method for a database, characterized by having a means for creating.