WO2012117438A1

WO2012117438A1 - Database management device, database management method, and database system

Info

Publication number: WO2012117438A1
Application number: PCT/JP2011/001154
Authority: WO
Inventors: 五十嵐和人; 矢田部英次; 千田理路
Original assignee: 株式会社日立製作所
Priority date: 2011-02-28
Filing date: 2011-02-28
Publication date: 2012-09-07

Abstract

Provided is a database management device, whereby it is possible, when accessing data according to a work program query, said data being stored in a database region, to shorten time required to access the data by using an index which is stored in an index storage region. The data management device comprises a control unit which, when creating the index, computes, on the basis of the degree to which the access time can be shortened by using the index and the storage capacity of the index storage region which is consumed by creating the index, priorities for a plurality of paths of the data for which indices are to be created, and creates only the index of the paths on the basis of the computed priorities.

Description

Database management apparatus, database management method, and database system

The present invention relates to a database management apparatus, a database management method, and a database system, and is suitably applied to, for example, a database management apparatus, a database management method, and a database system for managing an XML (Extensible Markup Language) database.

If the XML database does not define an XML schema for an XML type column in a relational database (RDB: Relational Data Base), it can be stored in any well-formed XML document. Is possible.

Therefore, in the XML database, when the schema of the XML document stored in the XML type column of the related database changes due to the addition of new elements or attributes during operation, the database administrator changes the schema or query. It is required to reorganize the index according to the situation.

Therefore, according to Patent Document 1, when redesigning an index, the priority to be tuned can be determined based on the execution frequency of the inquiry from the business program, and the index can be reorganized based on the determined priority. Technology is disclosed.

JP 2007-122405 A

However, conventionally, when a database administrator reorganizes an index, there is a problem that it is difficult to construct an index that achieves a large effect in a limited storage area because the size of the storage area for storing the index is not considered.

Conventionally, when the database administrator reorganizes the index, the tuning priority is calculated based on the frequency of execution of the query. Therefore, even if the frequency of execution is low, the effect of tuning on a query with a long processing time is low. There was a problem.

The present invention has been made in view of the above points, and intends to propose a database management apparatus, a database management method, and a database system that can exhibit high performance while effectively using a limited storage area.

In order to solve such a problem, in the present invention, when accessing data stored in a database area in response to a query statement from a business program, the data storage area is stored using an index stored in the index storage area. In a database management apparatus that accesses the data based on the search result, a statistical information acquisition unit that acquires statistical information about a path of data stored in the database storage area, and an inquiry time for the path A query time reduction amount calculation unit that calculates a query time reduction amount that can be reduced when the index is used based on the acquired query time, and an index for the path is created. The created index is stored in the index storage area. A storage area consumption calculating unit for calculating a storage area consumption consumed in the case of the storage, and a priority for a plurality of the data paths to be indexed based on the inquiry time reduction amount and the storage area consumption A priority calculating unit that calculates a degree; and an index deletion creating unit that creates an index of the path based on the priority.

In the present invention, when accessing data stored in the database area in response to a query from the business program, the data storage area is searched using an index stored in the index storage area, In the database management method of the database management apparatus that accesses the data based on the search result, a first step in which a statistical information acquisition unit acquires statistical information on a path of data stored in the database storage area; A query time reduction amount calculation unit obtains a query time for the path from the statistical information, and calculates a query time reduction amount that can be reduced when the index is used based on the acquired query time. And the storage area consumption calculation unit calculates an index for the path. A third step of calculating a storage area consumption amount that is generated when the generated index is stored in the index storage area, and a priority calculation unit includes the query time reduction amount and the storage area consumption amount And a fourth step of calculating a priority for a plurality of paths of the data to be indexed, and a fifth index deletion creation unit creates an index of the path based on the priority. These steps are provided.

