JP5287071B2 - Database management system and program - Google Patents

Description

  The present invention relates to a database management system and a program for searching for a desired record using an index.

  As an index structure often used in a database management system, there is a B-tree index. For example, in the case of a B + tree, the structure is composed of a lowermost node called a leaf, a node called a root, and a node between these nodes called a branch, and the leaf has a key and a value as an entry. The pair is stored, and the branch stores data obtained by adding a pointer to a child node to a leaf entry. Here, the key refers to the data of the index creation target column, and the value refers to an identifier (hereinafter referred to as ROWID) for uniquely identifying a row of the table.

  Such a tree structure is suitable when the key is unique or there are many types of keys. However, when the number of types of keys is small, a plurality of the same keys are stored, so the page usage efficiency is not good.

  Another index structure is a bitmap index. In the bitmap index, a bitmap is created for each key, and the value is represented by whether or not a bit is set at a position corresponding to the value. Although this index may be collated faster than the B-tree index, there is a demerit that it is not suitable for simultaneous execution of update processing.

  In addition, as another method for improving the use efficiency of a page, a method of combining a plurality of hierarchical data structures or compressing a key has been proposed. For example, Patent Document 1 proposes an index using a hierarchical data structure composed of a first-stage data structure called a decision graph and a second-stage data structure called a conclusion set. In particular, in this Patent Document 1, “the entire key related to the node is not stored”, “each entry in the conclusion set is: the exact value of the key related to the entry in the conclusion set. As shown in descriptions such as “maintaining key field and ...”, “the key stored in the conclusion set is stored in a compressed form”, etc. Is compressed and stored.

