JPH06215037A - Automatic updating device for index - Google Patents

Abstract

PURPOSE:To provide an automatic updating device for an index which can automatically generate and delete an index. CONSTITUTION:An index storage means 10 stores a pointer to the index for each attribute. An access frequency information storage area 20 stores access frequency information for each attribute and access request. At the time of access to the specified attribute, an access means 30 increases the access frequency information of the specified attribute in the access frequency information storage area 20 for +1 and starts an index evaluating means 50. When the value of the access frequency information of the specified attribute is larger than a previously set threshold value and the pointer to the index is not stored at the attribute value of the specified attribute in the index storage means 10, the index evaluating means 50 instructs the generation of the index to an index updating means 60. According to the instruction, the index concerning the specified attribute is generated, and the pointer to the index is written as the attribute value of the specified attribute in the index storage means 10.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to an automatic index updating device in a database system for searching a database via an index.

[0002]

2. Description of the Related Art In relational databases (RDB) and object-oriented databases (OODB), records and instance objects having certain attribute values are often searched. In order to perform this search at high speed, there is a method of providing an index (index) from the attribute value to the record or instance object (group) (for example, "AN INTRODU
CTION TO Database Systems '' (CJDATE, ADDISON-WESL
EY PUBLISHING COMPANY, Fourth Edition, 1986)).

In this case, however, the database user had to explicitly specify that an index should be added to each attribute. Because there are speeding up access by assigning an index and degrading the efficiency of the database space or memory space due to the index itself, let the database user decide whether to add the index or not. I am doing it. However, the current situation is that database users avoid indexing indiscriminately, and only index indexes that are frequently accessed or that require fast access.

On the other hand, a method for semi-automating the indexing has also been realized. As this method,
The one disclosed in JP-A-2-250175 is known. In the one disclosed in this publication, an index pattern is generated based on a table for which an index is defined and an access pattern indicating a process for this table, and then a design is performed from the generated index patterns. Select the one that meets the index narrowing conditions entered by the operator,
The execution time of each access pattern is evaluated for all the selected index patterns, the evaluation result is output, and the index pattern selected by the designer from the output evaluation results is stored in the database. It is defined as follows.

[0005]

However, the above-mentioned Japanese Unexamined Patent Application Publication No.
In the one disclosed in Japanese Patent Laid-Open No. 250175, the designer has to perform operations such as inputting an access pattern, inputting conditions for narrowing down an index, and selecting a desired index pattern from evaluation results. However, it was difficult to easily add the index.

It is an object of the present invention to provide an index automatic updating device capable of automatically generating and deleting an index.

[0007]

According to the present invention, in a database system for searching a database through an index, frequency information storage means for storing access frequency information of each attribute for data to be searched, and for each attribute. Access means for updating the access frequency information of the accessed attribute in the frequency information storage means for each access, and whether the contents of the index should be updated based on the access frequency information of each attribute in the frequency information storage means The evaluation unit includes an evaluation unit that evaluates whether or not there is an item, and an updating unit that updates the content of the index based on the evaluation result of the evaluation unit.

[0008]

According to the present invention, the access means updates the access frequency information about the accessed attribute in the frequency information storage means every time each access to each attribute of the data to be searched, and the evaluation means stores the frequency information storage. Based on the access frequency information of each attribute in the means, it is evaluated whether or not the content of the index should be updated (index addition or deletion), and the updating means updates the content of the index based on the evaluation result of the evaluation means. Therefore, the addition or deletion of the index corresponding to each attribute value depends on the access frequency information, and the index generation and deletion can be easily performed.

[0009]

Embodiments of the present invention will be described below with reference to the accompanying drawings.

FIG. 1 is a functional block diagram showing an embodiment of an automatic index updating apparatus according to the present invention. In the figure, the index storage area 10 stores index presence / absence information as information indicating whether or not there is an index (not shown) for each attribute.
That is, the index itself is not stored in this area, but the index presence / absence information is stored. In this embodiment, as the index presence / absence information, when an index exists, a pointer to the index is stored, and when the index does not exist, it is blank or NULL.
It has become. The initial value is blank or NULL.