Further, in the present invention, in the database system in which the terminal device, the database management device, and the storage device are connected so as to be able to communicate with each other, the database management device is configured so that the storage device When accessing data stored in a database area, the storage area of the data is searched using an index stored in the index storage area of the storage device, and the data is accessed based on the search result A database management device, a statistical information acquisition unit that acquires statistical information about a path of data stored in the database storage area, and an inquiry time for the path is acquired from the statistical information, and the acquired inquiry time Based on the above, reduction when using the index A query time reduction amount calculation unit that calculates a query time reduction amount that can be stored, and a storage that calculates an index for the path and calculates a storage area consumption amount that is consumed when the created index is stored in the index storage area An area consumption calculation unit; a priority calculation unit that calculates a priority for a plurality of paths of the data to be indexed based on the inquiry time reduction amount and the storage area consumption; and And an index deletion creation unit for creating an index of the path.

According to the present invention, it is possible to provide a database management apparatus, a database management method, and a database system that can exhibit high performance while effectively using a limited storage area.

It is a schematic block diagram of a database system. It is a logical block diagram of a database system. It is a conceptual diagram which shows statistical information. It is a conceptual diagram which shows system information. It is a conceptual diagram which shows index definition information. It is a conceptual diagram which shows a priority table. It is a conceptual diagram which shows a creation index table. It is a conceptual diagram which shows an example of an index reorganization setting screen. It is a conceptual diagram which shows an example of an index reorganization result screen. It is a flowchart which shows the process sequence of a priority calculation process. It is a flowchart which shows the process sequence of a magnification calculation process. It is a flowchart which shows the process sequence of an index deletion creation process.

Hereinafter, an embodiment of the present invention will be described in detail with reference to the drawings.

(1) Configuration of XML Database System According to this Embodiment In FIG. 1, 1 indicates an XML database system according to this embodiment as a whole. The XML database system 1 is configured such that a terminal device 10 and a database management device 20 are connected via a network N, and a secondary storage device 30 is connected to the database management device 20.

The terminal device 10 is an information processing device used by a user for performing business, and includes information processing resources such as a processor 11, a memory 12, and a communication interface 13. The memory 12 stores a business program 12A corresponding to the business content of the user who uses the terminal device 10, and a predetermined business process is executed when the processor 11 executes the business program 12A. Then, at the time of executing this business process, a query statement for acquiring necessary information from the XML database managed by the database management apparatus 20 is issued to the database management apparatus 20 via the communication interface 13. The communication interface 13 is composed of, for example, a NIC (Network Interface Card) or the like, and performs protocol control during communication with the database management apparatus 20.

The database management device 20 is a computer device provided with information processing resources such as a communication interface 21, a processor 22, a memory 23, and a disk interface 24, and is composed of, for example, a personal computer, a workstation, or a large computer.

The memory 23 is a primary storage device mainly used for holding various control programs. A query execution program 23A, a statistical information acquisition program 23B, a priority calculation program 23C, and an index deletion creation program 23D, which will be described later, are also stored and held in the memory 23. When the processor 22 executes the above-described various programs stored in the memory 23, the database management apparatus 20 as a whole performs various processes as described below. The disk interface 24 is composed of an adapter that performs protocol control during communication with the secondary storage device 30. The communication interface 21 has the same function as the communication interface 13 of the terminal device 10.

The secondary storage device 30 includes, for example, a single hard disk device or optical disk device, or a disk array device in which a plurality of hard disk devices or optical disk devices are mounted in an array. The secondary storage device 30 stores a database area 31, statistical information 32, system information 33, an index storage area 34, index definition information 35, and a priority table 36. The database area 31, statistical information 32, system information 33, index storage area 34, index definition information 35, and priority table 36 will be described later.

FIG. 2 shows a logical configuration related to database management of the database system 1.

In FIG. 2, the query execution unit 230A, the statistical information acquisition unit 230B, the priority calculation unit 230C, and the index deletion creation unit 230D are respectively executed by the processor 22 of the database management device 20 with the query execution program 23A stored in the memory 23, the statistics It represents a function that is exhibited by executing the information acquisition program 23B, the priority calculation program 23C, and the index deletion creation program 23D. Of these query execution unit 230A, statistical information acquisition unit 230B, priority calculation unit 230C, and index deletion creation unit 230D, an index reorganization process (described later with reference to FIG. 10) is performed by the priority calculation unit 230C and the index deletion creation unit 230D. An index reorganization unit 232 that executes (see FIG. 12) is configured.