Japanese translation of PCT publication No. 2004-527813

  However, even with the method disclosed in Patent Document 1, it is still inadequate in terms of page usage efficiency because the key is stored in the second-stage data structure.

  Further, in the conventional database management system, handling of NULL is also a factor for lowering the specification efficiency of the page. There are two ways to indicate whether a piece of data is NULL. One is a method in which a specific value in the data range is regarded as NULL internally, and the other is a method of adding a flag indicating whether the data is NULL.

  However, the former method means that the specific value cannot be used by the user, and is not always available. Although the latter method does not have such a restriction, it requires an area for a flag in addition to the data that is originally intended to be stored, so that the use efficiency of the page is lowered.

  The present invention has been made in view of the above, and an object of the present invention is to provide a database management system and a program capable of improving the page use efficiency and the cache effect.

  The database management system according to another aspect of the present invention provides the first value, the second value, the first value, and the second value that can be registered in a predetermined field among a plurality of records. A database management system for creating an index in which position information of records included in predetermined fields is associated with each other, wherein the first value and the position information include a first data structure and a second data structure. The first data structure so that the second value and the position information are associated with each other by a combination of the first data structure and the third data structure. The second data structure, the index creating means for creating the third data structure, the first data structure and the second data structure created by the index creating means And storage means for storing the third data structure as an index, wherein the first data structure is the second value associated with the first value and the first value. The position information of the data structure is included as an entry, and the position information of the third data structure associated with the second value is included as an entry. The second data structure includes the first information The position information of the record including the value of the predetermined field in the predetermined field is included as an entry, and the first value itself is not included as an entry. The third data structure includes the second value and the second value. The position information of the record including the value in the predetermined field is included as an entry.

  The database management system according to yet another aspect of the present invention includes a NULL value, a non-NULL value, a NULL value, and a non-NULL value that can be registered in a predetermined field among a plurality of records, respectively. A database management system for creating an index in which position information of records is associated with each other, wherein the NULL value and the position information are associated with each other by a combination of a first data structure and a second data structure. The first data structure and the second data structure such that the non-NULL value and the position information are associated with each other by a combination of the first data structure and the third data structure. Index creation means for creating the third data structure, and the first data structure created by the index creation means Storage means for storing the second data structure and the third data structure as an index, wherein the first data structure is associated with the NULL value. Position information of the data structure is included as an entry, and position information of the third data structure associated with the non-NULL value is included as an entry, and the second data structure includes the NULL value as the entry. The position information of the record included in the predetermined field is included as an entry and the NULL value itself is not included as an entry. The third data structure includes the non-NULL value and the non-NULL value in the predetermined field. The record position information is included as an entry.

  A database management system according to yet another aspect of the present invention provides a first value that can be registered in a first field among a plurality of records and a record that includes the first value in the first field. A second value of the record that is associated with the first position information and that can be registered in the second field between the plurality of records and the second field includes the second value in the second field. A database management system for creating an index that associates position information with each other, wherein the first value and the first position information correspond to each other by a combination of a first data structure and a second data structure Index creating means for creating the first data structure and the second data structure, and the first data structure and the second data structure created by the index creating means. Storage means for storing a field as an index, wherein the first data structure includes the first value and position information of the second data structure associated with the first value. And the second data structure includes the second value and the second location information of the record including the second value in the second field as the entry, and The value of 1 itself is not included as an entry.

  According to still another aspect of the present invention, there is provided a program that stores the first value, the second value, the first value, and the second value that can be registered in a predetermined field between a plurality of records. A program for causing a computer to search for a desired record using an index stored in storage means in association with position information of a record included in each of the fields, wherein a desired key is the first key A second data structure that comprises the index and is associated with the first value by retrieving the desired key from the first data structure that comprises the index when indicating a value Obtaining the position information, and, if the desired key indicates the second value, retrieving the desired key from the first data structure to construct the index and the first key 2 A step of acquiring position information of a third data structure associated with the value, and second data associated with the acquired position information when the desired key indicates the first value. A step of acquiring position information of a record including the first value in the predetermined field from a structure, and when the desired key indicates the second value, the position information is associated with the acquired position information Obtaining the positional information of the record including the second value in the predetermined field by retrieving the second value from the third data structure provided, and including the second The data structure includes the position information of the record including the first value in the predetermined field as an entry and does not include the first value itself as an entry. The third data structure includes The second value and the second value Characterized in that it comprises a position information of the records that contain the predetermined field as an entry.

  According to still another aspect of the present invention, there is provided a program including a NULL value, a non-NULL value, a NULL value, and a non-NULL value that can be registered in a predetermined field among a plurality of records. A program for causing a computer to search for a desired record using an index stored in the storage means in association with the position information of the information, and forming the index when the desired key indicates a NULL value A first step of acquiring position information of a record including the NULL value in the predetermined field from the first data structure, and a first key constituting the index when a desired key indicates a non-NULL value. 2 by retrieving the non-NULL value from the data structure of 2 and including the non-NULL value in the predetermined field. A second step of acquiring the position information of the first data structure, wherein the first data structure includes the position information of the record including the NULL value in the predetermined field as an entry, and the NULL value itself as an entry. It is not included.

  A program according to still another aspect of the present invention provides a first value that can be registered in a first field among a plurality of records, and a first value of a record that includes the first value in the first field. And the second position information of the record including the second value in the second field that can be registered in the second field between the plurality of records. Is a program for causing a computer to search for a desired record using an index stored in the storage means, wherein the first value is obtained from a first data structure constituting the index. A first step of acquiring position information of a second data structure that constitutes the index and is associated with the first value by searching for a first key to be shown; and the acquired position information Associated A second step of acquiring position information of a record including the second value in the predetermined field by searching a second key indicating the second value from the second data structure; And the second data structure does not include the first value as an entry.

  According to the present invention, the index of the record specified by each value (key) that can be taken in the same field is configured in at least a two-stage structure, and the subsequent index does not include the key of the previous index as an entry. In addition, the page usage efficiency and cache effect can be increased, and the concurrent execution performance of update processing is not reduced by avoiding the use of data structures such as those used in bitmap indexes for subsequent data structures. There is an effect that can be.

  Exemplary embodiments of a database management system and a program according to the present invention will be explained below in detail with reference to the accompanying drawings.

  FIG. 1 is a schematic diagram showing a configuration of a database management system that is common among embodiments to be described later. As shown in FIG. 1, the database management system 100 can be realized by a computer such as a PC (Personal Computer). Specifically, the database management system 100 stores a CPU (Central Processing Unit) 102 that centrally controls each unit of the database management system 100, a program that operates the CPU 102, and various data that is a processing target of the CPU 102. A primary storage device 105 and a secondary storage device 106 storing a database are configured, and data is transmitted and received between these units via a data bus 114. The primary storage device 105 includes a high-speed semiconductor storage device such as a ROM (Read Only Memory) 103 and a RAM (Random Access Memory) 104, and the secondary storage device 106 is a high-capacity magnetic storage device. It consists of a certain HDD (Hard Disk Drive) 107.

  In addition, the database management system 100 according to the embodiment of the present invention includes input / output means for enabling reception of processing requests necessary for database management and output of processing results. The input / output means may be realized by the network interface 110, or a configuration provided in a general-purpose PC, that is, a removable disk device 108 that drives a portable storage medium 108a such as a CD-ROM or FD, It may be realized by a display device 111 such as a CRT (Cathode Ray Tube) or LCD (Liquid Crystal Display), a keyboard 112 or an pointing device 113 for an operator to input commands or information to the CPU 102. Further, as shown in the figure, the database management system 100 may be provided with all of them connected to the data bus 114. When the input / output means is realized by the network interface 110, the database management system 100 can function like a server of a data warehouse.

  As described above, various processes performed by the database management system 100 are executed by the CPU 102. More specifically, it is a database management program developed on the RAM 104 of the primary storage device 105. The database management program is composed of a plurality of modules that share functions, and these modules can be said to be components of the database management system 100. The database management program may be stored in advance in the ROM 103 or the HDD 107, may be provided by the storage medium 108a, or may be provided from an external device via the network 109 by the network interface 110.

  FIG. 2 is a schematic configuration diagram of the database management system 100 shown from the viewpoint of the module configuration described above. As shown in FIG. 2, the database management system 100 includes a database 25 stored in the HDD 107 and the like, a processing analysis unit 21 that receives a processing request such as an SQL command and performs parsing and optimization of the processing request, A search unit 22 that searches the database 25 according to the processing request analyzed by the processing analysis unit 21 and obtains the search result, and an update unit 23 that updates the database 25 according to the processing request analyzed by the processing analysis unit 21. And an output unit 24 that outputs a processing result such as a search result by the search unit 22 or an update result by the update unit 23. The module configuration shown in FIG. 2 indicates a minimum configuration for causing the database management system 100 to execute search processing and update processing. The database management system 100 has a multi-stage structure that can be said to be a feature of each embodiment described later. Means (for example, an index creating unit) for creating an index.

  Each embodiment described below shows processing of the database management system 100 having the above-described configuration. In order to facilitate understanding of the features of each embodiment, the database 25 is shown in FIG. Assuming that such a table is stored, the database management system 100 performs various processes on the table.

  The table shown in FIG. 3 includes eight records, and each record includes four fields of “ROWID”, “document name”, “situation”, and “worker”. This table represents the progress of work from creation to final approval for various documents.

  In the table shown in FIG. 3, the “document name” field is a column for storing the name of the document to be worked. The “situation” field is a limited data column whose value changes in accordance with the progress of the work of the document, such as creating, reviewing, approving, and approved, and relatively frequently updated. The “worker” field is a column in which the current worker for the document is stored. As the value, for example, the creator is created, the examiner is being reviewed, and the approver is being approved. The “operator” field is also frequently updated in the same manner as the “status” field.