The access frequency information storage area 20 stores access frequency information for each attribute. The initial values are all set to 0, and are incremented by +1 for each access.

The access means 30 which is activated when an access request to the database is made refers to or updates the attribute value of the attribute group 40 and also refers to the index storage area 10 to obtain the index for the accessed attribute. If so, use that index. The access means 30 is the access information storage means 31.
The access information storage unit 31 updates the access frequency information of the corresponding attribute value every time the attribute is accessed. Further, the access means 10 activates the index evaluation means 50.

The index evaluation means 50 evaluates the access information frequency information of each attribute in the access information storage area 20, and the evaluation result and the index storage area 1
The index updating means 60 is instructed to generate or delete the index based on the stored information of 0.

In accordance with the instruction, the index updating means 60 updates the contents of the index storage area 10 and the contents of the index (not shown).

With this arrangement, the automatic index updating process of this embodiment will be described with reference to FIG.
FIG. 2 shows a flowchart showing the processing operation.

Here, it is assumed that the contents of the index storage area 10 and the access frequency information storage area 20 are initialized, and the threshold value for determining whether to create or delete the index is "1".

The access means 30 judges whether or not there is an access request (step 101). If there is no request, the access means 30 waits until there is a request. On the other hand, if there is a request, it judges whether or not there is an inverse access. Judge (Step 10
2).

The inverse access is to take out a record (group) having a certain attribute value and refer to or rewrite the record (group). This means access to the record from the attribute value. On the other hand, referring to or rewriting the value of a certain attribute of a record is called normal access. This means accessing the attribute value from the record.

In step 102, in the case of inverse access, the index storage area 10 is referred to,
It is determined whether there is an index (step 10
3) If it exists, the attribute value is accessed (referenced or updated) using the index (step 10).
4) If it does not exist, access (reference or update) to the attribute value that does not use the index is performed (step 10).
5).

For example, assume that a reference request for the value of attribute 1 is made. At this time, as shown in FIG. 1, since the index presence / absence information of the attribute 1 in the index storage area 10 is not a pointer to the index, the access unit 30 directly refers to the value 1 of the attribute 1. This result is returned to the access request source.

Upon completion of step 104 or step 105, in the access means 30, the access information storage means 31 updates the access frequency information of the accessed attribute in the access frequency information storage 20 (step 1
06). In the above example, the value of the access frequency information for the attribute 1 is incremented by +1 and updated to "+1". After completing step 106, access means 3
0 activates the index evaluation means 50.

The activated index evaluation means 50 evaluates the access frequency information of the accessed attribute (step 107), and judges whether or not the index should exist based on the evaluation result (step 10).
8). When evaluating the access frequency information, a preset calculation such as the absolute value of the access frequency information of the accessed attribute or the relative value of the access frequency information of the accessed attribute and the access frequency information of other attributes is performed. It is determined whether the reference value is greater than or equal to a preset threshold value,
If it is equal to or larger than the threshold, it is evaluated that the index is necessary, while if it is smaller than the threshold, it is evaluated that the index is unnecessary.

When the index evaluation means 50 evaluates that an index is necessary (should exist) in step 108, it refers to the index storage area 10 and refers to the index presence / absence information of the accessed attribute. It is determined whether or not there is a pointer to the index (step 109). Here, if it exists, the process returns to step 101, while if it does not exist, the index updating means 60 indicates that the index is necessary.
Instruct. The index updating means 60 generates an index for the accessed attribute (step 110) and writes a pointer to the index as index presence / absence information of the accessed attribute in the index storage area 10.

In the above example, the access frequency information of the attribute 1 has the value "+1", and since this value is the preset threshold value 1 or more, it is evaluated that the index is necessary. However, at this point, since the pointer to the index (not shown) as the index presence / absence information is not written in the attribute 1 in the index storage area 10, the index is generated and the attribute value of the attribute 1 is also generated. As a result, the pointer to the generated index will be written.

