JPH06131392A - Database system - Google Patents

Abstract

PURPOSE: To accelerate the execution speed of entire collation by shortening the time of processing to each record by providing a means for rearranging the order of comparison for optimizing the execution of search and collation. CONSTITUTION: The data processing system is provided with a host computer 10 and a search processor 11 and the search processor 11 is connected through a bus 13 to a lot of disk driving units 12. Each disk driving unit 12 holds a relational data base composed of one file or more. The host computer 10 executes the main processing work load of a system composed of one application program 14 or more. The application program 14 designates the search to be executed by the data base and generates the search collation, each search collation is processed by a collation compiler 15, and series of searching instructions are generated. These searching instructions are applied to the search processor 11 and executed.

Description

Detailed Description of the Invention

[0001]

FIELD OF THE INVENTION The present invention relates to a database system. More particularly, it relates to methods and apparatus for processing queries in such systems.

[0002]

2. Description of the Related Art Generally, database systems store data in the form of records, each consisting of a number of fields. One way to access data in such a system is to search the database for records that match a particular search query. A search query may consist of a special logical combination of comparison operations that must be done for a particular field of each record, where one record has a logical combination of "true" values for that record. Will be searched only.

[0003]

A problem with such an arrangement is that if the query is extremely complex, the processing time for each record will be long, which makes the execution of the search query very slow. turn into. The purpose of the present invention is to alleviate this problem.

[0004]

According to the present invention, the database is a search query comprising means for storing a database of a series of records and a logical combination of comparisons that must be made for each record. In the database system including means for inquiring, the database system is provided with means for rearranging the order of the comparison in order to optimize the execution of the search query. By optimizing the search query, the processing time for each record is reduced, which speeds the overall execution speed of the query. Note that the search query optimization is done only once, for example as part of the query translation process before the search is actually performed. Therefore, when the search is performed, this optimization process does not cost execution time.

[0005]

One data processing system according to the present invention will now be described by way of example with reference to the accompanying drawings.

In FIG. 1, the data processing system includes a host computer 10 and a search processor 11.
The search processor is connected to a number of disk drive units 12 by a bus 13.

The disk drive unit 12 holds a relational database consisting of one or more files. Each file consists of a series of records, each consisting of one or more fields. Each field represents either a number or text, which may be fixed or variable in length. In the case of a fixed length field, the length of the field is the same in all records, but in the case of a variable length field it changes from record to record. The length of the variable length field can be calculated, for example, from the length code provided at the beginning of the field.

In operation, the host computer 10
It executes the main processing workload of the system, which consists of one or more application programs 14. The application program may be such as to specify a search to be performed on the database and generate a search query. Each search query is processed by query compiler 15 to generate a series of search instructions. These search instructions are search processors
Given to 11 and executed.

A search query consists of one combination of comparison operations to be performed on each record of a file. For example, the query is ((C1 AND C2) OR
It may be in the form of C3) AND (C4 OR C5). Here, C1--C5 represent individual comparison operations. Each operation may include comparing one particular field of the record with a predetermined search key, or may include comparing two different fields in the record.

Query Compiler Next, FIG. 2 illustrates query compiler 15 in more detail. The first step 20 of the query compiler 15 analyzes the query and creates a syntax tree that represents the logical structure of the query.

For example, inquiry ((C1 AND C2)
OR C3) AND (C4 OR C5) yields a tree structure as shown in FIG. This syntax tree consists of a set of nodes interconnected by branches. Each node represents either comparison (C1, C2, etc.) or logical operation (AND, OR). Each node is represented by the compiler as a data structure containing the following items: That is, "operator form (AND,
OR, comparison) "," number of branches (that is, subnode group) "," most left branch (from pointer to subnode) ", and" other branch (from pointer to subnode) ".

The next step 21 of the query compiler is to call the set cost subroutine. This cost setting subroutine assigns costs and probabilities for each node. The cost assigned to a node is an estimate of the expected cost (in processing time) that sets whether that node produces a true or false result. The probability assigned to a node is the probability of a true outcome.

As will be described in more detail later, the cost of a logical node (AND, OR) depends on the cost and probability of its branches and the order in which they are evaluated. The set cost subroutine rearranges the pointers in the syntax tree as needed and sorts the branches into the order that gives the lowest cost so that they are evaluated in this order. (It is assumed that the branches of one logical node are always evaluated starting from the leftmost branch). If a node has n branches, n! There is a street branch ordering, and the cost setting subroutine determines the optimal permutation.

The next step 22 of the query compiler is to call the code generation subroutine. This subroutine issues an instruction to retrieve a particular field or group of fields and perform a particular comparison. that is,
Also, jump instructions are inserted to link these comparisons together in a particular logical structure.

For example, the instructions generated to fill the syntax tree structure shown in FIG. 3 are in the form of Table 1 below.

[0016]

[Table 1] Since compiler routines for performing logical combination operations are known, it is unnecessary to describe the code generation subroutine in more detail.

Cost Setting Subroutine Next, FIG. 4 shows the cost setting subroutine in more detail. It is called first on the root node, then it recursively calls itself to scan the entire syntax tree and process each node in this tree.

