JPS61184635A - Retrieving system for data base system - Google Patents

Abstract

PURPOSE:To minimize the access to a data substance and to shorten the processing time by storing the information concerning the total cases of the data of the index data in the plural B tree constructions and selecting the index having a small number of the data applicable before retrieving by using the information. CONSTITUTION:The B tree construction of the data base system is composed of one route page 11 and many stages of the index page 12 and a leaf page 13 located at the lowest level of the index. To the total cases 111 of the data of the route page 11, the information concerning the total cases of the data in the index of the tree construction is stored. The total cases 111 of the data of the page are accessed before retrieving the data and the information of the number of the cases of the data is used. The index having the minimum number of the data applicable to the designated conditions is selected, the access to the data substance is minimized by using the index and the processing time is shortened.

Description

DETAILED DESCRIPTION OF THE INVENTION [Field of Application of the Invention] The present invention relates to a search method for a database system, and particularly to a data search method that can shorten search time by accessing an index.

[Background of the invention]

In database systems, various studies are being conducted to reduce the number of accesses to data entities and speed up processing.

Among these, regarding the use of indexes in databases for efficient operation and the use of B-trees for the index structure, for example,
Fundamentals of database systems, ``7.67.6 Index''
(Author: Shunsuke Uemura, Ohmsha, August 30, 1980, 1st edition, PP207-PP219) and “Algorithmist Data Structure = PASCAL Program, 4.5 Multiply Tree” (Author, With, 2 Translated by Takuya Katayama, 2 Japan Computer Association, 1977.7.15. 3rd edition, PP277-PP30
1). In other words, when adding an index to a database, the former involves creating a table that converts the attribute (data item) that is the target of the index, that is, the key value, into the address where the record is stored, and creating a table that converts several records. They should be grouped together as a block, and the largest key value in that block should be associated with its block number. Furthermore, the index should have a multi-stage index structure, and the index should have a block overflow area and a shared overflow area. 5 etc. to be provided are described.

Also, for the latter, for a given n, every page contains nodes from n to 2n, so if the tree consists of N entries and the maximum page size is 20 nodes, then at worst it is necessary. The number of page accesses is -QOglIN, the characteristic of B-tree is that each page contains at most 2n items (keys), and excluding the root page,
Each page must contain at least n items, each page must be a leaf page and have no descendants, or m+
It is stated that there is one descendant (m is the number of keys), that all leaf pages appear at the same level, etc. However, with conventional search methods, when multiple indexes are used as database access routes, it is impossible to quantitatively determine which index to select. That is, in the conventional thinking, an index is a means for efficiently operating a database, and no consideration was given to using the index as a means for determining the filtering effect of the index.

[Purpose of the invention]

The purpose of the present invention is to solve such problems and to make it possible to determine the effect of applying conditions to indexes, that is, which index is most effective to use, and to solve the problem when there are multiple indexes available. The object of the present invention is to provide a search method for a database system that can minimize access to data entities.

[Summary of the invention]

In order to achieve the above object, the search method of the database system of the present invention is a relational database system equipped with data and an index using a plurality of B-tree structures for the data. Store information about the number of data and access the index before data search.7. Using the information on the number of data above, select the index with the smallest number of data that meets the specified conditions, and use this index to search the data. It is characterized by searching.

[Embodiments of the invention]

Hereinafter, embodiments of the present invention will be described in detail with reference to one drawing.

FIG. 1 is a structural diagram of an index showing one embodiment of the present invention.

The structure of FIG. 1 itself is shown according to the B tree structure. A B-tree index has one root page 11 and multiple index pages 12 in multiple stages (in FIG. 1, each stage has two pages).

and leaf page 13 located at the lowest level of the index.
It consists of