The inquiry execution unit 230A searches the requested information from the data stored in the database area 31 provided in the secondary storage device 30 in accordance with the inquiry sentence given from the business program 12A of the terminal device 10, and the search result is the business program. 12A is notified. Further, the query execution unit 230A transfers the query sentence given from the business program 12A to the statistical information acquisition unit 230B.

The statistical information acquisition unit 230B acquires predetermined information about the inquiry sentence every time the inquiry sentence is transferred from the inquiry execution unit 230A, and accumulates the information in the secondary storage device 30 as the statistical information 32.

The priority calculation unit 230C executes a priority calculation process (see FIG. 10) as described later at regular intervals set by the user, and a priority column in a priority table as described later (see FIG. 6). The priority of each path stored in is calculated.

The “priority” is a value calculated based on the query time reduction amount, storage area consumption, path usage frequency, and magnification stored in the priority table (see FIG. 6). In this form, indexes are created in descending order of priority.

The index deletion creation unit 230D executes an index deletion creation process (see FIG. 12) described later at regular intervals set by the user, creates indexes in descending order of priority, and deletes unnecessary indexes. To do.

(2) Various Information According to this Embodiment In FIGS. 1 and 2, a database area 31 is a storage area for storing data related to an inquiry from the terminal device 10. The index storage area 34 is one or a plurality of storage areas for storing indexes created by the database management apparatus 20.

FIG. 3 shows statistical information 32 acquired each time an inquiry is given from the business program 12A of the terminal device 10 to the database management device 20. For example, as shown in FIG. 3, the statistical information 32 includes a time column 32A, an inquiry statement column 32B, a used path name column 32C, an inquiry time column 32D, and a CPU time column 32E.

In the time column 32A, the time when the inquiry from the business program 12A is given is stored, and in the inquiry statement column 32B, the inquiry statement at the time of the inquiry is stored. The used path name column 32C stores the path name of the path in the XML document used when searching for the query, and the query time column 32D stores the time taken to access the data to be queried. The CPU time column 32E stores the processing time of the processor 22.

FIG. 4 shows system information 33 for managing the index storage area 34 provided in the database system 1. For example, as shown in FIG. 4, the system information 33 includes an index storage area name column 33A, a capacity column 33B, and a maximum magnification column 33C. The index storage area name column 33A stores the name of the index storage area 34 (index storage area name), the capacity column 33B stores the capacity of the index storage area 34, and the maximum magnification column 33C stores A maximum magnification preset by the user is stored in the index storage area 34. Details of the maximum magnification will be described later.

FIG. 5 shows index definition information 35 for managing an index existing in the database system 1. The index definition information 35 includes an index name field 35A and a path name field 35B as shown in FIG. 5, for example. Each index name (index name) is stored in the index name column 35A, and the path name of the path indexed by the index is stored in the path name column 35B.

FIG. 6 shows a priority table 36 used for calculating and managing the priority of each path registered in the statistical information 32. For example, as shown in FIG. 6, the priority table 36 includes a path name column 36A, an inquiry time reduction amount column 36B, a storage area consumption amount column 36C, a frequency column 36D, a magnification column 36E, and a priority column 36F.

In the path name column 36A, the path name of each path registered in the statistical information 32 is stored. The query time reduction amount column 36B stores the query time reduction amount when the index is created relative to the query time when the corresponding path index is not created, and the storage area consumption amount column 36C corresponds. The consumption amount of the index storage area 34 when the path index is created is stored.

Further, the frequency column 36D stores the usage frequency of the corresponding path per unit time acquired from the statistical information 32, and the magnification column 36E stores the magnification calculated for the corresponding path. Further, the priority column 36F stores the priority calculated for the corresponding path.

Here, the magnification is a value that defines how much the use frequency of a path is added to the priority when the priority is calculated, that is, a value indicating a ratio of the use frequency reflected in the priority. The magnification is calculated, for example, as in the following formula (1).
(Equation 1)
f (frequency) = [{(maximum magnification) -1)} / {max (frequency of all paths) -min (frequency of all paths)} × (frequency)] + [1-{(maximum magnification) -1) } / {Max (frequency of all paths) −min (frequency of all paths)} × min (frequency of all paths)] …… (1)