In particular, the data in the “operator” field has the following characteristics.
-Since there can be as many workers as there are workers, there are many types of data.
-Since there is no worker for an approved document, "unnamed" is stored.
・ Workers are biased. Example: Manager employees are often workers. Since there are many approved documents, there are many “unnamed” workers.

  Hereinafter, a database management method executed by the database management system 100 for the table shown in FIG. 3 will be described for each different embodiment.

(First embodiment)
FIG. 4 is a conceptual diagram of indexes created in advance by the database management system 100 according to the first embodiment. As shown in FIG. 4, the index is hierarchized in two stages. The first-level index indicates a data structure having a plurality of different keys and position information (pointer) of another data structure associated with each key as entries. On the other hand, the second-level index is a data structure specified by a pointer associated with each key of the first-level data structure, and has a ROWID of a record including the corresponding key as an entry. In particular, the data structure in the second row has no key information, and consists only of ROWID information.

  This two-level index is particularly effective when the key is a value that is frequently updated and has a small number of possible values, such as the “status” field. In the example of FIG. 4, the first-level index is a pair of a value in which a record that actually includes all possible values in the “status” field and a pointer that is position information of each value. A data structure with the entry as. Specifically, the data structure includes a pair of “being created” and pointer 0x0a01, a pair of “under review” and pointer 0x0b01, and a pair of “approved” and pointer 0x0e01. . In the example of FIG. 4, the second-level index includes a data structure (identified by pointer 0x0a01) having ROWID1, 7, and 8 of the record including “being created” as a value and “under review”. A data structure (identified by the pointer 0x0b01) whose entry is ROWID2 of the record including “value” and a data structure (identified by the pointer 0x0e01) whose entry is ROWID4, 5 of the record including “approved” as the value Is included).

  Such an index is created by the database management system 100 and stored in a predetermined storage unit such as the HDD 107 or the removable disk device 108. An external device connected via the network interface 110 may be used as a storage unit for storing the index.

  Next, search processing according to the database management method according to the first embodiment will be described. FIG. 5 is a flowchart showing the search process. First, the processing analysis unit 21 receives a processing request such as an SQL sentence and analyzes and optimizes the request. Here, as a result of the analysis and optimization, the search unit 22 searches the index having the structure shown in FIG. 4 for a key that is frequently updated and has a small value type. Therefore, the pointer of the first data structure having the field to which the key belongs as an entry is acquired (step S101). Next, the retrieval unit 22 retrieves the designated key from the first-stage data structure (step S102), and obtains a pointer to the second-stage data structure corresponding to the retrieved key (step S102). Step S103). Subsequently, all ROWIDs of the data structure stored in the acquired pointer are acquired (step S104). Accordingly, all records including the target key, that is, search results can be acquired, and the search results are output from the output unit 24.

  In the example of FIG. 4, in this search, if “under review” is designated as a key, ROWID2 is finally acquired, and a record specified by ROWID2 is output as a search result. Follow.

  In this way, each data structure in the second stage holds ROWID information for the same key, so there is no need to store the key in each data structure.

  The structure applied to each data structure is not particularly limited, and may be a B-tree index structure or a hash table structure. However, it is preferable not to use a data structure such as that used in a bitmap index in order not to degrade the concurrent execution performance of the update process.

  In addition, the addition of data is performed by acquiring a pointer of the second-stage data structure to be added to the first-stage data structure as in the search, and adding a new data structure to the acquired data structure. Do this by adding data. The same applies to data deletion. For the first data structure, the pointer of the second data structure storing the data to be deleted is acquired, and the data is deleted from the acquired data structure. .

  As described above, according to the database management method according to the first embodiment, it is possible to improve the page usage efficiency by not registering the key in the second stage data structure. It is also possible to increase the effectiveness of the cache. Furthermore, by not using a data structure that is used in a bitmap index as the second-stage data structure, it is possible to prevent the simultaneous execution performance of update processing from being degraded.