In the present invention, an index based on a B-tree structure requires fewer accesses to identify a data entity with a certain index value, and the index has information regarding the data entity. By adding information 111 regarding the number of data items to the index, the effect of the application conditions when using the index (in other words, the main search Before this, it is possible to determine which index is most effective when used (hereinafter referred to as filtering effect).

This allows more effective use of the index.

FIGS. 2(a), (b), and (c) are structural diagrams of the root page 11, index page 12, and leaf page 13 shown in FIG. 1, respectively.

In the root page 11 of Figure 2 (,), the total number of data items is 1.
11. Index entry 112. and a pointer (hereinafter abbreviated as maximum key pointer) 113 to an index page of an index entry group larger than the key value indicated by the index entry 112. Note that 115 is an unused area and has no particular relation to the index structure. The total number of data items 111 is a characteristic element of the present invention, and the total number of data items in the index can be known from this information. The index entry 112 is
Its structure includes a key value 116 for a certain item, a pointer 117 to a data entity having that key value, a pointer 114 to an index page storing a group of index entries below that key value, and characteristic elements of the present invention. The number of data items 118 is included. Number of data items 11
8 indicates the number of data items that are less than or equal to the key value.A determination method 1 that enables the determination of the filtering effect according to the present invention from the 1 information and the total number of data items 111 will be described later with reference to FIG. Next, in the structure of the index page 12 in FIG. 2(b), one index page 1
2 stores up to n index entries. The value of n may be any value that satisfies the B tree structure. Index entries 121, 122, and 124 in index page 12 are identical in structure to the index entries in root page 11, and have pointers 125, 114 . Key values 126 and 116, pointer 1
27 and 117. The data numbers 12 and 118 correspond to each other. Also, the fact that the index page 12 has one maximum key pointer 123 is the same as the maximum key pointer 113 in the root page 11. FIG. 2(c) shows the structure of the leaf page 13, which includes the above-mentioned index page 12 and root page 1.
It is structurally the same as 1. The difference between the leaf page 13 and the index page 12 is that a pointer 135 (this corresponds to pointers 114, 125) to the lower order in the index entry of the leaf page 13 (this corresponds to pointers 114, 125) and a maximum key pointer 134 (this corresponds to pointers 113, 123) ) is always vacant. In this way, the structure of 7=m arguments shown in FIG. 1 and FIG.
), it matches B-tree.

The index structure of the present invention has the original characteristics of B-tree as they are. Further, the addition of the characteristic elements of the present invention does not significantly change the original method of growing and extinguishing a B-tree. However, the search for index entries within index page 12 and leaf page 13 differs in that only sequential search can be used and no other searches can be used.

FIG. 3 is a flowchart for acquiring the number of data items for filtering effect determination.

When a key value to be compared (a constant, abbreviated as X below) and a comparison condition (equal sign, inequality sign, etc.) are given for a certain index item (data item), below that key value, A schematic process for acquiring the number of less than and equal data items will be explained with reference to FIG.

The variables used in Figure 3 are shown in Figures 2 (a) to (e).
It matches the symbol used in

First, access the root page, and the total number of data items is 111.
At the same time, the area 118 for counting the number of data items is cleared (block 21). Next, X and the key value of the index entry in the root page (root
- Compare keyl12's kayl16) block 2
2), when X is small, the pointer (ro) shown in FIG.

ptrlL4) of t-keyLL2 is set as a search pointer to enable access to the primary index page (block 23). Also, if X is large, the maximum key pointer in the root page (max
ptr 113) as the search pointer. In this case, data with a value smaller than
Since it exists after ptr114 of eyl'12, the number of data items (ro in Figure 2 (a)
ot - countllB of key 112) is added (Protect 24). Next, the search continues and the number of data entries continues to be added (block 25) until an index entry with a key equal to X or with a key value greater than entry number i
= O (block 251), compares the key value 126 or 136 with X, and if X is greater or equal, proceeds to block 26 for determining the comparison condition;
When X is smaller (block 252), coun
t is the number of data items (root-key 121, 131 c
ount128.138) and set the entry number to 1.
and returns to block 252 (
Block 254). If X is not greater than, equal to, or less than the key values 126 and 136, pointers 125 and 135 (ptr in the entry are set as search pointers (block 255)).

When the search pointer=nuQn, the process advances to the comparison condition block; otherwise, the process returns to block 251 (block 256).

In this way, the search is performed sequentially from the left to the right index entry of each page. When the search for (The value of coun is.

(Because the number also includes the item X), in the size judgment,
If the equal sign is not included, 1 is subtracted from the Count value to obtain the count (block 27).

Also, if the size judgment includes an equal sign, no correction is applied,
Finish the process. If the comparison condition is 1 = “or ≠”, count items equal to X (block 28
1 to 283), that is, if the number of the item equal to X is j, start the comparison from j = 0, and then
26, 136) is equal to X, the item number j and entry number i are incremented by 1, and then it is checked again whether they are equal to X (block 28).

