JP2008015810A - Index division management method for table data with multiple columns - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide an index management technique capable of maintaining retrieval efficiency and data storage efficiency in table data including a plurality of fields in spite of frequent update of the fields. <P>SOLUTION: When table data including a plurality of fields (columns) is managed on a computer, an index for the table data, a management object is created for columns with low update frequency except columns with high update frequency. At the time of search, it is checked whether a search condition for the columns with low update frequency is included in a designated search condition or not, and when it is included, search using the index is performed. Records are narrowed at high speed by the search using the index, and search is performed by sequential search to the narrowed records with a search condition for the columns with high update frequency excluded from the index structure. <P>COPYRIGHT: (C)2008,JPO&INPIT

Description

  The present invention relates to a technique for dividing and managing data in a table composed of a plurality of columns according to the data update frequency using an index defined for the data stored in the table.

  In a conventional database management system (DataBase Management System: DBMS), data is registered in a table and managed. The table is composed of records (rows) and fields (columns). When searching the specified data, the search is performed in the order in which the records are stored. Therefore, when the number of registered records is large, it cannot be easily extracted. Therefore, in order to improve the retrieval efficiency of data registered in large quantities, a method of creating an index (index) for data registered in a table is used.

In addition, there exists the following patent document 1 as a well-known technical document relevant to this invention. This document discloses a technique for searching using an index file for efficient and high-speed data extraction even when a file to be searched such as a CSV file becomes large.
JP 2003-162545 A

  In creating an index, according to the above-mentioned Patent Document 1, it is necessary to arrange data registered in a table in advance and create an index for each field. However, since the records and data registered in the table are constantly updated, the index management information is also frequently changed, which reduces the search using the index information and the storage efficiency of the data, A useful effect cannot be obtained.

  Therefore, an index search method using B-Tree (Binary-Tree) has been considered. By using this method, it is possible to search as sorted data regardless of the storage order of the records in the table. A tree-structured index used in the B-Tree (Binary-Tree) index search technique is called an index tree. An index tree can be configured for a plurality of fields, thereby increasing the data retrieval efficiency.

  However, as described above, since the records registered in the table are constantly updated, the index tree is constantly updated accordingly. For this reason, the index tree structure is disturbed, and the efficiency when searching for the designated data is reduced. Furthermore, since the data storage area is fragmented, the frequency at which the data storage area needs to be reconfigured increases. When the data storage area is reconfigured, the business is suspended once, so the business suspension period becomes long.

  It is an object of the present invention to provide an index management technique capable of maintaining search efficiency and data storage efficiency even when table data composed of a plurality of fields is frequently updated. It is in.

  In order to achieve the above object, the index division management method of the present invention, when managing table data having a plurality of fields (columns) on a computer, exceeds the predetermined reference value for the table data to be managed. Fields with a high update frequency are excluded from the index creation target, and other fields with a low update frequency less than a predetermined reference value are created as index creation targets. When searching, it is checked whether or not the instructed search condition includes a search condition for a field with a low update frequency below the predetermined reference value, and if so, a search using the index is performed. To. Specifically, when the instructed search condition includes only the search condition for a field with a low update frequency that is less than a predetermined reference value, a record that satisfies the instructed search condition can be searched by searching using an index. . If the specified search condition includes both a search condition for a field with a low update frequency below a predetermined reference value and a search condition for a field with a high update frequency at a predetermined reference value or higher, an index is used first. In this search, the records satisfying the search condition for the field with low update frequency are narrowed down, and the narrowed-down records are sequentially searched to search for the record satisfying the search condition for the field with high update frequency.

  Furthermore, when updating data, if the specified update condition includes an update condition for a field with a low update frequency less than the predetermined reference value, the index is recreated after the field is updated, When the updated update condition includes only an update condition for a field whose update frequency is higher than the predetermined reference value, only the update of the field is performed.

  The index has, for example, a tree structure with the index creation field as a key. The search using the index uses, for example, a B-Tree search (Binary-Tree Search) method having a tree structure. In some cases, the index storage capacity may be insufficient due to the update of the table data. In this case, the update field may be automatically excluded from the index creation target.