(Second Embodiment)
FIG. 6 is a conceptual diagram of indexes created in advance by the database management system 100 according to the second embodiment. As shown in FIG. 6, the index is hierarchized in two stages as in the first embodiment. The index on the first level indicates a data structure composed of two types of entries. One of the two types (first type) is an entry similar to the first-stage index according to the database management method according to the first embodiment. That is, it is an entry in which a plurality of different keys and position information (pointers) of another data structure associated with each key are paired. In particular, the key included in this entry is preferably a key that is frequently registered among a plurality of keys. In the example of the table shown in FIG. 3, “unnamed” in the “worker” field, the name of a manager employee, and the like correspond to such a key. Another type (second type) is an entry in which all keys other than the first type and position information (pointers) of another data structure associated with them are paired. Note that which one of the plurality of keys belongs to the first type or the second type depends on, for example, a decision tree.

  On the other hand, the second-level index is associated with the data structure specified by the pointer associated with each key of the first-type entry in the first-level and each key of the second-type entry. And a data structure specified by the pointer. The data structure corresponding to the first type entry uses the ROWID of the record including the corresponding key as an entry, as in the second-stage data structure of the first embodiment. On the other hand, the data structure corresponding to the second type entry has a pair of each key and the corresponding ROWID as an entry.

  In the example of FIG. 6, the first-level index is the first type of data structure, among the values of the “worker” field, “unnamed” indicating that no value is registered, and a pointer 0x0901. , A pair of “Suzuki” and pointer 0x0011, etc., and another structure associated with other values not included in the first type as the second type data structure Only position information (pointer). In the example of FIG. 6, the second-level index is a data structure corresponding to the first-type entry of the first level, and the ROWIDs 4 and 5 of the record meaning “unnamed” registration are the entries. A data structure (identified by pointer 0x0901) and a data structure (identified by pointer 0x0011) having ROWID 3 and 6 of a record including “Suzuki” as a value are included. Further, in the example of FIG. 6, the second-level index includes “Sato” in the “worker” field and its value as a data structure corresponding to the second-type entry (others) in the first level. A pair of ROWID 8 of the record, a pair of “Yamada” in the “worker” field and ROWID 1 of the record including the value, and a pair of “Yamada” in the “worker” field and ROWID 7 of the record including the value And a data structure (identified by pointer 0x0f01).

  Similarly to the first embodiment, the index used in the second embodiment is created by the database management system 100 and stored in a predetermined storage unit.

  Next, search processing according to the database management method according to the second embodiment will be described. FIG. 7 is a flowchart showing the search process. First, the processing analysis unit 21 receives a processing request such as an SQL sentence and analyzes and optimizes the request. Here, as a result of the analysis and optimization, the search unit 22 searches the index having the structure shown in FIG. 6 for a key whose value is biased so as to be obtained from the decision tree. Therefore, the pointer of the first data structure having the field to which the key belongs as an entry is acquired (step S201). Next, the search unit 22 searches for the designated key from the first-type data structure of the first level (step S202). If the designated key is found in the first type data structure of the first level (step S203, Yes), the pointer of the second level data structure corresponding to the key is acquired (step S204) and acquired. All ROWIDs of the data structure stored in the pointer are acquired (step S205).

  On the other hand, if the designated key is not found in the first type data structure in the first level in step S203 (No in step S203), the pointer of the second level data structure corresponding to the other entry is set. Obtain (step S206), and search the designated key from the data structure (step S207). As a result of the search, the ROWID of the entry that matches the key is acquired (step S208). As described above, all records including the target key, that is, search results can be acquired, and the search results are output from the output unit 24.

  In this search, in the example of FIG. 6, if “Suzuki” is designated as a key, ROWIDs 3 and 6 are finally acquired, and records specified by these ROWIDs 3 and 6 are output as search results. Follow the process of being done. As another example, if “Yamada” is designated as a key, ROWID 1 and 7 are finally obtained, and the process of outputting the record specified by these ROWID 1 and 7 as a search result is followed.

  As in the first embodiment, there is no particular limitation on the structure applied to each data structure, but it is preferable not to use a data structure used in a bitmap index. Further, addition and deletion of data are as described in the first embodiment.

  If each second-level structure is created ignoring the number of entries stored therein, the page usage efficiency may be deteriorated. For example, even if there is only one entry in the structure, one page is reserved for the structure. If there are many such structures, the key is not stored in the second row. The effect will be diminished. Even in such a case, as described above, according to the database management method according to the second embodiment, the second level data structure corresponding to the second type entry in the first level By combining structures that contain only a few entries, page usage efficiency can be increased.

  In addition, when there are many types of keys, if the entries are created for all the keys, the search cost in the first-stage structure cannot be ignored. Even in such a case, according to the database management method according to the second embodiment, in the first stage, the entries of the data structure in the first stage are not stored one by one for the less frequently used keys. The number of entries can be reduced, and the search cost can be reduced.