Note that the value of the maximum magnification stored in the maximum magnification column 33C of the system information 33 is substituted for “maximum magnification” in the above formula (1), and “max (frequency of all paths)” and “min (all paths) The maximum value and the minimum value among the use frequencies of all paths stored in the frequency column 36D of the priority table 36 are respectively substituted for “ Further, when the usage frequencies of all paths are the same, that is, when the minimum value is subtracted from the maximum value of the usage frequencies of all paths, the magnification is set to “1”.

The priority is calculated using, for example, the following formula (2) using the magnification calculated by the formula (1).
(Equation 2)
Priority = f (frequency) × {(query time reduction amount) / (storage area consumption amount)} (2)

The “query time reduction amount” in the above equation (2) includes the query time reduction amount of the path whose priority is to be calculated among the query time reduction amounts stored in the query time reduction amount column 36B of the priority table 36. Is substituted. In addition, the storage area consumption of the path for which the priority is calculated among the storage area consumption stored in the storage area consumption column 36C of the priority table 36 is substituted into “storage area consumption”. The priority calculated by the above equation (2) is stored in the priority column 36F of the priority table 36.

In this way, the ratio of path usage frequency to be reflected in the priority, that is, the response time request such as batch processing and OLTP (On-Line Transaction Processing) by allowing the database administrator to arbitrarily set the magnification. It is possible to correspond to different database systems.

FIG. 7 shows a creation index table 37 for temporarily managing indexes that can be created in the index storage area 34 based on the priority and the storage capacity of the index storage area 34 by the index deletion creation unit 230D. The creation index table 37 shows a list of indexes that can be created, and is temporarily stored in the memory 23, for example. The creation index table 37 includes a creation target path name column 37A, and the creation target path name column 37A stores a path name of a path for which an index is to be created.

(3) Configuration of Various Screens According to this Embodiment FIG. 8 shows a configuration example of the index reorganization setting screen 30 displayed on a display screen (not shown) of the terminal device 10 or the database management device 20. Using this index reorganization setting screen 30, the database administrator can set items necessary for reorganizing the index managed by the database management apparatus 20.

Actually, the index reorganization setting screen 30 includes an index reorganization function setting field 31, a maximum magnification setting field 32, an index storage area capacity setting field 33, and a save button 34 as shown in FIG.

The index reorganization function setting field 31 is a field for the database administrator to specify whether to use the index reorganization function, and the maximum magnification setting field 32 is an index to be reorganized. This is a field for the database administrator to specify the maximum magnification used for the calculation of the priority.

The index storage area capacity setting field 33 is a field for the database administrator to specify the storage capacity of the index to be reorganized, and the save button 34 is the index reorganization function setting field 31, the maximum magnification setting field. 32 and a button for saving the setting designated by the database manager in the index storage area capacity setting field 33. When the index reorganization function setting field 31, the maximum magnification setting field 32, and the index storage area capacity setting field 33 are set to perform index reorganization, when the save button 34 is pressed, FIG. The index reorganization process of FIG. 12 is executed.

FIG. 9 shows a configuration example of the index reorganization result screen 40 displayed on a display screen (not shown) of the terminal device 10 or the database management device 20. The database administrator can grasp the execution of the index reorganization process and the execution result by browsing the index reorganization setting screen 40.

In practice, the index reorganization result screen 40 includes an index reorganization process execution notification field 41, a path name field 42, a path status field 43, a reduction time field 44, and a total reduction time field 45, as shown in FIG. Is done.

The index reorganization process execution notification field 41 displays the time when the index reorganization process was executed and a notification that the index reorganization process was executed, and the path name field 42 shows the path name of the path reorganized by the index reorganization process. Is displayed.

In the path status field 43, the index reorganization process indicates whether the path has been newly created (new), the index has been deleted (deletion), or before or after the index reorganization process is executed. Also, the path status indicating whether the index exists (existing) is displayed.