40 shows the first function to test the type of node and branch according to whether it is a comparison node or a logical node.

Reference numeral 41 indicates that, in the case of a comparison node, a cost value and a probability value are assigned to that node. The cost of a comparison node is equal to the sum of the cost of accessing the field (fields) needed for the comparison and the cost of performing the comparison. The cost of accessing one field depends on the structure of the record. In particular, accessing a field preceded by one or more variable length fields requires sequential access to all these preceding fields to bring to the start of the desired field. , Generally takes longer. In general, the cost of accessing a field can be evaluated as a + nb, where a and b are constants and n is the number of preceding variable length fields. The cost of performing the comparison depends on the type of comparison. Simple comparisons such as "equal to", "greater than or equal to" or "less than or equal to" can be done quickly and are therefore assigned to lower cost values. Comparing more complex conditions such as "include" takes longer and is therefore assigned a relatively high cost value.

The probability value assigned to the comparison node is an estimate of the likelihood that the comparison will yield a "true" result. This assessment is based on the past experience of comparative outcomes. If there is no information about the previous outcome, then a predefined default value is used. Typical values are shown in Table 2 below.

[0021]

[Table 2]

42 indicates that if it is a logical node, the next step in the set cost routine is to make a recursive call to itself for each branch from that node. This results in each of the subnodes being processed by the set cost subroutine to assign costs and probabilities to these subnodes. Note that if any subnode is itself a logical node, this will be a more nested call to the set cost subroutine, and so on until the compare node is reached.

43 shows that once all branches of a logical node have been processed, the branches of this node are relocated to minimize the cost of this logical node and are assigned cost and probability values.

For a given logical node, the cost depends on the cost and probability of its branches and the order in which these branches are evaluated. For simplicity, consider the case where there are only two branches. Extension to three or more branches is obvious.

AND node AND node has cost c1. c2 and probability p1. Suppose we have two branches with p2 and p2. If branch 1 is evaluated first, the expected cost is c
1 + p1. It becomes c2. On the other hand, if branch 2 is evaluated first, the expected cost is c1 + p2. It becomes c1.

The cost setting routine determines which of these two possible evaluation orders gives the least cost, and swaps over pointers in the syntax tree structure, if necessary, to give the least cost. Make sure that the branch that you have is the leftmost branch and therefore is evaluated first. The cost assigned to that node is this minimum cost value.

Assuming that the comparison results are statistically independent, the probability assigned to the AND node is p1. p2.

OR node cost c1. c2 and probability p1. Consider an OR node with p2 and. If branch 1 is evaluated first, the expected cost is c1 + (1-p1) c2, while if branch 2 is evaluated first, the expected cost is c2 +.
(1-p2) c1.

As before, the cost-setting routine determines the order of evaluation that gives the lowest cost and swaps across pointers as needed so that the branch with the lowest cost is evaluated first. .

Assuming the comparisons are statistically independent, the probability assigned to an OR node is p1 + p2-p.
1. It becomes p2.

[0031]

According to the database system of the present invention described above, the search query is optimized, whereby the processing time for each record is shortened, and thus the overall execution speed of the query is accelerated.

[Brief description of drawings]

1 illustrates a data processing system embodying the present invention.

FIG. 2 shows a flowchart of a compiler for optimizing search queries.

FIG. 3 shows a syntax tree representing a search query.

FIG. 4 shows the compiler subroutine in more detail.

[Explanation of symbols]

 10 Host computer 11 Search processor 12 Disk drive unit 13 Bus 14 Application program 15 Compiler

Claims (7)

[Claims] 1. Means for querying the database with a search query comprising a means for storing a database containing a series of records and a logical combination of comparisons to be made for each record. ) And a means (1) for rearranging the order of the comparisons to optimize the execution of the search query.
A database system characterized by having 5). 2. The means for rearranging the order of the comparisons comprises: (a) indicating the time cost required to retrieve the data for the comparisons and to perform the comparisons; Means (42) for assigning the cost to each comparison associated with the query and (b) means for indicating the likelihood of the comparison producing a predetermined result and assigning the probability to each comparison ( 43) and (c) means (43) for finding the order of performing the comparisons that gives the least possible cost and rearranging the comparisons in that order. Database system. 3. The database system of claim 1, including a host computer for generating said search query, said means for querying said database including a dedicated search processor (11). 4. The compiler means (15) in the host computer for rearranging the order of the comparisons.
The database system according to claim 3, further comprising: 5. The database system of claim 1, wherein said means for storing said database includes a plurality of disk file units (12). 6. A method of compiling a search query comprising a logical combination of comparisons to produce an optimized sequence of search instructions, (a) for retrieving data for comparison and performing the comparison. Assigning the cost to each comparison associated with the search query, and (b) indicating the likelihood of the comparison giving rise to a predetermined result. And (c) finding the order in which the comparisons that give the least possible cost are made and rearranging the comparisons in that order. 7. The logical combination of comparisons comprises a tree structure, and the step of assigning a cost to each comparison is performed recursively from the root of the tree at each node of the tree structure. The method of claim 7.