JPH10269248A - Method for random extraction of data in data base processing system, and data base processing system based upon the same - Google Patents

Abstract

PROBLEM TO BE SOLVED: To improve the throughput of the random extracting processing in a data base processing system. SOLUTION: It is made possible to issue an inquiry including random extraction in an inquiry issuing process 2, and application order between the random extraction and other inquiries is changed in an inquiry converting process 8 in consideration of extraction units of the random extraction. Further, random access to a secondary storage device is reduced in a record managing process 4. Thus, the inquiry including the random extraction can be issued and inquiry conversion is performed in consideration of the extraction units to enable application to an inquiry including a totalizing process and further improve the efficiency of inquiries over a wide range. The random access to the secondary storage device is reduced to be able to improve the efficiency more.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a query processing method for a relational database, and more particularly to a random sampling processing method for efficiently executing a query including a random sampling process for a large-scale database.

[0002]

2. Description of the Related Art In recent years, with the spread of in-company information processing systems, various business data such as transaction information and customer information have been accumulated in a database, and the range of information available through the database has rapidly expanded. I am doing it. At this time, by analyzing the large-scale data stored in the database and extracting the features and regularities of the data,
There is an increasing demand for data mining processing aimed at expanding business opportunities and improving business efficiency. In general, in data mining processing, in order to analyze and extract features and regularities of large-scale data from various viewpoints, it is necessary to issue queries with different combinations of data items and condition settings and analyze the data repeatedly There is. However, as the size of data stored in the database increases, the time required for one inquiry process increases, and it is becoming difficult to efficiently extract data features and regularity.