According to the index division management method for indexes for a plurality of columns according to the present invention, the following effects are obtained.
(1) Since a field with a high data update frequency is excluded from the index composition target, since the index is not changed when the field is updated, the data storage efficiency of the index can be maintained. Further, since a high-speed search using an index can be performed at the time of search, it is possible to maintain and improve data search efficiency.
(2) Since fields with high data update frequency are excluded from the index configuration target, the data storage area of the fields with high data update frequency is insufficient, and the index is changed even when it is divided and stored in another data storage area. Therefore, it is possible to maintain and improve the data retrieval efficiency and maintain the index data storage efficiency.

  Hereinafter, an embodiment of an index division management method for indexes on table data of a plurality of columns to which the present invention is applied will be described.

  FIG. 1 is a block diagram showing a configuration example of a database management system according to an embodiment of the present invention. The database management system device D1 includes a database access unit D3 and a data management unit D4. The application D2 specifies a table name, a search condition, etc. as input information and requests the database access unit D3 to perform processing. The database access unit D3 includes a data input unit D31 that receives input information specified by the application D2, a data operation unit D32 that extracts and updates information in the data management unit D4, and a data output unit that transfers result information to the application D2. D33 is provided. The data management unit D4 is an external storage device that manages various data. Each data is stored and managed in a table D41, and an index D42 is created to manage the data in the table D41. The data management unit D4 can also be configured with a plurality of external storage devices. Note that the database management system apparatus D1 and the application D2 in FIG. 1 are specifically realized by executing predetermined software on a computer.

  FIG. 2 is a data configuration diagram showing an example of a table managed by the system of FIG. The table T1 includes three fields of field 1 (T11), field 2 (T12), and field 3 (T13), and data is stored in the order of registration. It is assumed that hiragana characters are stored in field 1 (T11), alphabetic characters are stored in field 2 (T12), and numbers are stored in field 3 (T13). The update frequency of the data stored in the table T1 is low in the field 1 (T11) and the field 2 (T12) and high in the field 3 (T13).

  In this embodiment, in order to improve the search efficiency of the table T1, an index I1 as shown in FIG. 3 is created for the table T1. The index I1 creates an index tree I11 based on the data stored in the table T1, and sets the index management unit I12 that can specify the position of each data to the field 1 (T11) and the field 2 that are less frequently updated. This is created for the two fields of (T12). The field 3 (T13) with high data update frequency is not included in the management target of the index management unit I12. Therefore, when performing a search of field 3, it is necessary to adopt a sequential search method in which a search is performed while referring to the data of field 3 of each record in the table in order without using an index.

  Assume that the value “5” of the field 3 (T13) of the record T14 (FIG. 2) of the table T1 is updated to “11” in a state where the index I1 is defined in the table T1. At this time, the index I1 is in the state of the index I2 as shown in FIG. As can be seen from FIG. 4, since field 3 (T13) is excluded from the index tree construction target, there is no change to index management unit I12, and index management units I11 and I12 are the same. Also, since the value of the index key information I13 is only updated to I23 and no separate area is used, the data storage efficiency in the index management unit can be maintained. Further, since the index I1 before the data update and the index I2 after the data update are not changed at all, the search efficiency so far can be maintained.

  FIG. 5 is a flowchart at the time of creating an index in the index management method according to this embodiment. When creating an index for a table, first, it is determined whether or not the update frequency of the field to be configured in the index tree is high (step C01). If the column has a high data update frequency, the process ends without being included in the index tree configuration target. If it is a column with a low data update frequency, the data stored in that column is registered in ascending order and key information is registered as an index management unit (step C02), and an index tree is constructed (step C03).

  FIG. 6 is a flowchart at the time of data search in the index management method according to this embodiment. The instruction given at the time of data retrieval is, for example, “{(column 1 is“ U ”) and (column 2 is“ B ”) and (column 3 is“ 8 ”)} or {(column Search for a record in which 1 is “O”) and (column 2 is “C”) and (column 3 is “7”)}. The pre-processing before entering the processing of FIG. 6 arranges the given instructions, and sets the search condition (for example, “column 1 is“ U ””) for any one field (column) to “and”. The search is divided into several search conditions, with the unit connected in (1) as a unit. The processing of steps S01 to S09 is performed for each divided search condition.

  First, when the search condition for the first processing target includes a search condition for a field with low data update frequency, a search is performed using the index tree with the search condition for the field with low update frequency (step S01). The index tree is created for fields with a low update frequency, and by using this, a search can be performed at high speed. Then, the index management unit determines whether there is a record that satisfies the search condition (step S02). If there is no record, it means that there is no record that satisfies the search condition. It is determined whether there is a search condition (separated by the above prior process). If there is, the search condition is set as a processing target (step S10), and the process is repeated from step S01.

  If there is a record that satisfies the search condition for the field with low update frequency in step S02, it means that the record is narrowed down with the search condition for the field with low update frequency. As an object, the processing after step S03 is performed. First, it is determined whether the search condition to be processed includes a search condition for a field that is not an index configuration target (that is, a field with a high data update frequency) (step S03). When such a search condition is included, the records that are narrowed down by the search condition for the field with low update frequency are searched, and records that satisfy the search condition for the field with high update frequency are sequentially searched (step S04, In steps S06 to S08), the process proceeds to step S09 when all records matching the search conditions are output as search results. If the search condition for the field that is not the index construction target is not included in step S03, the search results in steps S01 and S02 are records that satisfy the search target condition for processing, so these records are output as search results ( Step S05), the process proceeds to Step S09.

  If the search condition to be processed consists only of search conditions for fields with a high update frequency, the search process using the indexes in steps S01 and S02 is skipped, and all records in the table are searched. Assuming that the processing after step S03 is performed.

  The above data search command “{(column 1 is“ U ”) and (column 2 is“ B ”) and (column 3 is“ 8 ”)} or {(column 1 is“ o ”) and (column 2 is“ C ”) and (search for a record in which column 3 is“ 7 ”)} is given”, the flow of FIG. 6 will be described. Before entering the processing of FIG. 6, the above instructions are changed to “(column 1 is“ U ”) and (column 2 is“ B ”) and (column 3 is“ 8 ”)” and “(column 1 Is divided into two search conditions: “O”) and (column 2 is “C”) and (column 3 is “7”).

  First, the processing from step S01 is performed with the search condition “(column 1 is“ U ”) and (column 2 is“ B ”) and (column 3 is“ 8 ”) as a processing target. Of these search conditions, the search conditions “(column 1 is“ U ”) and (column 2 is“ B ”)” for columns 1 and 2 in which the index is created are searched at high speed using the index. it can. Therefore, in steps S01 and S02, a search using an index is performed under the search condition “(column 1 is“ U ”) and (column 2 is“ B ”)”. As a result, the records of “U B 5” and “U B 8” in FIG. 2 are narrowed down. The search condition to be processed includes the search condition “column 3 is“ 8 ”” for the column 3 for which no index has been created. Therefore, the process proceeds from step S03 to S04, and the search conditions are narrowed down in steps S04 and S06 to S08. Using two records as search targets, records that satisfy the search condition “column 3 is“ 8 ”” are sequentially searched. As a result, the record “U B 8” is output as the search result. This result is stored in a predetermined buffer as a search result of the search conditions “(column 1 is“ U ”) and (column 2 is“ B ”) and (column 3 is“ 8 ”).

  Next, the process proceeds from step S09 to S10, and the processing from step S01 is performed for the next search condition “(column 1 is“ O ”) and (column 2 is“ C ”) and (column 3 is“ 7 ”)”. In the same manner as described above, the search is narrowed down by the search using the index, the forward search is performed, and the record “O C 7” is obtained as the search result. Thereafter, since there is no next search condition in step S09, the data search is terminated. Since the two search conditions are connected with “or”, the final result is “(U B 8) or (O C 7)” obtained by connecting the search results with “or”.

  FIG. 7 is a flowchart when updating data in the index management method according to this embodiment. At the time of data update, a search condition for specifying a record to be updated is given, information for specifying a field to be updated of the record specified by the search condition, and a value set in the field are given. Shall.

  First, it is determined whether the update target field is an index tree construction target field (step U01).

  If the field is an index tree configuration target field, the record to be updated is searched using the index tree in accordance with the given search condition (step U02), and the corresponding index management record is deleted from the index tree. (Step U03), the data of the corresponding field of the corresponding record is updated (Step U04). Next, in order to search for a new position where the updated index management record is to be inserted, a search is performed again using the index tree (step U05), and the corresponding index management record is added to the index tree. An index tree is re-created (step U06).

  If the search condition for specifying the record to be updated consists only of search conditions for fields that are not included in the index tree structure, in step U02 instead of searching using the index, the search target records A search is performed, and in step U03, the index management record of the record to be updated may be deleted. If the search condition for specifying the record to be updated includes both the search condition for the field that is the index tree construction target and the search condition for the field that is not the index tree construction target, the index tree of step U02 is Since it is only possible to narrow down the search by using only the search, it is necessary to perform a search under the search condition for the field that is not the object of the index tree configuration, but the search may be performed in step U02. In short, U02 to U06 are characterized in that, in addition to the process of updating the update target field of the update target record, the index of the record is recreated.

  If the field is not an index tree construction target in step U01, the record to be updated is first searched using the index tree in accordance with a given search condition (step U07). In step U07, when a search condition for specifying a record to be updated includes a search condition for a field that is an index tree construction target, a process for narrowing records with the search condition is performed. If the search condition for specifying the record to be updated consists only of the search condition for the field that is not the index tree construction target, step U07 is skipped.

  Next, when the search condition for specifying the record to be updated includes the search condition for the field that is not the index tree construction target, the record narrowed down in step U07 is set as the processing target, and one record of the processing target is set. The process of steps U08-U11 is performed while taking out one. First, it is determined whether or not the retrieved record satisfies the search condition for a field that is not a given index tree construction target (step U08). If the search condition is not satisfied in step U08, the process proceeds to step U10. If the search condition is satisfied in step U08, it means that the record is a record to be updated, so the field to be updated in the record is updated as specified (step U09), and the process proceeds to step U10. It is determined whether or not the next record exists (step U10). If it does not exist, the process proceeds to step U12. If it exists, the next record is set as a processing target (step U11), and the processes from step U08 are repeated.

  If the search condition for specifying the record to be updated does not include the search condition for the field that is not the index tree construction target, the record to be updated should already be specified only by the search in step U07. There is no need to perform a sequential search as in steps U08 to U11. Therefore, in this case, after step U07, the update target field of the update target record may be updated as specified, and the process may proceed to step U12. In short, U07 to U11 are characterized in that only the process of updating the update target field of the record to be updated is performed, and the index of the record is left as it is. As a result, the index tree structure does not change, and a new index management area is not used, so that the storage efficiency of the data management area can be maintained.

  Further, when the next data update is instructed (step U12), the processing from step U01 is repeated with the next search condition as a processing target (step U13) in order to perform the data update. At the stage when all the instructed data updates are completed, the data update and the index management information update are completed.

  In the above embodiment, whether or not to create an index is determined in step C01 in FIG. 5 depending on whether or not the field update frequency is high, but the determination of whether the update frequency is high or low is arbitrary. For example, the number of updates of each field in a predetermined period is aggregated, a threshold value (reference value) that determines whether the update frequency is high or low is determined, and a field that has been updated a number of times greater than or equal to the threshold value has an update frequency. It can be a high field. Further, a reference value with a high or low update frequency may be determined at any time by human judgment.