In the reduction time field 44, the inquiry time reduced by the index reorganization process is displayed for each path. If the displayed reduction time is positive (for example, “100”), it indicates that the query time for the path has been reduced by the index reorganization process, and the displayed reduction time is negative (for example, “−” 10 ") indicates that the inquiry time for the path has increased due to the index reorganization processing.

In the total reduction time field 45, the total inquiry time reduced by the index reorganization process is displayed. The reduction time displayed in the total reduction time field 45 is a value obtained by adding all the reduction times displayed in the reduction time field 44.

(4) Various Processes According to this Embodiment FIG. 10 shows a processing procedure of the priority calculation process executed by the processor 22 of the database management apparatus 20 in cooperation with various programs stored in the memory 23. When various items necessary for index reorganization are set on the index reorganization setting screen 30 and the save button 34 is finally pressed, the processor 22 first displays the priorities shown in FIG. 10 in order to reorganize the index. Execute the calculation process.

First, the processor 22 acquires the statistical information 32 stored in the secondary storage device 30, refers to the acquired statistical information 32, and selects any path registered in the statistical information 32 (S1). ).

Next, the processor 22 acquires the index definition information 35 stored in the secondary storage device 30, refers to the acquired index definition information 35 and the path selected in step S1, and indexes the path selected in step S1. It is determined whether or not there exists (S2).

If the processor 22 obtains an affirmative result in this determination, it determines that the current index exists in the selected path, and uses or does not use the index for the query time for the data specified by this path. Compare with. Specifically, the processor 22 subtracts the query time stored in the query time column 32D of the statistical information 32 from the query time when the index is not used (S3).

On the other hand, if the processor 22 obtains a negative result in the determination at step S2, it determines that there is no index currently in the selected path, and the data to be specified by this path when the index is used. The inquiry time for the data specified by this path when the index is not used, specifically, the inquiry time stored in the inquiry time column 32D of the statistical information 32 is subtracted from the inquiry time (S4).

Then, the processor 22 calculates the index storage area length required when an index is created for this path (S5). Since the index storage area length is calculated by a conventional method, a description thereof is omitted here.

In calculating the priority of the path selected in step S1, the processor 22 performs a magnification calculation process to determine how much the use frequency of the path used for the calculation of the priority is reflected in the calculation of the priority. Execute (S6).

The magnification calculation process will be described later with reference to FIG. 11. In this magnification calculation process, the above formula (1) is used, and the magnification of the path usage frequency to be reflected in the priority is calculated by this magnification calculation process.

Then, the processor 22 calculates the priority using the magnification calculated in step S6, and registers the calculated priority in the priority column 36F of the priority table 36 stored in the secondary storage device 30 (S7). . The specific calculation method of the priority calculated here is as shown in the above equation (2). That is, the processor 22 refers to the priority table 36, divides the inquiry time reduction amount by the storage area consumption amount, and calculates the priority by multiplying the divided value by a magnification.

Next, the processor 22 determines whether or not all paths registered in the statistical information 32 have been selected (S8).

If the processor 22 obtains a negative result in this determination, it returns to step S1, selects another path registered in the statistical information 32, and executes the processes of steps S1 to S8 described above. The processor 22 repeats the loop of steps S1 to S8 described above until all the paths registered in the statistical information 32 are selected.

On the other hand, if the processor 22 obtains a positive result in the determination at step S8, it ends this priority calculation process.

FIG. 11 shows a processing procedure of a magnification calculation process executed by the processor 22 of the database management apparatus 20 in cooperation with various programs stored in the memory 23. When the processor 22 shifts to the magnification calculation process (S6) in the priority calculation process shown in FIG. 10, the processor 22 executes the magnification calculation process shown in FIG.

First, the processor 22 acquires the system information 33 stored in the secondary storage device 30, and refers to the acquired system information 33 to acquire the maximum magnification of the path that is the target of the magnification calculation processing (S61). ).

Then, the processor 22 creates a function f (frequency) for calculating the magnification with the use frequency of the path as an input value (S62).

The function f (frequency) created here is, for example, the above formula (1), but is not limited to this, and a function that represents an approximate curve that passes through a plurality of (frequency, magnification) specified in advance by the database administrator. It may be created.

The processor 22 acquires the statistical information 32 stored in the secondary storage device 30, and refers to the acquired statistical information 32 to acquire the use frequency of the path that is the target of the magnification calculation process (S63). ).

Then, the processor 22 inputs the use frequency of the path acquired in step S63 to the function created in step S62 (for example, the above equation (1)) as an input value, and calculates the magnification for the path (S64).