[0003] As a technique for improving responsiveness of an inquiry process, a document "ACM SIGMOD International Conference"
on Management of Data (SIGMOD'96) "(published by ACM Press)
There is a data cube method disclosed in P.205-216. In this method, an expected query is processed before the query is accepted, and if a processed query is issued, only the processed result is returned without actually performing the query processing. It is. However, this approach requires a large amount of storage space to prepare query results in advance, and the range of queries that can be processed and handled in advance is limited.
For queries that do not provide query results in advance,
There is a disadvantage that a great deal of processing time is required. On the other hand, in the calculation of the feature amount and the extraction of the regularity from the large-scale data, it suffices if the tendency and the characteristic of the large-scale data can be obtained, and an accurate query result is not often required. Therefore, as a method of greatly reducing the query processing time, a random extraction process is introduced into the query process, and the feature amount and regularity are estimated from the randomly extracted data.
It is effective to reduce the amount of data to be processed and shorten the response time.

In the execution of a query including a random sampling process, not only does the data processing amount be reduced by random sampling, but also the execution efficiency of the query is not changed before execution of the query. It is important that the execution time can be significantly reduced by converting it to a query. That is, by applying the random extraction processing as early as possible in the inquiry processing to reduce the amount of data to be subjected to subsequent processing, the processing amount of the entire inquiry can be reduced. Generally, the logical structure of the data to be subjected to the inquiry processing is in the form of a table (20) as shown in FIG. The horizontal direction of this table is called a record (21), and the vertical direction is called a column (22). The same column of each record stores data in the same format. The set of records obtained after applying the database processing to the table which is the set of records becomes a table again. The database processing applied to the table refers to a condition evaluation processing, a projection processing, a joining processing, and a classification and aggregation processing for the table.

[0005] The contents of each process will be described below. First, the condition evaluation process in the database processing system specifies one or more condition evaluation columns and the conditions set for the values of those columns, and specifies the specified columns in the records included in the table to be processed This is a process of extracting a record that satisfies the set condition and reconstructing the table. The projection process in the database processing system is a process of designating one or more projection columns, extracting only the designated columns for each record included in the table to be processed, and configuring the table again. Next, the join processing in the database processing system is to designate one or more join columns commonly included in two tables to be processed and to include all records included in one table in the other table In this process, all records having the same value in the join column are joined with each other, and the table is formed again with the new records generated as a result. Further, the classification and aggregation processing in the database processing system is to specify one or more grouping columns and one or more aggregation target columns,
The records included in the table to be processed are classified into records where the value of the specified grouping column is the same as one group, and the statistics such as the total value or the average value of the values of the aggregation target column for each group Is calculated, and the result is output as one record.

In the present invention, a method of converting a query including a random extraction process by introducing a random extraction process defined as follows into a database will be described. The random extraction processing in the database processing system of the present invention
This is a process of randomly selecting records from a table that is a set of records and reconstructing them as a table. A collection of records extracted by one extraction operation in a random extraction process is called an extraction unit, and in one extraction process, the extraction probability of each extraction unit is guaranteed to be equal.

A query issued to a database as described above is configured by applying the various database processes described above in combination in an appropriate order to a table to be queried. Therefore, in query optimization including random extraction processing, the query is modified within a range that does not lose the random extractability of the random extraction processing, the random extraction processing is applied as early as possible in the query processing, and the subsequent processing is performed. It is important to shorten the processing time by reducing the data amount. For a conventional query conversion method including random sampling processing, refer to the document "International Conference On Ver.
y Large Data Bases (VLDB'86) "(Morgan Kaufmann Pub
lishers, Inc.) (pp. 160-169). This method changes the order of application of queries for queries including basic database processing such as random extraction processing, condition evaluation processing, projection processing, and join processing, while preserving the random extractability of random extraction processing. It discloses a query conversion process for the purpose.

[0008]

The first problem in the conventional method is that it cannot be applied to the case where the query conversion process includes a classification and aggregation process in the query for optimizing the random extraction process. As a query optimization method in data mining applications, it has only a limited effectiveness. The problem in optimizing the random extraction process in the query conversion process including the classification and aggregation process is that the extraction unit of the random extraction process is not properly handled. For example, when the sales details information of a product is classified for each customer, and the purchase pattern of each customer is to be examined, after simply performing random extraction at the sales details information level of the product, the purchase pattern for each customer is determined. However, since the complete purchase history of each customer cannot be obtained, the purchase pattern cannot be analyzed efficiently. This is because, in the purchase pattern analysis for each customer, the purchase history for each customer is extracted as an extraction unit, and where the purchase pattern analysis is to be performed, random extraction processing that ignores this is performed. Further, the second problem is that, in the record reading process, when a random extraction process is applied to a record stored in a storage device such as a magnetic disk device, the storage position of the record to be extracted is random, A random access to the storage device occurs, which results in an increase in the random sampling processing time. Further, the third problem is that, in the query issuing process, since there is no mechanism for specifying the query processing time and the accuracy of the query result, etc., a part of the data is read out by random sampling, and the query result desired by the user is estimated. Cannot be easily issued by the user.

A first object, which is a main object of the present invention, is to solve the above first problem, and to execute a query for efficiently executing a query including a random sampling process, to perform a query conversion in consideration of an extraction unit. An object of the present invention is to provide a query conversion processing method related to a random extraction process that can be applied to a query including a classification and aggregation process by performing query optimization as a process. A second object of the present invention is to provide an efficient record storage and reading method that does not generate random access to a storage device in random extraction of records from the storage device. A third object of the present invention is to provide a query issuing method capable of designating a query execution time or a query result accuracy.

[0010]

SUMMARY OF THE INVENTION The present invention relates to the first to third aspects.
In order to achieve the objective of (1), (1) to (3) below
Means. (1) Introduce the concept of random extraction columns for queries that include random extraction processing and convert queries that take into account the unit of extraction to convert queries that include classification and aggregation processing to more efficient queries Query conversion processing. Here, the random extraction column is one or more columns specified for a table to be extracted in order to specify an extraction unit in the random extraction process. In the random extraction process using a random extraction column, in one random extraction operation, values are randomly assigned to each random extraction column, and records with values assigned to each column are read from the table. All are extracted and the result is randomly selected. In the present invention, a query is converted so that a set of records having the same value in the random extraction column is treated as one extraction unit. Thus, for example, in the above example, a complete customer purchase history can be obtained by designating a customer number as a random extraction column and extracting records having the same customer number as a unit. However, in the following, in the random extraction process where the random extraction column SC is not specified, the random extraction process is performed on a record basis irrespective of the column value of the record, and the random extraction column SC will be NULL (empty set) In the random extraction process, it is defined that all items are extracted. In the random extraction processing according to the present invention, the sample grouping column SGC may be specified in addition to the random extraction column SC. When SGC is specified, the records in the table are divided into groups according to the value of the sample grouping column SGC, and a set of records with the same value of the random sampling column SC in each group is extracted as one extraction unit However, in the random extraction processing in which the sample grouping column is specified, it is defined that the extraction probabilities of the extraction units are guaranteed to be equal in each group.

(2) In storing and reading out a record to be stored in a record storage device of a record to be subjected to random extraction processing, a hash function is applied to a random extraction column of the record, and the storage and storage of the record are performed based on the hash value. A record storing process and a record reading process for reducing random access to the record storage device by performing reading are provided.

(3) At the time of issuing a query, the time required for query processing and the accuracy of the estimated value of the query result are specified, and the amount of records randomly extracted is adjusted according to the size of the database and the complexity of the query. And a query result evaluation process for realizing a query process having an arbitrary response time and accuracy.

Still another modification according to the present invention and a configuration for realizing the modification will be described in embodiments.

[0014]

FIG. 1 shows an embodiment of a database processing system according to the present invention. First, using FIG.
The configuration of the present embodiment will be described. The random extraction processing method according to the present embodiment includes a query issuing process 5 for generating a query sentence according to an input from the terminal device 1 and a query processing result display process 6 for displaying the query processing result and the evaluation result on the terminal device. In accordance with a query issuing process 2, an execution procedure generating process 7 for generating an executable intermediate code and a query result evaluation criterion from the query statement, a query conversion process 8 for converting the intermediate code into an intermediate code with higher execution efficiency, and an intermediate code A query execution management process 3 including a query process execution process 9 for performing a query process and a query process result evaluation process 10 for evaluating a query result according to the query evaluation criterion, a record reading process 11 for reading a record as data, and a record Record storage processing 1 for storing data in the record storage device 12
And a record management process 4 comprising: If the series of processes is programmed and recorded on a recording medium, the present invention can be used at any location.

The operation of this embodiment will be described below with reference to FIG. First, the inquiry issuing process 5 generates an inquiry sentence according to the input from the terminal device 1. The execution procedure generating process 7 refers to the query sentence generated by the query issuing process 5 and generates a query execution procedure and a query result evaluation criterion. Further, the query conversion process 8 converts the query execution procedure generated by the execution procedure generation process 7 into a query execution procedure with higher execution efficiency. Subsequently, the query execution process 9 issues a record read request to the record read process 11 according to the query execution procedure converted by the query conversion process 8 and processes the read record to generate a query result. The query execution process 9 continues to execute the query process until instructed by the query result evaluation process 10. The record reading process 11 reads the record from the query execution process 9, reads the record stored in the record storage area 12 by the record storage process 13 according to the request, and sends the read record to the query execution process 9. Hand over. Subsequently, the query result evaluation process 10 evaluates the query processing result generated by the query execution process 9 according to the query result evaluation criterion generated by the execution procedure generation process 7, and displays the query processing result and the evaluation result in a query result display process. 6 and determines whether or not the query execution process should be stopped, and if so, instructs the query execution process 9 to stop the query process. Finally, the query result display process 6 is the query process result evaluation process 10
Receives the query processing result and its evaluation result generated by
This is displayed on the terminal device 1.

In the following, the query issuing process, the query procedure generating process, the query converting process, the record reading process,
The details of the query process result evaluation process will be described using a specific query example. First, as a specific example, consider a database including three tables as shown in FIG. The customer table 31 has four fields of customer number, customer classification, name, and address.
Consists of three columns. At this time, the customer number is a key column of the customer table, has a unique value for each record of the table, and the value of this column uniquely determines a record in the table. The order table 32 has four columns: order number, customer number, priority, and order date. At this time, the order number is a key column of the order table, has a unique value for each record of the table, and the value of this column uniquely determines a record in the table. The customer number is a foreign key to the key column customer number of the customer table, and the value range of the customer number column of the customer table matches the value range of the customer number column of the order table. Commodity table 33 shows order number, product name, transportation means,
It consists of four columns of unit price. At this time, the order number is a foreign key corresponding to the key column order number in the order table, and the range of the order number column in the order table matches the range of the order number column in the product table. In the following, the customer number column of the customer table is represented as, for example, customer table.customer number.

The inquiry issuing process 5 in the embodiment is as follows.
This is a process of transferring a query sentence described in a database query language such as SQL to the execution procedure generation process 7. For example, the following query sentence described in the SQL format with respect to the database is considered as a specific example. 1: SELECT Customer classification, priority, transportation means, AVG (RANDOM (order amount)) 2: FROM SELECT order number, customer classification, priority, transportation means, SUM
(Product table.Unit price) AS order amount 3: FROM customer table, order table, product table 4: WHERE customer table.Customer number = Order table.Customer number 5: AND order table.Order number = Product table.Order number 6: GROUP BY order table.Order number, customer table.customer class, order table.priority, product table.transportation means 7: GROUP BY customer classification, priority, transportation means; Is not part of the query statement. At this time, the keyword RANDOM on the first line specifies that estimation by random sampling is performed to calculate the average value of the order amount. Also keywords
If RANDOM is specified immediately before the keyword SELECT,
Specifies that random sampling is used to extract records.

An example of the above-mentioned inquiry statement is as follows: customer table, customer number and order table, customer number value and order table, order number and product table, order number. Records with the same value are joined (lines 3-5), and records are grouped according to the values of the four columns: order table. Order number, customer table. Customer classification, order table. Priority, product table. (6th line), calculate the total of the product table and unit price for each group (2nd line), and divide the resulting record according to the values of the three columns of customer classification, priority, and means of transportation. Grouping (line 7), and finally, instructing to estimate the average value of the order amount for each group using random sampling (line 1).

FIG. 12 shows the relevant columns from each of the customer table, order table, and product table shown in FIG. 3, and shows the customer table, customer number and order table, customer number value, order table, and order. This shows the result of merging records with the same value of number and product table.order number. In this process, the joining column of the first joining process is {customer number}, and the joining column of the second joining process is {order number}.

FIG. 13 is an order table showing the results shown in FIG.
Records are grouped according to the values of the four columns: order number, customer table, customer category, order table, priority, product table, and transportation means, and the result of calculating the total of the product table and unit price for each group is shown. . In this processing, the grouping column is {order number, customer classification, priority, transportation means}, and the tallying column is {unit price}.

FIG. 14 groups the results shown in FIG. 13 according to the values of three columns of customer classification, priority, and means of transportation (the seventh line), and calculates the average value of the order amount for each group. The results are shown. In this process, the grouping column is {customer classification, priority, transportation means}, and the tallying target column is {order amount}.

The execution procedure generating process 7 in the present embodiment is a process in which the query sentence from the query issuing process 5 is
This is a process of converting into an intermediate code that can be interpreted and executed in the query execution process 9. Generally, what kind of intermediate code is used to convert a query sentence depends on the database processing system. Here, the database processing is converted into the following intermediate codes and handled. That is, C (CC, T) is the condition evaluation process using the condition evaluation column for the table T as CC, P (PC, T) is the projection process using the projection column for the table T as PC, and the join columns for the table S and table T. JC
Is defined as J (JC, S, T), the aggregation column for the table T is AG, and the aggregation process for the grouping column is GC is A (AG, GC,
T), and the random extraction column for table T is SC and the sample grouping column is SGC, and the random extraction process is S (SC,
SGC, T).

At this time, the result of converting the query example into an intermediate code is as follows. A (order amount, {customer class, priority, mode of transport}, S (unspecified, {customer class, priority, mode of transport}, A (product table. Unit price, {order number, customer class, priority, mode of transport, Means}, J ({order number}, product table, J ({customer number}, customer table, order table)))) At this time, the random sampling process is introduced in this query to estimate the classification and aggregation process results. Therefore, the random extraction column SC of the random extraction processing is unspecified, and the sample grouping column SGC is the grouping column {customer classification, priority, transportation means} of the classification and aggregation processing to be estimated. When the query results are extracted at random, the random extraction column SC of the random extraction process is not specified, and the sample grouping column SGC is {} (empty set). The result of the conversion into a code is a tree structure as shown in Fig. 4. In the execution of the query processing,
Processing is applied to the individual records to be processed in order from the processing specified as the leaf of the intermediate code to the processing specified as the root.

Further, in the execution procedure generation processing 7 in the present embodiment, an inquiry processing result evaluation criterion is generated from the query sentence issued from the query issuance processing 5 and passed to the query result evaluation processing 10. . For example, if a query processing time is specified at the time of issuing a query, the specified processing time is passed to the query result evaluation processing,
Alternatively, when the accuracy of the tally result is specified for the query including the tally process, the specified accuracy is transferred to the query result evaluation process. As an example of a method of specifying the query processing time at the time of issuing a query and the accuracy of the tally result, it is conceivable to add a keyword for specifying the time or the accuracy to the query statement. For example, SELECT customer category, SUM (RANDOM (unit price)) AS order amount FROM customer table, order table, product table WHERE customer table. Customer number = order table. Customer number AND order table. Order number = product table. Order number WITH The query statement IN 2 MINUTES; specifies that the total product price for each customer category be estimated within a range that can be determined within 2 minutes. Also, SELECT customer category, SUM (RANDOM (unit price)) AS order amount FROM customer table, order table, product table WHERE customer table. Customer number = order table. Customer number AND order table. Order number = product table. Order number WITH 0.99 PRECISION; query statement is 99% of the total unit price for each customer segment
Specify to estimate with accuracy of. At this time, in the above two query statements, when only the keyword of time specification or precision specification is specified, the insertion position of the random extraction processing is automatically determined in the query issuing processing, and the RANDOM keyword is complemented to make it possible for the terminal apparatus to execute the processing. It is also possible to reduce the trouble of issuing a query sentence.

In the query conversion process 8 in this embodiment,
The order of application of the processes is exchanged between the random extraction process introduced in the intermediate code generated by the execution procedure generation process 7 and the query process applied immediately before, thereby improving the execution efficiency. Convert to intermediate code. At this time, in the query conversion process, for a query including a random extraction process, a query is modified such that an extraction unit is stored using a random extraction column, thereby increasing the randomness of the random extraction process. Convert to a more efficient query while preserving it.

First, the state in which the query conversion is applied to the intermediate code of the above-described query example will be described below. As shown in FIG. 5, the value of the random extraction column of the random extraction processing inserted in the intermediate code is changed to the value of the grouping column {order number, customer classification, priority, Transportation means}, and change the application order of the random sampling process and the classification and aggregation process. As a result of this change, as shown in FIG. 15, a value is randomly assigned to each randomly extracted column from the table before the application of the classification and aggregation process-1, for example, {order number: order 1, customer classification: building, priority: high. , Transportation means: truck} are all extracted. At this time, since the grouping column is a unique column in the table after the classification and aggregation processing, the value of the grouping column and each record correspond one-to-one. Therefore, performing random extraction on a record-by-record basis for the table after the classification and aggregation processing is equivalent to performing random extraction by randomly specifying the values of the grouping columns before the classification and aggregation processing. Therefore, the randomness of the random extraction processing is not lost even by changing the application order of the processing as described above.

Next, as shown in FIG. 6, the random sampling column in the random sampling process is changed to the difference obtained by subtracting the sample grouping column from the random sampling column before the conversion, that is, {order number}. Apply random extraction processing. Due to this change, classification and aggregation processing-1 as shown in FIG.
A value is randomly assigned to the random extraction column from the table before application, and all records having, for example, {order number: order 1} are extracted. At this time, if the table before random sampling is grouped according to the values of the sample grouping columns {customer classification, priority, transportation means}, the values of the sample grouping columns are equal to each other, and Even if a value is assigned to each of the custom extraction columns {order number, customer classification, priority, transportation means} and records are extracted, the effective order for specifying records is {order number}
Only. Therefore, the randomness of the random extraction processing is not lost by the exchange of the application order of the processing as described above.

Further, in order to change the order of application of the next joining process-2 and the random sampling process, the random sampling process is distributed to each table before the joining as shown in FIG. With this change, all records having the value {order number: order 1} assigned to the random sampling column are extracted from the table before the application of the aggregation processing. At this time, a set of records obtained by extracting the records with the values specified in the random extraction column from the table after the join processing, and the records with the values specified in the random extraction columns from the rainy table The set of records obtained by extracting is one-to-one, so that the randomness of the random extraction process is not lost by exchanging the application order of the processes.

In the subsequent joining process-1, as shown in FIG. 8, the random extraction process is distributed to each table before the joining, and the application order is exchanged. With this change, all records having the value {order number: order 1} assigned to the random sampling column are extracted from the table before the application of the aggregation processing. This exchange of the application order is also possible for the same reason as described above. However, since the customer table does not include the {order number} as a column, the random extraction column of the random extraction process distributed to the customer table is NULL, and all items are extracted for the customer table. By the above query conversion, the intermediate code of the above query example is converted as shown in FIG.

In the following, a method of exchanging the processing order between the various inquiry processing and the random extraction processing will be described. The exchange between the random extraction process and the condition evaluation process may be performed simply by changing the application order of both processes. That is, S (SC, SG
C, C (CC, T)) ≡C (CC, S (SC, SGC, T)). At this time, ≡ indicates that the operations on both sides are equivalent to random sampling processing. In order for the operations on both sides to be equivalent to the random extraction processing, (i) the extraction unit is the same in the processing on both sides. (ii) Two points that the extraction probability of each extraction unit is preserved in the processing of both sides, or (i) The extraction unit matches in the processing of both sides. (ii) In the query after conversion, It is sufficient to show two points that the extraction units are extracted independently and with the same extraction probability. Therefore, it can be seen from the following two points that the above processes on both sides are equivalent. (i) Records extracted in one extraction process are shown in Table T, where sgc is the value of the sample grouping column SGC of the sample group into which the record is classified, and sc is the value specified in the random sampling column SC. Among the included records, SC
= sc and SGC = sgc and the records satisfying the conditions specified in the condition evaluation column CC, and the extraction units on both sides are equal. (ii) In the query before and after the conversion, the set of SC and SGC values and the extraction unit before and after the query conversion correspond one-to-one.
If the value of SC is randomly determined for the GC, the extraction probabilities of each extraction unit are preserved in the query transformation.

If the SC is not specified, random sampling is performed on a record-by-record basis. The records before and after the processing correspond one-to-one by the condition evaluation processing. The unit of extraction is the same in the query, and the extraction probabilities of the records in the table T that satisfy the conditions are stored before and after the query conversion. When SC = NULL, the operations on both sides match because the random extraction process extracts all items. The exchange between the random extraction processing and the projection processing may be performed simply by changing the application order of both. That is, S (SC, S
GC, P (PC, T)) ≡P (PC, S (SC, SGC, T)) holds. However, in order to be able to perform a random sampling operation on the left side, SC⊆PC is required.

At this time, it can be seen from the following two points that the above-mentioned processes on both sides are equivalent. (i) Records extracted in one extraction process include the value of the sample grouping column SGC of the sample group into which the records are classified as sgc, and the value specified in the random sampling column SC as sc in Table T. SC = s of records
The record satisfies c and SGC = sgc, and the extraction units on both sides are equal. (ii) There is a one-to-one correspondence between a set of SC and SGC values and an extraction unit, and if the SC value is randomly determined for SGC, the extraction probability of each extraction unit is preserved in query conversion. Have been.

When the SC is not specified, the record before the processing and the record after the processing have a one-to-one correspondence by the projection processing, the extraction unit is equal in the query before and after the conversion, and the extraction probability for each record is also stored. ing. If SC = NULL,
Since the random extraction process extracts all items, the operations on both sides match. The exchange between the random sampling process and the classification and aggregation process may be performed simply by changing the application order of both processes. That is, S (SC, S
GC, A (AC, GC, T)) ≡A (AC, GC, S (SC, SGC, T)) holds. At this time, the fact that the processing on both sides is equivalent is as follows.
I understand from the point. (i) The record extracted in one extraction process, the value of the sample grouping column SGC of the sample group in which the record was classified is sgc, the value specified in the random extraction column SC is sc, in the classification aggregation process A record satisfying SC = sc, SGC = sgc, and GC = gc among records included in Table T, where the value of the grouping column GC of the classified group is gc, and the extraction units on both sides are equal. (ii) SC and SGC and GC value sets and extraction units are one-to-one
If the value of SC is randomly determined with respect to SGC, the extraction probability of each extraction unit is preserved in the query transformation.

When the SC is not specified, a query is converted by specifying the grouping column of the classification and aggregation process in the SC. If there is no grouping process, the result of the classification and totaling process is one record, so that all items are extracted without performing the random extraction process. When SC = NULL, the operations on both sides match because the random extraction process extracts all items. The exchange between the random extraction process and the binding process may be performed by limiting the random extraction column SC and the sample grouping column SGC to the columns included in the respective tables, and exchanging the application order of both. That is, S (SC, S
GC, J (JC, S, T)) ≡J (JC, S (SC / S, SGC / S, S), S (SC / T, SGC / T,
T)) holds.

The fact that the processes on both sides are equivalent is as follows.
I understand from the point. (i) The record extracted in one extraction process, the value of the sample grouping column SGC of the sample group into which the record was classified is sgc, the value specified in the random extraction column SC is sc, and the right processing The records extracted by
Records satisfying SC / S = sc / S and SGC / S = sgc / S in Table S, and SC / T = sc / T and SGC / T = sgc / T in Table T It is also extracted during processing. Records that are not extracted in the right process do not satisfy any of the above conditions, and therefore are not extracted in the left process. Therefore, the extraction units on both sides are equal. (ii) There is a one-to-one correspondence between a set of SC and SGC values and an extraction unit, and if the SC value is randomly determined for SGC,
In the query conversion, the extraction probability of each extraction unit is stored.

If no SC is specified, the key column of J (JC, S, T) is set to KC, and S (no specification, SGC, J (JC, S, T)) ≡S (KC, S
GC, J (JC, S, T)). If there is no key column in J (JC, S, T), distribution is performed by weighting according to the attribute value of the join. That is, the appearance ratio of the attribute value xi of the attribute X in the table T is | T.xi |, and the extraction probability of the record having the attribute value xi from the table S is | T.xi | / | Tx | max. I do. S (unspecified, SGC, J (JC, S, T)) ≡ S (unspecified, SGC, J (J
C, Select (| T.xi | / | Tx | max, S (unspecified, S)), T)), and the extraction unit does not change. The extraction operation of each extraction unit is independent and the extraction probability is 1 / | S | * | T.xi | / | Tx | max * 1 / | T.xi | = 1 / | S | | Tx | max equal to each other. When SC = NULL, the operations on both sides match because the random extraction process extracts all items. In addition, if the sample grouping column SGC is included in the random extraction column SC for random extraction processing (SC⊇SGC), the difference between the random extraction column before conversion and the sample grouping column Can be replaced. For example, as shown in FIG. 17, if SC = {X, Y}, SGC = {Y},
The unit of extraction in this random extraction process is a record in which the values of the columns X and Y are equal.
After random grouping on the values of columns X and Y after grouping on the values of, and first on the values of column X obtained by subtracting the random sampling column SGC from the random sampling column SC from the table records After performing extraction, column Y
Does not change even if the extraction unit and the extraction probability are equal in each group. That is, S (SC, SGC, T) ≡S (SC-SGC, SGC, T) holds.

The fact that the processing on both sides is equivalent is as follows.
I understand from the point. (i) In the processing on the left side, assuming that one of the records extracted in one extraction processing is r, the value of the random extraction column SC of r is sc, and the sample grouping column SGC is sgc, SGC = sgc And SC-SGC = sc-sgc holds, so SC
The record r is also extracted in the processing on the right side by the assignment of the same value of SGC and SGC. Conversely, in the processing on the left side, one of the records extracted in one extraction processing is defined as r, and the value of the random extraction column SC-SGC of r is defined as s.
c-sgc, sample grouping column SGC is sgc, S
Since GC = sgc and SC = sc hold, the record r is also extracted in the processing on the left side by assigning the same value to SC and SGC. As described above, since the same record is extracted for the assignment of the same value of SC and SGC, the extraction unit is equal. (ii) There is a one-to-one correspondence between a set of SC and SGC values and an extraction unit, and if the SC value is randomly determined for SGC,
In the query conversion, the extraction probability of each extraction unit is stored.

Record reading process 1 in this embodiment
1 reads a record stored in the record storage device 12 in accordance with a record read request issued by the inquiry execution process 9. FIG. 11 shows how records are stored in this embodiment. In storing a record in the record storage device 12 according to the present embodiment, a column of one or more records of the record is specified in advance as a division column BC using a record storage process 13, and a hash is applied to the specified division column. The function 111 is applied, and records are classified and stored in groups called buckets 112 according to their values. The hash function 111 used for hash division is described in the document “PRINCIPLES OF DATA
BASE AND KNOWLEDGE-BASE SYSTEMS ”, by JDUllman Co
By using a divided hash function or the like disclosed in pp. 358-360 issued by mputer Science Press, it becomes possible to read a record in which a hash value is specified for each divided column. However, when there is no designation of the division column, all records are stored in one bucket. For each bucket, a block 113 which is a continuous area in the record storage device is allocated as necessary, and records are stored in those blocks. Therefore, in reading records having the same hash value, random access to the record storage device does not occur.

In reading records from the record storage device 12 in the present embodiment, the following four methods are selectively used depending on the contents of the database processing immediately after the record reading processing. Method 1: Immediate database processing other than random extraction processing or random extraction column of random extraction processing immediately after is NU
In the case of LL, normal read processing is performed. That is, all records included in all buckets are read. Method 2: If the random extraction column of the random extraction process immediately after is not specified, the random extraction process is performed in record units. That is, for one read request, one record to be stored in the record storage area 12 is randomly determined, and the record is read. Method 3: If there is no common part between the random extraction column SC of the immediately subsequent random extraction process and the record division column BC,
Performs record reading processing using a hash function.
That is, as shown in FIG. 10, at the time of reading a record, a hash value is randomly designated for each of the random extraction columns, and a hash function 101 is assigned to the random extraction column SC of the record stored in the record storage device.
Is applied to extract only records with the specified hash value. This record reading method is used even when the division column is not specified. Method 4: If there is a common part between the random extraction column of the random extraction process immediately after and the record division column, a record read process using the bucket-divided record is performed. That is, as shown in FIG. 11, at the time of record reading, a hash value is randomly specified for each of the random extraction columns, and the random extraction column SC and the storage division column BC of the record stored in the record storage device 12 are stored. Regarding the common part SC @ BC, the record stored in the block 113 of the bucket 112 having the specified hash value is read, and the difference SC-BC between the random extraction column SC and the storage division column BC of the read record is read. , A hash function 114 is applied to extract only records having a specified hash value.

The record storage device 12 in this embodiment
Stores records organized in a table format. When records are divided into buckets and stored, the records assigned to the respective buckets are assigned and stored in blocks, which are continuous areas on the record storage device, according to the hash value of the record's divided column, so that record access is sequential. It becomes an access, and the bucket reading efficiency can be improved.

Inquiry result evaluation processing 10 in this embodiment
Then, based on the query evaluation criterion generated by the execution procedure generation process 7, the query result in the query execution process 9 is evaluated, and execution control of the query process is performed. When a time is specified in the query evaluation criterion generated by the execution procedure generation process 7, the query execution evaluation process compares the query execution time with the specified time each time a query result is received from the query execution process, and executes the query execution. If the time has exceeded the specified time, it instructs the query execution process to stop the query process. When accuracy is specified in the query evaluation criterion generated by the execution procedure generation processing 7, the query processing evaluation processing calculates the accuracy of the estimated value of the query result every time a query result is received from the query execution processing, and calculates the estimated value. If the precision exceeds the specified precision, the query execution processing is instructed to stop the query processing, and the estimated value and precision at that time are returned.

At this time, the accuracy of the estimated value is determined according to the literature “Dictionary of Statistics” by Kei Takeuchi, published by Toyo Keizai Shinposha, pp. 243-247, 252-254.
Can be calculated by the estimation method relating to the random sampling method and the village extraction method disclosed in the above. Further, in the above embodiment, the database processing system can be configured using an inquiry processing method having only an inquiry issuing process or an inquiry conversion process independently.

[0043]

According to the query conversion process of the present invention, in the exchange of the application order between the random extraction process and another query process, the query is converted in consideration of the extraction unit of the random extraction process. Compared to a conventional query conversion process that does not consider the extraction unit, the present invention can be applied to a query including a totaling process, and the efficiency of a wider range of queries can be improved. Also, at the time of query issuance, the response time is specified by specifying the time required for query processing and the accuracy of the estimated value of the query result, and adjusting the amount of records randomly extracted according to the size of the database and the complexity of the query. And a query result with high accuracy can be easily obtained. In addition, when performing random sampling in bucket units from the table and performing aggregation processing, if the number of records per bucket to be aggregated is sufficiently large, the accuracy calculation of the aggregation result is calculated using the simple random extraction processing. By approximating by a calculation formula, the accuracy calculation of the aggregation result can be approximated by the accuracy calculation formula in the case of a simple random sampling process without requiring new statistics associated with record division.

[Brief description of the drawings]

FIG. 1 is a schematic diagram of an embodiment of a database processing system according to the present invention.

FIG. 2 is an explanatory diagram related to the configuration of a table.

FIG. 3 is an explanatory diagram of an example of a database.

FIG. 4 is an explanatory diagram of an intermediate code of a query example before conversion.

FIG. 5: Random sampling process and classification and aggregation process in query example
FIG. 4 is an explanatory diagram of application order exchange of No. 1;

FIG. 6 is an explanatory diagram of random extraction column conversion in random extraction processing in a query example.

FIG. 7 is an explanatory diagram of an application order exchange of a random extraction process and a joining process-2 in a query example.

FIG. 8 is an explanatory diagram of an application order exchange of a random extraction process and a join process-1 in a query example.

FIG. 9 is an explanatory diagram of a converted intermediate code of a query example.

FIG. 10 is an explanatory diagram of processing for reading a record from a record storage device.

FIG. 11 is an explanatory diagram of processing for storing a record in a record storage device.

FIG. 12 is an explanatory diagram of an example of a join processing result in a query example.

FIG. 13 is an explanatory diagram of a processing result of totaling processing-1 in an example of a query;

FIG. 14 is an explanatory diagram of a processing result of a tallying process-2 in an example of a query;

FIG. 15 is an explanatory diagram of records extracted by query conversion between random extraction processing and classification and aggregation processing-1.

FIG. 16 is an explanatory diagram of a record extracted by conversion of a random extraction column in a random extraction process.

FIG. 17 is an explanatory diagram of a method of converting a random extraction column in a random extraction process.

[Explanation of symbols]

 DESCRIPTION OF SYMBOLS 1 Terminal device 2 Query issue process 3 Query execution management process 4 Record management process 5 Query issue process 6 Query result display process 7 Execution procedure generation process 8 Query conversion process 9 Query execution process 10 Query result evaluation process 11 Record read process 12 Record storage Device 13 Record storage processing 20 Table 21 Record 22 Column 31 Customer table 32 Order table 33 Product table

 ──────────────────────────────────────────────────続 き Continued on the front page (72) Inventor Yori Takahashi 5030 Totsuka-cho, Totsuka-ku, Yokohama-shi, Kanagawa Prefecture Inside Software Development Division, Hitachi, Ltd. (72) Inventor Tadashi Nishizawa 1-280, Higashi-Koigabo, Kokubunji-shi, Tokyo Central Research Laboratory, Hitachi, Ltd.

Claims (16)

[Claims] 1. A random extraction method in a database processing system for extracting desired data from a database, comprising: (1) a query issue management process for issuing a query to the database; and (2) a query issue management process for issuing the query. It has a query execution management process for performing execution management, and (3) a data management process for storing data in the database and managing the stored data. The query execution management process in (2) includes (2- 1) a process of inserting a random extraction process into the query issued in the query issuance process; and (2-2) a more efficient data extraction of the query while preserving the unit of extraction in the inserted random extraction process. And a query conversion process for converting the query into a query that can be performed in the database processing system. 2. The query processing executed in the query execution management processing of the above (2) comprises: (A) a query issued in the above (1) for a table in which records as data to be processed are tabulated. The records of the table are divided into groups according to the values of one or more grouping columns specified in the above, and the values of one or more aggregation columns specified in the query also issued in the above (1) are respectively After applying the classification and aggregation process that aggregates for each group of
(B) A value is randomly specified for each of one or a plurality of random extraction columns specified in the query, and a random extraction process of extracting all records having the specified values from the aggregation result is applied. In the query conversion process (2-2), the process (B) is applied to the record of the table, and then the process (A) is applied. 2. The method according to claim 1, further comprising a query conversion process for exchanging the application order of A) and (B). 3. In the query conversion process of (2-2), when the process (B) is a random extraction process in a record unit that does not specify a random extraction column, the query conversion process is performed as the random extraction column. 3. The random extraction processing method in the database processing system according to claim 2, wherein the method is executed after conversion into a query using a grouping column in the classification and aggregation processing of the processing (A). 4. The query processing executed in the query execution management processing of the above (2) comprises: (A) a query issued in the above (1) for a table in which records as data to be processed are tabulated. The records in the table are divided into groups according to the values of one or more grouping columns specified in (b), and (B) one or more random extractions also specified in the query issued in (1) above After randomly specifying a value for each column in each column of each column and applying a random extraction process of extracting all records having the specified value from the grouping process result,
(C) applying a tabulation process on the values of one or more columns designated in the query similarly issued in (1) to the random sampling result for each group. If included, the above (2-2)
The query conversion process of (A), (B), (C)
(A1) First, for the table to be processed, the grouping column specified by the query is specified as the sample grouping column, and the records of the table are divided into groups according to the value of the sample grouping column. After randomly applying values to each of the random extraction columns for each group and applying a random extraction process for extracting all records having the specified values, (C1) the random extraction process 2. The random extraction processing method in the database processing system according to claim 1, further comprising a step of changing to a step of applying the classification and aggregation processing in the query before conversion to the result of (1). 5. The query processing executed in the query execution management processing of the above (2) is one of the two tables specified in the query issued in the above (1) for the two tables to be processed. Alternatively, after combining records of respective tables having the same value in a plurality of join columns and applying a join process as one record, (B) the one specified in the query issued in the above (1) is also applied. A case where a value is randomly specified in each of one or a plurality of random extraction columns, and a random extraction process of extracting all records having the specified value from the join result is applied. , The query conversion process of (2-2) is performed by the process (A),
(B) is applied to each of the tables to be processed in (A2), and the random extraction process in which the random extraction columns are limited to columns commonly included in each of the tables is applied. 2. The method according to claim 1, further comprising the step of: applying a join process for a join column specified by a query before conversion to an extraction result from the table. 6. The query processing executed in the query execution management processing of (2) is one or more of: (A) one or more specified in the query issued in (1) for the table to be processed. After the records in the table are divided into groups according to the values of the sample grouping columns, each of the one or more random sampling columns specified in the query issued in (1) above for each group. (B) after applying a random extraction process of randomly specifying values in the columns and extracting all records having the specified values, (B) the one specified in the query issued in (1) above. Alternatively, a table record is divided into groups according to the values of a plurality of grouping columns, and a classification / aggregation process is performed in which aggregation is performed on the values of one or a plurality of aggregation columns designated similarly for each group. In the case where the query conversion process of (2-2) is included, the query conversion process of (2-2) includes the process (A),
(B1) is applied to (A1) a random extraction process using a difference obtained by subtracting a sample grouping column from a random extraction column for a table to be processed as a new random extraction column. 2. The random extraction processing method in the database processing system according to claim 1, further comprising a process of changing the process to the process of B). 7. The query processing executed in the query execution management processing of (2) is performed by: (A) one or a plurality of queries specified in the query issued in (1) for the table to be processed; (B) one or more random extraction columns specified in the query issued in (1) above, after applying the condition evaluation processing of extracting a table record according to the value of the condition evaluation column Apply a random extraction process that randomly specifies a value for each column of, and extracts all records with the specified value from the result of the conditional evaluation process.
(2-2), the query conversion process of (2-2) includes the processes (A) and (B) of (A1) one or a plurality of After applying a random extraction process using a random extraction column,
(B1) A process is provided for changing the extraction result to a condition evaluation process using one or a plurality of condition evaluation columns specified in the query similarly issued in (1). A random extraction processing method in the database processing system described in the above. 8. The query processing executed in the query execution management processing of (2) is performed by: (A) one or more of the queries specified in the query issued in (1) for the table to be processed; After applying the projection process that extracts the projection column of
(B) Similarly, a value is randomly specified for each of one or a plurality of randomly extracted columns specified in the query issued in (1), and the record having the specified value is subjected to the projection processing. Applying the random extraction process of extracting all from the result, the process (2-2)
The query conversion process of (A) converts the processes (A) and (B) into (A
1) After applying the random extraction process using the random extraction column specified in the query to the table to be processed, (B)
2. The random extraction processing method in the database processing system according to claim 1, further comprising a step of: 1) applying a projection process using a projection column designated by a query to the extraction result. 9. A random extraction processing method in a database processing system for extracting desired data from a database, comprising: (1) a query issue management process for issuing a query to the database; and (2) a query issue management process for issuing the query. A query execution management process for performing execution management; and (3) a data management process for storing data in the database and managing the stored data.
The query issue management process of (1-1) is a query issue process of generating a query sentence according to an input from the terminal device;
(1-2) a query result display process for displaying a query result and an evaluation result with respect to the query result on the terminal device, wherein the query execution management process of (2) comprises (2-1)
An execution method generating process for generating a query execution procedure in which random extraction processing is inserted and a query result evaluation criterion for evaluating a query result by random extraction from a query sentence issued by the query issuing process of (1-1); (2-2) The query execution procedure generated by the execution method generation process of (2-1) can be further efficiently extracted from the query while preserving the extraction unit in the inserted random extraction process. And (2-3) execute a query in accordance with the query execution procedure converted in the query conversion process of (2-2), and perform the data management process of (3). (2-4) Evaluating the result of the query execution process of (2-3) according to the query result evaluation criterion generated by (2-1). Passing the query results and evaluation results for the query issuing process (1), wherein in response to the query result and the evaluation result (2-
3) a query result evaluation process for controlling the query execution process; and (3) the data management process includes (3-1) a data storage process for storing data in a database, and (3) a data storage process for storing data in a database.
-2) A random extraction method in a database processing system, comprising: a data read process for reading data in accordance with a data read request issued by the query execution process of (2-3). 10. A random extraction processing method in a database processing system for extracting desired data from a database, comprising: (1) a query issue management process for issuing a query to the database; and (2) a query issue management process for issuing the query. A query execution management process for performing execution management; and (3) a data management process for storing data in the database and managing the data. The data management process of (3) includes the query of (2). Regarding the query processing executed in the execution management processing, a value is randomly specified in each of one or a plurality of random extraction columns specified in the query issued in (1) above, and the specified value is specified. Is included in the table where the records as data are included in the table, A database process characterized by applying a hash function to a random extraction column when extracting records from the system, and extracting the result of extracting all records having hash values determined at random in advance as a random extraction result Random sampling method in the system. 11. The data management process of (3) divides a record of a table into buckets, which are sets of mutually exclusive records, according to a hash value of a random extraction column specified by a query. When a bucket is allocated to one or more blocks as a continuous area on an external record storage device and stored, and records are extracted from a table in which records as data are tabulated, all records included in the bucket are extracted. 11. The random extraction processing method in the database processing system according to claim 10, wherein the reading of the records included in the bucket is performed by sequential access to the record storage device by using one random extraction unit. 12. A random extraction processing method in a database processing system for extracting desired data from a database, comprising: (1) a query issue management process for issuing a query to the database; and (2) a query issue management process for issuing the query. A query execution management process for performing execution management; and (3) a data management process for storing data in the database and managing the stored data.
In the query issuance management process, a query statement designating introduction of a random extraction process at an appropriate position in the query process is issued. 13. The inquiry issuance management process of (1),
In addition to the specification regarding the introduction of the random extraction process into the query process, the time required for the query process is specified, and the query execution management process of (2) is performed in accordance with the specified query execution time. 13. The random extraction processing method in the database processing system according to claim 12, wherein adjusting the amount guarantees that the query processing is completed within the time specified by the query. 14. The inquiry issue management process (1)
In addition to the query issuance process that specifies that a random sampling process is to be introduced, the accuracy of the estimated value of the aggregation process result is specified, and the query execution management process (2) is performed according to the accuracy of the specified estimated value. 13. The random extraction processing method in the database processing system according to claim 12, wherein by adjusting the number of randomly extracted records, an estimated value of a total processing result having an accuracy designated at the time of issuing the query is returned. 15. A database processing system for extracting desired data from a database according to input information from a terminal device, comprising: (1) a query issuance management unit for issuing an inquiry to the database; and (2) an issued query. Query execution management means for performing execution management of
(3) data management means for storing data in the database and managing the stored data;
The query issuing management means of (1) comprises: (1-1) a query issuing means for generating a query sentence according to an input from a terminal device; and (1-2) a query result and an evaluation result with respect to the query result. The query execution management means of (2) includes:
-1) A query execution procedure in which random extraction processing is inserted and a query result evaluation criterion for evaluating a query result by random extraction are generated from the query sentence issued by the query issuing means of (1-1). Execution method generating means;
(2-2) The query execution procedure generated by the execution method generation means of (2-1) is used to extract the query with higher efficiency while preserving the unit of extraction in the inserted random sampling process. And (2-3) executing a query in accordance with the query execution procedure converted by the query conversion means of (2-2), and performing a query on the data management means of (3). Query execution means for issuing a data read request; and (2-4) evaluating the result of the query execution process of (2-3) according to the query result evaluation criterion generated in (2-1). And (2-3) query result evaluation processing means for passing the query result and the evaluation result to the query issuing means and controlling the query execution means of (2-3) according to the query result and the evaluation result. Data management means) is,
(3-1) data storage means for storing data in a database; (3-2) data reading means for reading data from the database in accordance with the data read request issued by the query execution means of (2-3); A database processing system comprising: 16. A recording medium on which a program for executing the method according to claim 9 is recorded.