Regarding the attribute 1, for example, n values of the value 1, the value 2, ..., The value n as the value of the attribute 1 and the information (for example, pointer) indicating the record (group) referred to by each value An index associated with and is generated. When the index is generated in this way, the index is referred to when the attribute 1 is accessed next time.

FIG. 3 shows the internal state of the automatic index updating device in the processing up to this point. In FIG. 3, the value of the access frequency information of the attribute 1 of the access frequency information storage area 20 is updated from “0” to “1”, the index for the attribute 1 is generated, and the attribute 1 of the index storage area 10 is further updated. It is shown that the pointer (P1) to the generated index has been generated as the index presence / absence information.

The processing of steps 109 and 110 is performed by using the absolute value of the access frequency information of the accessed attribute,
Alternatively, it may occur regardless of which of the relative values of the access frequency information of the accessed attribute and the access frequency information of another attribute is used.

When the index evaluating means 50 evaluates that the index is unnecessary (should not exist) in step 108, it refers to the index storage area 10 and refers to the index presence / absence information of the accessed attribute. It is determined whether or not there is a pointer to the index (step 111), and if it does not exist, the process returns to step 101. On the other hand, if it exists, the accessed attribute and the index indicating that the index is unnecessary are indexed. Notify the updating means 60. The index updating means 60 deletes the index for the notified attribute (step 112) and deletes the pointer to the index from the index storage area 10.

When the index is unnecessary in step 108, the accessed attribute is used when the relative value between the access frequency information of the accessed attribute and the access frequency information of another attribute is adopted. This is when the relative value of the access frequency information of is smaller than the threshold value. Therefore, when the absolute value is adopted, the processes of steps 111 and 112 are not performed.

In the above embodiment, the access frequency information is evaluated by comparing the absolute value of the access frequency information or the relative value with the access frequency information of other attributes with the threshold value. However, without being limited to these, the following may be performed.

(1) A column of access times is held as access frequency information, and the number of access times after the designated time or the number of access times before the designated time and a preset threshold value are set. And the access frequency information is evaluated.

(2) As in (1) above, a column of access times is held as access frequency information, and the number of access times within a specified past fixed period (for example, a specified time period or a monthly day), The access frequency information is evaluated by comparing it with a preset threshold value.

(3) The access frequency information is held for each access type (read or update), and the access frequency information corresponding to the type is evaluated by the methods (1) and (2).

As described above, according to the present embodiment, the database user can evaluate the access frequency information required when deciding whether to generate or delete the index (for example, to obtain the absolute value of the access frequency information. It is possible to automatically perform index generation and deletion processing only by specifying (for example, adoption).

[0035]

As described above, for each access to each attribute of the data to be searched, the access frequency information of the corresponding attribute is updated, and whether the contents of the index should be updated based on this access frequency information. Since the contents of the index are updated based on the evaluation result, the addition or deletion of the index corresponding to each attribute value depends on the access frequency information. Deletion can be easily implemented.

[Brief description of drawings]

FIG. 1 is a functional block diagram showing an embodiment of an automatic index updating device according to the present invention.

FIG. 2 is a flowchart showing an automatic index update processing operation according to the present embodiment.

FIG. 3 is a diagram for explaining a process of automatic index update processing according to the present embodiment.

[Explanation of symbols]

10 ... Index storage area, 20 ... Access frequency information storage area 20, 30 ... Access means, 31 ... Access information storage means, 40 ... Attribute group, 50 ... Index evaluation means, 60 ... Index update means.

Claims (1)

[Claims] 1. In a database system for searching a database via an index, frequency information storage means for storing access frequency information of each attribute for data to be searched, and the frequency information storage for each access to each attribute. Access means for updating access frequency information about the accessed attribute in the means, and evaluation means for evaluating whether or not the contents of the index should be updated based on the access frequency information for each attribute in the frequency information storage means An automatic index updating device comprising: an updating unit that updates the contents of the index based on an evaluation result of the evaluation unit.