The processor 22 determines whether or not the usage frequencies of all the paths registered in the statistical information 32 have been acquired (S65).

If the processor 22 obtains a negative result in this determination, it returns to step S63, acquires the use frequency for the path that has been registered in the statistical information 32 and has not yet acquired the use frequency, and calculates the magnification of the path. Note that the processor 22 repeats the above steps S63 to S65 until the use frequency of all the paths registered in the statistical information 32 is obtained.

On the other hand, if the processor 22 obtains a positive result in the determination at step S65, it ends this magnification calculation process.

FIG. 12 shows a processing procedure of index deletion creation processing executed by the processor 22 of the database management apparatus 20 in cooperation with various programs stored in the memory 23. When various items necessary for index reorganization are set on the index reorganization setting screen 30 and the save button 34 is finally pressed, the processor 22 performs the process shown in FIGS. 10 and 11 described above to reorganize the index. After executing the processing shown, the index deletion creation processing shown in FIG. 12 is executed.

First, the processor 22 acquires the priority table 36 stored in the secondary storage device 30, and selects a path registered in the priority table 36 in descending order of priority (S11).

Next, the processor 22 determines whether or not an index including the selected path with the highest priority can be created in the index storage area 34 (S12). The processor 22 determines that an index can be created in the index storage area 34 when there is sufficient storage area in the secondary storage device 30, and determines that creation is impossible when the storage area is insufficient. To do.

When the processor 22 obtains a positive result in this determination, it registers this high priority path in the creation index table 37 (S13).

On the other hand, if the processor 22 obtains a negative result in the determination at step S12, the process proceeds to step S14.

The processor 22 determines whether or not all paths registered in the priority table 36 have been selected (S14).

If the processor 22 obtains a negative result in this determination, it returns to step S11 and selects another path with a higher priority. Then, the processor 22 repeats the processes of steps S11 to S14 until all the paths registered in the priority table 36 are selected.

On the other hand, when the processor 22 obtains a positive result in the determination at step S <b> 14, it acquires the index definition information 35 stored in the secondary storage device 30 and is registered in the acquired index definition information 35. Is selected (S15).

Then, the processor 22 determines whether or not the selected path is included in the creation index table 37 (S16).

If the processor 22 obtains a negative result in this determination, it determines that the priority of the index previously defined and managed by the index definition information 35 is low, and this index does not need to be defined as it is. The index is deleted from the index definition information 35 (S17).

On the other hand, when the processor 22 obtains a positive result in the determination in step S16, the priority of the index that has been defined and managed by the index is high, and it is determined that the index needs to be defined as it is. The process proceeds to step S18 while leaving this index without being deleted from the index definition information 35.

The processor 22 determines whether or not all paths registered in the index definition information 35 have been selected (S18).

If the processor 22 obtains a negative result in this determination, it returns to step S15 and selects another path registered in the index definition information 35. Then, the processor 22 repeats the processes of steps S15 to S18 until all the paths registered in the index definition information 35 are selected.

On the other hand, when the processor 22 obtains a positive result in the determination at step S18, it selects any path registered in the created index table 37 (S19).

Then, the processor 22 determines whether or not the selected path is included in the creation index table 37 (S20).

If the processor 22 obtains a negative result in this determination, it determines that the index to be defined and managed by the index definition information 35 is not defined in the current index definition information 35, and the index of the path selected in step S19. Is created (S21). When the processor 22 newly creates an index, the created index is defined by the index definition information 35 and managed.

On the other hand, when the processor 22 obtains a positive result in the determination at step S20, it determines that the index to be defined and managed by the index definition information 35 is already defined and managed by the current index definition information 35. Then, the process proceeds to step S22.

The processor 22 determines whether all paths registered in the created index table 37 have been selected (S22).

If the processor 22 obtains a negative result in this determination, it returns to step S19 and selects another path registered in the created index table 37. The processor 22 repeats the processing from step S19 to S22 until all the paths registered in the created index table 37 are selected.

On the other hand, when the processor 22 obtains a positive result in the determination at step S22, it determines that indexes have been created for all paths registered in the created index table 37, and performs this index deletion creation process. finish.