(Third embodiment)
FIG. 8 is a conceptual diagram of an index created by the database management system 100 according to the third embodiment. As shown in FIG. 8, the index is hierarchized in two stages as in the first embodiment. The index on the first level indicates a data structure composed of two types of entries. The two types are an entry of position information (pointer) of another data structure associated with a key indicating NULL, and position information (position of another data structure associated with a key indicating non-NULL ( Pointer) entry.

  On the other hand, the second-stage index is composed of a data structure specified by a pointer associated with the first-stage NULL entry and a data structure identified by a pointer associated with non-NULL. In the former data structure, the ROWID of the record including the corresponding key is used as an entry, as in the second-stage data structure of the first embodiment. On the other hand, the latter data structure has a pair of each key and the corresponding ROWID as an entry.

  This index is suitable for the case where NULL is stored as a value in the “worker” field when the “situation” field indicates a value of “approved” in the table shown in FIG. 3.

  In the example of FIG. 8, the first-level index includes, as data structures, another structure pointer 0x0201 associated with a NULL entry and another structure associated with a non-NULL entry. Pointer 0x0801. In the example of FIG. 8, the second-level index includes a data structure (identified by pointer 0x0201) having ROWID 4 and 5 as entries, as a data structure corresponding to the first-level NULL entry. It is out. Further, in the example of FIG. 8, the second-level index is a data structure corresponding to the non-NULL entry in the first level, and “Sato” in the “worker” field and the ROWID 8 of the record including the value. An entry includes a pair of “Yamada” in the “worker” field and ROWID1 of the record including the value, and a pair of “Yamada” in the “worker” field and ROWID7 of the record including the value. Data structure (identified by pointer 0x0801).

  Similarly to the first embodiment, the index used in the third embodiment is also created by the database management system 100 and is stored in a predetermined storage means.

  Next, a search process according to the database management method according to the third embodiment will be described. FIG. 9 is a flowchart showing the search process. First, the processing analysis unit 21 receives a processing request such as an SQL sentence and analyzes and optimizes the request. Here, as a result of the analysis and optimization, the search unit 22 searches for the key identified as either NULL or non-NULL from the index having the structure shown in FIG. Therefore, the pointer of the first-stage data structure having the field to which the key belongs as an entry is acquired (step S301). Next, the search unit 22 determines whether the designated key is NULL or non-NULL (step S302). If the specified key is NULL (step S302, Yes), the pointer of the second-stage data structure corresponding to NULL is acquired (step S303), and all of the data structures stored in the acquired pointer are acquired. ROWID is acquired (step S304).

  On the other hand, if the specified key is non-NULL in step S302 (No in step S302), a pointer to the second-stage data structure corresponding to non-NULL is acquired (step S305), and the data structure is retrieved from the data structure. The designated key is searched (step S306). As a result of the search, the ROWID of the entry that matches the key is obtained as a pointer to the second-stage data structure corresponding to the other entry (step S307). As described above, all records including the target key, that is, search results can be acquired, and the search results are output from the output unit 24.

  In this search, in the example of FIG. 8, if NULL is specified as a key, ROWIDs 4 and 5 are finally acquired, and a record specified by these ROWIDs 4 and 5 is output as a search result. Follow the process. As another example, if non-NULL “Yamada” is designated as a key, ROWID 1 and 7 are finally acquired, and a record specified by these ROWID 1 and 7 is output as a search result. Follow.

  As in the first embodiment, there is no particular limitation on the structure applied to each data structure, but it is preferable not to use a data structure used in a bitmap index. Further, addition and deletion of data are as described in the first embodiment.

  As described above, according to the database management method according to the third embodiment, a data structure having a NULL key is prepared separately from a non-NULL data structure. It is not necessary to store a flag indicating the state, and as a result, the use efficiency of the page can be increased.

(Fourth embodiment)
The database management system 100 according to the fourth embodiment creates an index combining the index shown in the first embodiment and the index shown in the third embodiment, and performs a search using the index. It is characterized by performing. Specifically, the first-stage index and the second-stage index are configured as the first-stage index and the second-stage index shown in the third embodiment, and the second-stage index is made non-NULL. The corresponding index and the third level index are configured as the first level index and the second level index shown in the first embodiment.

  FIG. 10 is a conceptual diagram of an index built in advance by the database management method according to the fourth embodiment. In FIG. 10, with respect to the first and second tier indexes, except that the data structure entry corresponding to non-NULL is associated with a pointer instead of ROWID for each key. Since this corresponds to the two-level index structure shown in FIG. In FIG. 10, the index corresponding to the non-NULL of the second-level index and the third-level index are the same as the concept of the two-level index structure shown in FIG. Omitted.

  Next, a search process according to the database management method according to the fourth embodiment will be described. FIG. 11 is a flowchart showing the search process. First, the processing analysis unit 21 receives a processing request such as an SQL sentence and analyzes and optimizes the request. Here, as a result of the analysis and optimization, the search unit 22 searches for the key identified as either NULL or non-NULL from the index having the structure shown in FIG. Therefore, the pointer of the first data structure having the field to which the key belongs as an entry is acquired (step S401). Next, the search unit 22 determines whether the designated key is NULL or non-NULL (step S402). If the specified key is NULL (step S402, Yes), the pointer of the second-stage data structure corresponding to NULL is acquired (step S403), and all of the data structures stored in the acquired pointer are acquired. ROWID is acquired (step S404).

  On the other hand, if the specified key is non-NULL in step S402 (step S402, No), a pointer to the second-stage data structure corresponding to non-NULL is acquired (step S405), and the data structure is obtained. The designated key is searched (step S406). Then, the pointer of the third-stage data structure corresponding to the retrieved key is acquired (step S407), and all the ROWIDs of the data structure stored in the acquired pointer are acquired (step S408). Accordingly, all records including the target key, that is, search results can be acquired, and the search results are output from the output unit 24.

  In this search, in the example of FIG. 10, if NULL is designated as a key, ROWIDs 4 and 5 are finally acquired, and a record specified by these ROWIDs 4 and 5 is output as a search result. Follow the process. As another example, if “Yamada” is designated as a key, ROWID 1 and 7 are finally obtained, and the process of outputting the record specified by these ROWID 1 and 7 as a search result is followed.

  As in the first embodiment, there is no particular limitation on the structure applied to each data structure, but it is preferable not to use a data structure used in a bitmap index. Further, addition and deletion of data are as described in the first embodiment.

  As described above, according to the database management method according to the fourth embodiment, the index structure shown in the first embodiment and the index structure shown in the third embodiment are the same. By combining them, an index having a three-stage structure is created and a search using the index is possible, so that the effects of the database management methods according to both embodiments can be enjoyed.

(Fifth embodiment)
The database management system 100 according to the fifth embodiment creates an index that combines the index shown in the second embodiment and the index shown in the third embodiment, and performs a search using the index. It is characterized by performing. Specifically, the first-stage index and the second-stage index are configured as the first-stage index and the second-stage index shown in the third embodiment, and the second-stage index is made non-NULL. The corresponding index and the third-stage index are configured like the first-stage index and the second-stage index shown in the second embodiment.

  FIG. 12 is a conceptual diagram of an index built in advance by the database management method according to the fifth embodiment. In FIG. 12, with respect to the first-level index and the second-level index, the second structure is that the entry of the data structure corresponding to non-NULL is associated with a pointer instead of ROWID for each key. Except for the fact that it has two types of data structures as shown in the embodiment, it corresponds to the two-level index structure shown in FIG. In FIG. 12, the index corresponding to non-NULL of the second-level index and the third-level index are the same as the concept of the two-level index structure shown in FIG. Omitted.

  Next, search processing according to the database management method according to the fifth embodiment will be described. FIG. 13 is a flowchart showing the search process. First, the processing analysis unit 21 receives a processing request such as an SQL sentence and analyzes and optimizes the request. Here, as a result of the analysis and optimization, the search unit 22 searches the index having the structure shown in FIG. 12 for a key identified as either NULL or non-NULL. Therefore, the pointer of the first data structure having the field to which the key belongs as an entry is acquired (step S501). Next, the search unit 22 determines whether the designated key is NULL or non-NULL (step S502). If the specified key is NULL (Yes in step S502), the pointer of the second-stage data structure corresponding to NULL is acquired (step S503), and all of the data structures stored in the acquired pointer are acquired. ROWID is acquired (step S504).

  On the other hand, if the designated key is non-NULL in step S502 (No in step S502), a pointer to the second-stage data structure corresponding to non-NULL is acquired (step S505), and the data structure is obtained. The designated key is searched (step S506). If the specified key is found in the first type data structure of the second level corresponding to non-NULL (step S507, Yes), the pointer of the third level data structure corresponding to the key is acquired ( In step S508), all ROWIDs of the data structure stored in the acquired pointer are acquired (step S509).

  On the other hand, if the designated key is not found in the first type data structure in the second level in step S507 (No in step S507), the pointer of the third level data structure corresponding to the other entry is set. Obtain (step S510), and search for the designated key from the data structure (step S511). As a result of the search, the ROWID of the entry that matches the key is acquired (step S512). As described above, all records including the target key, that is, search results can be acquired, and the search results are output from the output unit 24.

  In this search, in the example of FIG. 12, if NULL is specified as a key, ROWIDs 4 and 5 are finally acquired, and records specified by these ROWIDs 4 and 5 are output as search results. Follow the process. As another example, if “Yamada” is designated as a key, ROWID 1 and 7 are finally obtained, and the process of outputting the record specified by these ROWID 1 and 7 as a search result is followed.

  As in the first embodiment, there is no particular limitation on the structure applied to each data structure, but it is preferable not to use a data structure used in a bitmap index. Further, addition and deletion of data are as described in the first embodiment.

  As described above, according to the database management method according to the fifth embodiment, the index structure shown in the second embodiment and the index structure shown in the third embodiment are the same. By combining them, an index having a three-stage structure is created and a search using the index is possible, so that the effects of the database management methods according to both embodiments can be enjoyed.

(Sixth embodiment)
In the first to fifth embodiments, the database management system 100 according to the sixth embodiment creates an index that enables a so-called compound search by performing a search using different keys. It is characterized by performing the search used.

  Here, in order to simplify the explanation, a mode in which two different keys are given to the index shown in the first embodiment will be described.

  FIG. 14 is a conceptual diagram of an index built in advance by the database management method according to the sixth embodiment. As shown in FIG. 14, the index is hierarchized in two stages. The first level index indicates the same data structure as the first level in FIG. 4 shown in the first embodiment, but the second level data structure has a key different from the first level key. Each entry includes a pair of ROWID. In other words, a key is associated with each entry of the second-stage data structure shown in FIG.

  Next, search processing according to the database management method according to the sixth embodiment will be described. FIG. 15 is a flowchart showing the search process. First, the processing analysis unit 21 receives a processing request such as an SQL sentence and analyzes and optimizes the request. Here, as a result of the analysis and optimization, the search unit 22 has a bias in the values as obtained from the first key that is frequently updated and has a small number of possible values and the decision tree. A record including both of the second key keys is searched from the index having the structure shown in FIG. Therefore, first, the pointer of the first-stage data structure having the field to which the first key belongs as an entry is acquired (step S601). Next, the retrieval unit 22 retrieves the designated first key from the first-stage data structure (step S602), and a pointer to the second-stage data structure corresponding to the retrieved first key. Is acquired (step S603). Subsequently, the designated second key is searched from the data structure (step S604), and as a result of the search, the ROWID of the entry that matches the second key is obtained (step S605). Thereby, all records including both the intended first key and second key, that is, search results can be acquired, and the search results are output from the output unit 24.

  In this search, in the example of FIG. 14, “Creating” as the value of the “situation” field is designated as the first key, and “Yamada” as the value of the “worker” field is designated as the second key. Assuming that the ROWIDs 1 and 7 are finally acquired, the process of outputting the record specified by the ROWIDs 1 and 7 as a search result is followed.

  Thus, it is not necessary to store the first key at least in each data structure in the second stage.

  As in the first embodiment, there is no particular limitation on the structure applied to each data structure, but it is preferable not to use a data structure used in a bitmap index. Further, addition and deletion of data are as described in the first embodiment.

  As described above, according to the database management method according to the sixth embodiment, it is possible to enjoy the effects of the first embodiment even for complex search.

(Seventh embodiment)
A database management system 100 according to the seventh embodiment is a modification of the sixth embodiment. Specifically, a three-level index structure is created by applying the two-level index structure shown in the first embodiment to the second-level data structure shown in the sixth embodiment. And performing a search using the index. In other words, the two-stage index structure shown in the first embodiment is applied to the first key index and the second key index, respectively.

  FIG. 16 is a conceptual diagram of an index built in advance by the database management method according to the seventh embodiment. In FIG. 16, the first level index corresponds to the first level index shown in FIG. 14, and for the second level index, each data structure entry is not ROWID for each key. Except for the fact that the pointer is associated, it corresponds to the second-level index shown in FIG. In FIG. 16, the relationship between the second-stage index and the third-stage index is also the same as the two-stage index structure shown in FIG.

  Next, a search process according to the database management method according to the seventh embodiment will be described. FIG. 17 is a flowchart showing the search process. First, the processing analysis unit 21 receives a processing request such as an SQL sentence and analyzes and optimizes the request. Here, as a result of the analysis and optimization, the search unit 22 has a bias in the values as obtained from the first key that is frequently updated and has a small number of possible values and the decision tree. A record including both of the second key keys is searched from the index having the structure shown in FIG. Therefore, first, the pointer of the first-stage data structure having the field to which the first key belongs as an entry is acquired (step S701). Next, the retrieval unit 22 retrieves the designated first key from the first-stage data structure (step S702), and a pointer to the second-stage data structure corresponding to the retrieved first key. Is acquired (step S703). Subsequently, the designated second key is searched from the data structure (step S704), and the pointer of the third-stage data structure corresponding to the searched second key is obtained (step S705). Then, all ROWIDs of the data structure stored in the acquired pointer are acquired (step S706). Thereby, all records including both the intended first key and second key, that is, search results can be acquired, and the search results are output from the output unit 24.

  In the example of FIG. 16, this search specifies “Creating” as the value of the “Status” field as the first key, and “Sato” as the value of the “Worker” field as the second key. Assuming that the ROWID 8 is finally acquired, the process follows that a record specified by the ROWID 8 is output as a search result.

  Thus, it is not necessary to store the first key in each data structure in the second row, and it is not necessary to store the second key in each data structure in the third row.

  As in the first embodiment, there is no particular limitation on the structure applied to each data structure, but it is preferable not to use a data structure used in a bitmap index. Further, addition and deletion of data are as described in the first embodiment.

  In the above description, the index structure of the first embodiment is applied to the second and third stage indexes, but the index structure of another embodiment may be applied.

  As described above, according to the database management method according to the seventh embodiment, the index structure according to the first to fifth embodiments can be applied to the compound search, and those The effect by embodiment can be enjoyed.

  The database management method executed by the database management system 100 according to the first to seventh embodiments described above is a CD-program as a computer program in a format that can be installed on a computer such as a PC or an executable format. The present invention can be provided by being recorded on a computer-readable recording medium such as a ROM, a flexible disk (FD), a CD-R, and a DVD (Digital Versatile Disk).

  Further, such a computer program may be provided by being stored on a computer connected to a network such as the Internet and downloaded to a PC or the like via the network.

  The database management system, the database management method, and the program according to the embodiment of the present invention are more effective when applied to a management system such as a package delivery management system in which status data is frequently updated, such as package delivery status. .

It is the schematic which shows the structure of the database management system concerning embodiment of this invention. It is a module block diagram of the database management system concerning embodiment of this invention. It is a figure which shows the example of the table used as index object. It is a conceptual diagram of the index formed by the database management system concerning a 1st embodiment. It is a flowchart which shows the search process by the database management method concerning 1st Embodiment. It is a conceptual diagram of the index formed by the database management system concerning a 2nd embodiment. It is a flowchart which shows the search process by the database management method concerning 2nd Embodiment. It is a conceptual diagram of the index formed by the database management system concerning 3rd Embodiment. It is a flowchart which shows the search process by the database management method concerning 3rd Embodiment. It is a conceptual diagram of the index formed by the database management system concerning a 4th embodiment. It is a flowchart which shows the search process by the database management method concerning 4th Embodiment. It is a conceptual diagram of the index formed by the database management system concerning a 5th embodiment. It is a flowchart which shows the search process by the database management method concerning 5th Embodiment. It is a conceptual diagram of the index formed by the database management system concerning a 6th embodiment. It is a flowchart which shows the search process by the database management method concerning 6th Embodiment. It is a conceptual diagram of the index formed by the database management system concerning a 7th embodiment. It is a flowchart which shows the search process by the database management method concerning 7th Embodiment.

Explanation of symbols

21 Processing Analysis Unit 22 Search Unit 23 Update Unit 24 Output Unit 25 Database 100 Database Management System 102 CPU
103 ROM
104 RAM
105 Primary storage device 106 Secondary storage device 107 HDD
108 Removable Disk Device 108a Storage Medium 109 Network 110 Network Interface 111 Display Device 112 Keyboard 113 Pointing Device 114 Data Bus

Claims (12)

The first value and the second value that can be registered in a predetermined field between a plurality of records are associated with the position information of the records that respectively include the first value and the second value in the predetermined field A database management system for creating an index,
The first value and the position information are associated with each other by a combination of a first data structure and a second data structure, and the second value and the position information are the first data structure. Index generating means for creating the first data structure, the second data structure, and the third data structure so as to be associated with each other by a combination of the data structure and the third data structure;
Storage means for storing the first data structure, the second data structure, and the third data structure created by the index creation means as an index;
With
The first data structure includes the first value and position information of the second data structure associated with the first value as an entry, and is associated with the second value. Including the position information of the third data structure as an entry,
The second data structure includes, as an entry, position information of a record including the first value in the predetermined field, and does not include the first value itself as an entry.
The database management system according to claim 3, wherein the third data structure includes the second value and position information of a record including the second value in the predetermined field as entries. Database management for creating an index that associates NULL values and non-NULL values that can be registered in a predetermined field among a plurality of records, and position information of records that respectively include the NULL values and the non-NULL values in the predetermined fields A system,
The NULL value and the position information are associated by a combination of a first data structure and a second data structure, and the non-NULL value and the position information are the first data structure and the third data structure. Index creating means for creating the first data structure, the second data structure, and the third data structure so as to be associated with each other by a combination with the data structure;
Storage means for storing the first data structure, the second data structure, and the third data structure created by the index creation means as an index;
With
The first data structure includes, as an entry, position information of the second data structure associated with the NULL value, and the third data structure associated with the non-NULL value. Including location information as an entry,
The second data structure includes position information of a record including the NULL value in the predetermined field as an entry, and does not include the NULL value itself as an entry.
The database management system according to claim 3, wherein the third data structure includes, as entries, the non-NULL value and position information of a record including the non-NULL value in the predetermined field. The index creating means is configured to associate the non-NULL value and the position information with a combination of the first data structure, the third data structure, and the fourth data structure. And a fourth data structure are created,
The storage means stores the first data structure, the second data structure, the third data structure, and the fourth data structure created by the index creation means as indexes. ,
The third data structure includes, as entries, the non-NULL value and position information of the fourth data structure associated with the non-NULL value,
Said fourth data structure according to claim 2, wherein the free of non NULL value as and entry non NULL value itself includes as an entry position information of records that contain the predetermined field Database management system. The non-NULL value is classified into a first value and a second value,
The index creating means associates the first value and the position information by a combination of the third data structure and the fourth data structure, and the second value and the position information are Creating the third data structure, the fourth data structure, and the fifth data structure so as to be associated by a combination of the third data structure and the fifth data structure;
The storage means includes the first data structure, the second data structure, the third data structure, the fourth data structure, and the fifth data created by the index creating means. Store the structure as an index,
The third data structure includes the first value and position information of the fourth data structure associated with the first value as an entry, and is associated with the second value. Including the position information of the fifth data structure as an entry,
The fourth data structure includes, as an entry, position information of a record including the first value in the predetermined field, and does not include the first value itself as an entry.
3. The database management according to claim 2 , wherein the fifth data structure includes, as entries, the second value and position information of a record including the second value in the predetermined field. system. A first value that can be registered in a first field among a plurality of records is associated with first position information of a record that includes the first value in the first field, and the plurality of records A database management system for creating an index that associates a second value that can be registered in a second field with the second position information of a record that includes the second value in the second field. And
The first data structure and the second data structure so that the first value and the first position information are associated with each other by a combination of the first data structure and the second data structure. Indexing means for creating a body;
Storage means for storing the first data structure and the second data structure created by the index creation means as an index;
With
The first data structure includes the first value and position information of the second data structure associated with the first value as an entry,
The second data structure includes the second value and the second position information of a record including the second value in the second field as an entry, and includes the first value itself. A database management system that is not included as an entry. The index creation means includes the second data structure so that the second value and the second position information are associated with each other by a combination of the second data structure and the third data structure. And the third data structure,
The storage means stores the first data structure, the second data structure, and the third data structure created by the index creation means as an index,
The second data structure includes the second value and position information of the third data structure associated with the second value as an entry, and includes the first value as an entry. Without
The third data structure includes, as an entry, position information of a record including the second value in the second field, and does not include the second value itself as an entry. 5. The database management system according to 5 . The first value and the second value that can be registered in a predetermined field between a plurality of records are associated with the position information of the records that respectively include the first value and the second value in the predetermined field. A program for causing a computer to search for a desired record using an index stored in a storage means,
When the desired key indicates the first value, the index is constructed and associated with the first value by retrieving the desired key from the first data structure constituting the index. Obtaining the position information of the second data structure obtained;
When the desired key indicates the second value, the index is constructed and associated with the second value by retrieving the desired key from the first data structure. Obtaining the position information of the data structure of No. 3,
When the desired key indicates the first value, the position information of the record including the first value in the predetermined field from the second data structure associated with the acquired position information A process of obtaining
When the desired key indicates the second value, the second value is obtained by retrieving the second value from the third data structure associated with the acquired position information. Obtaining position information of a record included in the predetermined field;
Including
The second data structure includes, as an entry, position information of a record including the first value in the predetermined field, and does not include the first value itself as an entry.
The program according to claim 3, wherein the third data structure includes the second value and position information of a record including the second value in the predetermined field as an entry. Stored in storage means that associates NULL values and non-NULL values that can be registered in a predetermined field among a plurality of records, and position information of records that respectively include the NULL values and the non-NULL values in the predetermined fields. A program for causing a computer to search for a desired record using the index,
A first step of obtaining position information of a record including the NULL value in the predetermined field from a first data structure constituting the index when a desired key indicates a NULL value;
When the desired key indicates a non-NULL value, the position information of the record including the non-NULL value in the predetermined field is obtained by retrieving the non-NULL value from the second data structure constituting the index. A second step to acquire;
Including
The program according to claim 1, wherein the first data structure includes, as an entry, positional information of a record including the NULL value in the predetermined field, and does not include the NULL value itself as an entry. The second step constructs the index and sets the non-NULL value to the non-NULL value by retrieving the non-NULL value from a second data structure constituting the index when the desired key indicates a non-NULL value. The position information of the associated third data structure is obtained, and the position of the record including the non-NULL value in the predetermined field from the third data structure associated with the obtained position information. Including obtaining information,
9. The third data structure according to claim 8 , wherein the third data structure includes, as an entry, position information of a record including the non-NULL value in the predetermined field, and does not include the non-NULL value itself as an entry. program. The non-NULL value is classified into a first value and a second value,
In the second step, when the desired key indicates the first value, the first value is stored in the predetermined field from the third data structure associated with the acquired position information. When the position information of the included record is acquired, and the desired key indicates the second value, the index is configured and the fourth data structure associated with the acquired position information is used. Retrieving position information of a record including the second value in the predetermined field by searching for a second value;
Said third data structure, according to claim, characterized in that does not include the first value as and entries first value itself includes the entry position information of records that contain the predetermined field 8 The program described in. A first value that can be registered in a first field among a plurality of records is associated with first position information of a record that includes the first value in the first field, and the plurality of records An index stored in the storage means that associates the second value that can be registered in the second field with the second position information of the record that includes the second value in the second field. A program for using a computer to search for a desired record,
A second data structure constituting the index and associated with the first value by retrieving a first key indicating the first value from a first data structure constituting the index A first step of acquiring body position information;
A record including the second value in the predetermined field by searching a second key indicating the second value from the second data structure associated with the acquired position information. A second step of acquiring position information of
Including
The program according to claim 2, wherein the second data structure does not include the first value as an entry. The second step constructs the index and associates it with the second value by retrieving the second key from the second data structure associated with the acquired position information. Position information of the third data structure obtained, and the position of the record including the second value in the predetermined field from the third data structure associated with the obtained position information. Including obtaining information,
Said third data structure, according to claim, characterized in that does not contain the second value as and entries second value itself includes the entry position information of records that contain the predetermined field 11 The program described in.

Images