When the series of processing is completed, the number of data items whose count is equal to or less than the X value is determined. Comparison conditions are 6g <#y, 1g
The number of data retrieved when ≧” and “=” is C
This is the out value itself. Comparison condition is # > 31
When ゜°°≧'' and '≠'', the total number of data items sum minus the Count value becomes the number of data items to be retrieved. This method makes it possible to determine which index is more effective to use when there are multiple indexes available.

Up to this point, the structure of the index according to the present invention and the method for determining the filtering effect of the index have been described.

Next, how the present invention is applied to a database system will be described.

FIG. 4 is an example of one resicon in a relational database system.

Now, assuming that FIG. 4 is a relation 1, the relationship between the structure of this relation 1 and its index is shown. In this relation l, one data item consists of four items: item 1 (31) consisting of numerical data, item 2 (32) consisting of character data, and item 3 (33) consisting of character data. , item 4 (34) consists of numerical data. Item 1 is.

index 35, item 2 to index 36, item 4 to index 3
7 each. The data value of each item is
It is described in the figure.

Now, if a search request is given to search for item 3 in each data regarding glue controller 1 in Fig. 4 (however, the search conditions are as follows...Item 10)
and item 2>11ΔΔa b c,” and item 4>10
0. Note that "" indicates a character string and Δ indicates a blank space. In character string comparison, it is assumed that the blank space is smaller than other characters.

Since all items for which one condition is specified as a database search method have indexes, data searches are performed using any one of the indexes (multiple indexes cannot be used at the same time). By adding the filtering effect determination process described above, it is possible to determine which index is advantageous to use.

In other words, according to the filtering effect determination method of the present invention (method explained in FIG. 3), when using the index for item 1, 5 items of data will be 1. When using the index for item 2, 10 items will be It can be seen that if the index for item 4 is used, 18 pieces of data will be retrieved. Therefore, for this search request, it can be determined that a data search using the index for item 1 is optimal. In the above example, the maximum number of data items is 18 at most, and there is no big difference which index is used. However, in an actual database, it is not uncommon for the number of data items to be tens of thousands or more. is extremely effective.

〔Effect of the invention〕

As explained above, according to the present invention, in a data search, when there are multiple available indexes, it is determined which of them is most effective to use before the main search. Since it can be determined by accessing 1
With an index access time that is much shorter than that of a regular search, it is possible to identify an index with the shortest actual search time. In addition, according to the present invention, the present invention can be realized simply by adding information regarding the number of data items on the B-tree, and even in that case.

With the added information, the original characteristics of B-tree are not lost,
Since it does not significantly change the way the tree grows or disappears, there is no need to significantly increase the index maintenance processing time.

[Brief explanation of the drawing]

Fig. 1 is a structural diagram of an index showing an embodiment of the present invention, Fig. 2 is a structural diagram of each page in Fig. 1, and Fig. 3 is a processing flowchart for acquiring the number of data items for determining filtering effectiveness according to the present invention. , FIG. 4 is a structural diagram showing an example of a beam laser controller. 11: Root page, 12: Index page, 13
: Leaf page, 111: Total number of data items. 112.121, 122, 124, 131, 132.1
33: Index entry, 114, 125.135
: Index/leaf page pointer, 116, 12
6,136: Item key value, 117.127,137: Data entity pointer, 118.128.138: Number of data items below key value, 113.123,134: Maximum key pointer, 115: Unused area, 31, 32, 33, 34
:Item, 35, 36.37: Index of each item. Figure 2 (a) Structure of root page (b) Structure of index page (C) Structure of leaf page

Claims (2)

[Claims] (1) In a relational database system equipped with data and an index using a plurality of B-tree structures for the data, information about the total number of data items in the index is stored in the B-tree structure, and the index is used before data search. A search method for a database system, characterized in that the index is accessed, the information on the number of data items is used to select an index with the smallest number of data that meets specified conditions, and data is searched using the index. (2) To select the index with the smallest number of data that meets the specified conditions above, the top page of the B-tree is selected, so after obtaining the data count information from that page, value) and the key value of the index entry within the page, and then in the lower page, until an index entry with a key value equal to or larger than the data item of the specified condition appears,
A search method for a database system according to claim 1, characterized in that the number of data items is added by searching, and after the search is completed, the added value of the number of data items is corrected based on a comparison condition.