(5) Other Embodiments In this embodiment, the processor 22 executes the above-described various processes shown in FIGS. 10 to 12. However, a control unit that integrates the functions of the processor 22 on one chip performs various processes. May be executed. In this case, the control unit acquires a function as the statistical information acquisition unit 230B that acquires the statistical information 32 regarding the path of the data stored in the database storage area 31, the query time for the path from the statistical information 32, A function as a query time reduction amount calculation unit that calculates a query time reduction amount that can be reduced when an index is used based on the query time created, an index for the path is created, and the created index is stored in the index storage area 34 Priority for multiple data paths to be indexed based on the function of the storage area consumption calculation unit that calculates the consumption of storage area consumed when stored, query time reduction, and storage area consumption Based on the function as the priority calculation unit 230C for calculating the degree of priority and the priority, A function as an index deletion creating section 230D to create the index.

In the present embodiment, the creation of the index is periodically executed at an appropriate timing after the save button 34 on the index reorganization setting screen 30 is pressed. It may be executed substantially simultaneously with being pressed. In this case, the database administrator can explicitly instruct the creation timing of the index.

Further, the creation of the index may be executed with the timing when the storage capacity of the index storage area 34 is changed. In this case, if the storage capacity of the index storage area 34 is increased, an index with a low priority can be newly created and stored, and the query time can be reduced extensively. On the other hand, if the storage capacity of the index storage area 34 is reduced, an index having a low priority can be deleted to effectively use the storage area.

Also, the creation of the index may be executed when the date or time specified in advance by the database administrator has passed. In this case, it is not necessary to press the save button 34 on the index reorganization setting screen 30 each time an index is to be created, and the convenience of the database administrator can be improved. In addition, it is possible to prevent the index from being reorganized over a long period of time.

Further, the creation of the index may be executed when any of the inquiry times stored in the inquiry time column 32D of the statistical information 32 exceeds a predetermined threshold. In this case, it is possible to prevent a situation in which the inquiry time is extremely slow from continuing.

The storage capacity of the index storage area is specified by the database administrator in the index storage area capacity setting field 33 of the index reorganization setting screen 30. However, even if the ratio of the storage capacity is specified, not the storage capacity. Good. In this case, a storage area for a newly created index can be secured.

As for the storage capacity of the index storage area, a value obtained by subtracting a free area specified in advance by the database administrator may be used as the actual index storage area on the assumption that a new index will be created later. Also in this case, a storage area for a newly created index can be secured.

The present invention can be widely applied to database management apparatuses having various configurations equipped with a management function for managing an XML database.

DESCRIPTION OF SYMBOLS 1 ... Database system, 10 ... Terminal device, 20 ... Database management apparatus, 21 ... Communication interface, 22 ... Processor, 23 ... Memory, 23A ... Inquiry execution program, 230A ... Inquiry execution part, 23B ...... Statistical information acquisition program, 230B ... Statistical information acquisition unit, 23C ... Priority calculation program, 230C ... Priority calculation unit, 23D ... Index deletion creation program, 230D ... Index deletion creation unit, 231 ... Database management system 231 ... Database management system, 232 ... Index reorganization unit, 30 ... Secondary storage device, 31 ... Database area, 32 ... Statistical information, 33 ... System information, 34 ... Index storage area 35 …… Index definition information, 36 …… Priority table.

Claims