As described above, according to the index division management method for indexes on a plurality of columns according to the present embodiment, the following effects are obtained.
(1) Fields with a high data update frequency are excluded from the object of index tree configuration, and even when a record is updated, there is no change in the index tree, so that search efficiency can be maintained and improved.
(2) Since the field with high data update frequency is excluded from the index tree configuration target, the data storage area of the field with high data update frequency is insufficient, and the index tree even when divided and stored in another data storage area Since there is no change in the search efficiency, search efficiency can be maintained and improved.

It is a block diagram which shows an example of a database management system structure. It is a data block diagram which shows an example of the table | surface explaining embodiment of this invention. FIG. 3 is a data configuration diagram showing an index for the table of FIG. 2. FIG. 4 is a data configuration diagram showing the index of FIG. 3 when data is updated in the index management method according to the present invention. It is a flowchart figure at the time of the index creation in the index management method in this invention. It is a flowchart figure at the time of the data search by the index management method in this invention. It is a flowchart figure at the time of the data update in the index management method in this invention.

Explanation of symbols

  D1 ... Database Management System (DBMS), D2 ... Application, D3 ... Data Access Unit, D31 ... Data Input Unit, D32 ... Data Operation Unit, D33 ... Data Output Unit, D4 ... Data Management Unit, D41 ... Table D42 ... index, T1 ... table, I1, I2 ... index, I11 ... index tree, I12 ... index tree configuration target.

Claims (2)

An index partition management method for managing table data having a plurality of fields (columns) on a computer,
The index creation means provided in the computer excludes fields with a higher update frequency than the predetermined reference value from the management target table data from the index generation target, and updates the update frequency below the other predetermined reference value. For low fields, the index creation target is an index creation step;
The search means included in the computer includes a step of performing a search using the index when the instructed search condition includes a search condition for a field with a low update frequency less than the predetermined reference value. Index division management method for multi-column table data. The index partition management method for table data of a plurality of columns according to claim 1,
The updating means provided in the computer is
If the instructed update condition includes an update condition for a field with a low update frequency less than the predetermined reference value, re-creating the index after updating the field;
If the instructed update condition includes only an update condition for a field having a higher update frequency than the predetermined reference value, only updating the field; and
An index division management method for table data of a plurality of columns, comprising:

Images