When accessing the data stored in the database area according to the query statement from the business program, the storage area of the data is searched using the index stored in the index storage area, and based on the search result In the database management device for accessing the data,
A statistical information acquisition unit that acquires statistical information about a path of data stored in the database storage area;
A query time reduction amount calculating unit that acquires a query time for the path from the statistical information, and calculates a query time reduction amount that can be reduced when the index is used based on the acquired query time;
A storage area consumption calculation unit that creates an index for the path and calculates a storage area consumption consumed when the created index is stored in the index storage area;
A priority calculation unit for calculating a priority for a plurality of paths of the data to be indexed based on the query time reduction amount and the storage area consumption amount;
An database deletion creation unit comprising: an index deletion creation unit that creates an index of the path based on the priority.
The priority calculation unit
The query time reduction amount that can be reduced when the index is used is divided by the storage area consumption amount that is consumed when the index is created to calculate a reduction time per unit consumption amount. The database management apparatus according to claim 1, wherein the priority is calculated by multiplying a reduction time per unit consumption by a predetermined magnification.
The magnification is
The database management apparatus according to claim 2, wherein the database management apparatus is a value indicating a ratio of a usage frequency of the index to be reflected with respect to the priority.
The index deletion creation unit
The path is selected in descending order of priority, the selected path is temporarily stored as an index creation target path, and an index of a path different from the temporarily stored path is the index storage area. If the stored index is deleted from the index storage area, and the index of the temporarily stored path is newly created, the created index is stored in the index storage area. The database management apparatus according to claim 1, wherein the database management apparatus is stored in an index storage area.
The index deletion creation unit
When the path is selected in descending order of priority and an index of the selected path is created, it is determined whether or not the created index can be stored in the index storage area. The selected path is temporarily stored as an index creation target path, and if it is determined that the path is not possible, a path with the next highest priority is selected after the selected path. The database management device described.
The index deletion creation unit
It is determined whether or not the temporarily stored path index is already stored in the index storage area, and the temporary stored path index is updated only if it is not already stored. The database management device according to claim 4, wherein the database management device stores the created index in the index storage area.
When accessing the data stored in the database area according to the query statement from the business program, the storage area of the data is searched using the index stored in the index storage area, and based on the search result In the database management method of the database management apparatus for accessing the data,
A first step in which a statistical information acquisition unit acquires statistical information on a path of data stored in the database storage area;
A query time reduction amount calculation unit acquires a query time for the path from the statistical information, and calculates a query time reduction amount that can be reduced when the index is used based on the acquired query time. Steps,
A third step in which a storage area consumption calculating unit creates an index for the path and calculates a storage area consumption consumed when the created index is stored in the index storage area;
A fourth step in which a priority calculation unit calculates priorities for a plurality of paths of the data to be indexed based on the query time reduction amount and the storage area consumption amount;
And a fifth step of creating an index of the path based on the priority.
In the fourth step, the priority calculation unit includes:
The query time reduction amount that can be reduced when the index is used is divided by the storage area consumption amount that is consumed when the index is created to calculate a reduction time per unit consumption amount. The database management method according to claim 7, wherein the priority is calculated by multiplying a reduction time per unit consumption by a predetermined magnification.
The magnification is
The database management method according to claim 8, wherein the database management method is a value indicating a ratio of a usage frequency of the index to be reflected with respect to the priority.
In the fifth step, the index deletion creating unit
The path is selected in descending order of priority, the selected path is temporarily stored as an index creation target path, and an index of a path different from the temporarily stored path is the index storage area. If the stored index is deleted from the index storage area, and the index of the temporarily stored path is newly created, the created index is stored in the index storage area. The database management method according to claim 7, wherein the database is stored in an index storage area.
In the fifth step, the index deletion creating unit
When the path is selected in descending order of priority and an index of the selected path is created, it is determined whether or not the created index can be stored in the index storage area. The selected path is temporarily stored as an index creation target path, and if it is determined that the path is not possible, a path with the next highest priority is selected after the selected path. The database management method described.
In the fifth step, the index deletion creating unit
It is determined whether or not the temporarily stored path index is already stored in the index storage area, and the temporary stored path index is updated only if it is not already stored. The database management method according to claim 10, wherein the created index is stored in the index storage area.
In a database system in which a terminal device, a database management device, and a storage device are connected so as to communicate with each other,
The database management device includes:
When accessing data stored in the database area of the storage device in response to a query statement from the business program of the terminal device, storing the data using an index stored in the index storage area of the storage device A database management device that searches an area and accesses the data based on the search result, a statistical information acquisition unit that acquires statistical information about a path of data stored in the database storage area, and the path A query time reduction amount calculating unit that acquires a query time reduction amount that can be reduced when the index is used based on the acquired query time, and an index for the path And store the created index in the index storage area A storage area consumption calculation unit for calculating a storage area consumption consumed in the event of a failure, and a priority for a plurality of the data paths to be indexed based on the query time reduction amount and the storage area consumption A database system comprising: a priority calculation unit that calculates a degree; and an index deletion creation unit that creates an index of the path based on the priority.