JPH0452495B2 - - Google Patents

Description

DETAILED DESCRIPTION OF THE INVENTION [Field of Application of the Invention] The present invention relates to a method for processing queries to a database, and more particularly to a method for processing queries to a database suitable for efficiently processing a plurality of queries to a database.

[Background of the invention]

In the following, we will particularly explain the conventional method of query processing in relational database systems (hereinafter abbreviated as RDBS) and discuss its problems.

In RDBS, based on a data processing request (query) in a highly data-independent, non-procedural database query language, the system itself determines the optimal (minimum based on some performance metric, e.g. I/O count, CPU time) for that processing request. It is necessary to generate processing procedures for data access instructions (meaning giving costs). This is called query optimization processing, and the pros and cons of this optimization processing method will affect the performance of the system.

In query optimization processing, the evaluation order of queries is determined by considering the characteristics of the query, the characteristics of the data, the characteristics of the database, the characteristics of the system, and the various data processing mechanisms stored in the system. Determining the evaluation order of internal conditional expressions Appropriate selection of the various equipped data processing mechanisms (selection of evaluation method for conditional expressions) Appropriate selection of access paths (indexes, links, etc.) prepared for data access However, conventionally, the above
has been mainly studied, and has hardly been studied.

In other words, multiple queries are to be processed.
In RDBS, queries are serially processed one by one in the order they are entered into the system, and are executed without considering the relationships between queries. This means that the database data that has been loaded from the database into the buffer in main storage, as well as the intermediate results obtained from query processing, are not being used effectively.・Issuing a data loading request. If related query processing requests are continuous, the effect of database data load buffering will be effective, but if they are not continuous, database data will need to be loaded each time, and processing efficiency will not be improved. I can't hope.

Many methods for processing queries to databases have been proposed so far, and are particularly discussed in the following documents.

●Procedures of ACM－SLGMOD
International Conference on Management
o Data ●Procedings of the International Conference
on Very Large Data Bases ●acm, Transaction on Database Systems ●Information Processing Society of Japan National Conference Lecture Collection [Object of the Invention] The purpose of the present invention is to solve the problems of the prior art as described above, and to cluster related queries against a database. The object of the present invention is to provide a query processing method that achieves efficient use and management of database buffers by scheduling database processing based on this.

[Summary of the Invention] In order to achieve the above object, the present invention provides a method for processing queries to a database.
In a database processing system that processes a relational database that responds to group queries, transaction clustering processing means clusters the transactions for each transaction that accesses the same kind of data; A transaction cluster that schedules transaction clusters to be
A cluster execution schedule formulating means, a transaction cluster intra-cluster analysis processing means for analyzing a transaction cluster based on the schedule determined by the transaction cluster execution schedule formulating means, and a transaction cluster intra-cluster analysis processing means. a transaction cluster creation/management unit having means for converting the source command of the transaction to match the intra-cluster transaction with the transaction cluster execution schedule through the means and determining the execution schedule of the intra-cluster transaction; A feature of this method is that by performing clustering and scheduling at the source command level above the transaction, the database buffer is used efficiently and the processing efficiency of queries to the database is improved.

[Embodiments of the invention]

Hereinafter, one embodiment of the present invention will be described based on the drawings.

FIG. 1 is a diagram for explaining the conceptual overall structure of a database query processing method according to an embodiment of the present invention.

The database processing system 2 includes a system control unit 4 that realizes overall control of the system based on database processing requests input from the terminal group 1.
and a transaction cluster creation/management unit 5 that clusters related processing requests for multiple queued database processing requests.
A command cluster creation/cluster creation/clustering unit analyzes the transaction cluster generated by the transaction cluster creation/management unit 5 and clusters the commands that make up the transaction (meaning the database processing request described above). Management unit 6 and the command cluster creation/
a command optimization processing section 7 that analyzes each command cluster generated by the management section 6 and performs conventional query optimization processing on the commands forming the cluster; and the optimization processing section. 7
The code generation/execution section 8 generates code and controls execution based on the processing results.

Database 3 has multiple relations R ₁ , R ₂ ,
It is composed of... Relation R ₁ , R ₂ ,
. . . data is described in a two-dimensional table format, and each row is called a row or tuple.
A tuple consists of several attributes (called columns). In comparison to the past, relation
R ₁ , R ₂ , . . . correspond to files, tuples to records, and columns to fields. A database containing multiple relations R ₁ , R ₂ , . . . is called a relational database.

Figure 2 shows the transaction in Figure 1.
FIG. 3 is a diagram for explaining the cluster creation/management unit 5 in more detail.

The following processing is performed within the transaction cluster creation/management unit 5. The transaction analysis processing means 9 analyzes the transaction transferred from the system control unit 4, and determines the relationship between the transaction and the relationships R ₁ , R ₂ , . . . that constitute the transaction management table 9a and the database. A transaction/relation relation description table 9b to be held and a transaction/single relation column relation description table 9c to hold the relation between transactions and columns of a single relation are generated. The transaction clustering processing means 10 performs clustering of transactions using the information in the tables 9a, 9b, and 9c obtained by the transaction analysis processing means 9, and clusters the transactions using the cluster identifier (this will be described later) and the relevant information. A transaction cluster management table 10a is created that is represented by a list of identifiers of transactions that constitute a cluster. The transaction cluster execution schedule formulating means 11 schedules the execution of the transaction cluster obtained by the transaction clustering processing means 10, and creates a transaction cluster execution schedule table (ordered list of cluster identifiers) 11a. Create. The intra-transaction cluster analysis processing means 12 analyzes the transaction clusters obtained by the above-mentioned means and controls the execution of transactions within the cluster. 13 is a means for converting the source processing request of the transaction within the transaction cluster to match the intra-cluster transaction with the transaction cluster execution schedule, and formulating the execution schedule of the transaction within the transaction cluster. . This means 13 creates a table 13a representing the conversion result of the source processing request and the execution schedule of transactions within the transaction cluster.

Transaction management table 9a and transaction-relation relationship description table 9 generated by transaction analysis processing means 9
b. The structure of the transaction/single relation column relationship description table 9c is shown in FIGS. 3, 4, and 5, respectively.

Tr# in the transaction management table 9a shown in FIG. 3 is a transaction identification number assigned to each transaction in the system. Corresponding to this number, the database processing contents of the transaction are held in a source image, that is, a query language source command.

As shown in FIG. 4, the transaction-relation relationship description table 9b contains the transaction access type (search system) for each transaction Tr#1, Tr#2, Tr#3... :R, update system: U, etc.), relation R ₁ ,
Based on the related information with R ₂ , R ₃ . Count information COUNT1, which indicates the total number of set “1”, respectively.
COUNT2 is retained. In the diagram, 3 of U ₃ is
Transaction Tr #3 is an update processing request,
This indicates that relation R ₃ is the specific target of the update.

As shown in FIG. 5, the transaction/single relation column relationship description table 9c includes the access type (R: search type, U: update type) for each transaction to a specific relation, and the relevant relay. Column C ₁ composing the column,
Count information COUNT1 and COUNT2 are held based on the relationship (1: related, 0: not related) information with C ₂ , C ₃ , . . . and the related matrix information. In the diagram, 2 of U ₂ is the transaction
Tr#3 is an update processing request, and column _C2 indicates the specific update target.

Incidentally, the command cluster creation/management section 6 shown in FIGS. 1 and 2 is also realized with the same internal configuration as the transaction cluster creation/management section 5 described above. Therefore, although detailed explanations and diagrams are omitted, the command cluster creation/management unit 6 includes a command analysis processing means, a command management table created by the means, and a command cluster creation/management unit 6.
an inter-relation relation description table, a command/single relation relation description table, a command/clustering processing means, and a command/cluster management table created by the means;
means for formulating a command/cluster execution schedule;
and a cluster execution schedule created by the means, an analysis processing means in the command cluster, a means for converting the source of the intra-cluster command, a means for formulating an execution schedule for the intra-cluster command, and a source conversion result of the command created by the means. It consists of various means and information management such as command execution schedule and command execution schedule.

Next, the operation of each of the processing sections described above will be explained in detail using FIG. 6.

Transaction Tr input from terminal group 1
(database processing request) is the system control unit 4
The transaction is queued in a transaction processing queue (not shown) (601). The transaction cluster creation/management unit 5 acquires transaction Tr one by one from the processing queue (602), and calculates the number of acquired transactions.
While counting Ntr (603), the acquired transaction Tr is analyzed using the transaction analysis processing means 9 (604). At this time, as mentioned above, the transaction management table 9
Related information such as a, a transaction/relation relation description table 9b, and a transaction/single relation column relation description table 9c are created.

Next, using this information, the transaction clustering processing means 10 performs transaction clustering processing (605). In clustering, the access type of transactions and the order of input into the database processing system are considered. The operation of transaction clustering will be explained using FIG. Now, assume that the cluster identifier is CLi (i=1, 2, 3, . . . ).
At this time, when transaction Tr#1 is input analysis, CL ₁ = (Tr#1). Then transaction Tr
After #2 input analysis, transaction Tr #1
Since there are no relations R ₁ , R ₂ , . . . common to transaction Tr #2, a new cluster CL ₂ is created, and CL ₂ =(Tr #2). Next, when transaction Tr#3 is input,
Transaction Tr #3 is transaction Tr
Transaction Tr is placed in cluster CL ₁ because it requires relations (R ₁ , R ₅ ) common to #1.
#3 is incorporated. That is, CL ₁ =(Tr#1,
Tr#3). Similarly, transaction Tr
After inputting #5, CL ₁ = (Tr#1, Tr#3, Tr
#5), two clusters of CL ₂ =(Tr#2, Tr#4) are generated.

Next, when inputting transaction Tr#6,
Since the access type of transaction Tr#6 is different from the previous search access R and is update access U, this is managed as one cluster, and CL ₃ =(Tr#6). Furthermore, when inputting transaction Tr#7, the transaction
After confirming the unrelatedness with Tr#6, clusters CL ₁ and CL ₂
Verify compatibility with From the figure, CL ₂ = (Tr#2,
Tr#4, Tr#7). When transactions Tr#8 to Tr#10 are similarly verified, transactions Tr#8 and Tr#10 are related to relation _R1 . However, transaction Tr#6
Since the contents of relation R ₁ have been updated by the access type U ₁ of , it can be seen that it is necessary to set up a new cluster that includes the updated relation R ₁ . That is, transaction Tr#8,
Transaction Tr#10 constitutes cluster _CL4 , and _CL4 =(Tr#8, Tr#10). It can be seen that transaction Tr#9 is included in cluster CL ₂ {CL ₂ = (Tr#2, Tr#4, Tr#7, Tr
#9)}.

The above processing is performed by the transaction clustering processing means 10.
Processing time of transaction targeted for processing 0
Check Te (To is the initial setting value, Te represents the elapsed time from queuing the transaction to the processing queue 602 to clustering processing 605 of the transaction) and the number Ntr (No is the initial setting value) ( 606, 607).
Appropriate settings of To and No enable efficient system services. A possible method for determining the processing schedule for each cluster CLi is to give priority to the input order of the transactions that make up the cluster CLi. In the example in Figure 7, CL ₁ , CL ₂ , CL ₃ ,
Clusters are processed in order of CL ₄ (608). Next, clusters are acquired one by one in the order of cluster processing (609), and the source command conversion of transactions within the cluster and the processing scheduling of transactions are set according to the system environment settings.
This is done using the information of COUNT1 and 2 (61
0). In this way, set COUNT1 and 2 to each cluster
It is used to determine the processing order of transactions within the CLi, and processing scheduling for intra-cluster transactions is determined in a manner that reduces the number of fetches of data from the database (610).
For example, the processing order of transactions in cluster CL ₁ in FIG. 7 is transaction Tr#3, Tr
Processing is performed in the order of #1 and Tr #5. The above is the processing content of the transaction cluster creation/management section 5.

Next, create the above transaction cluster/
Based on the information set in the management unit 5, an internal analysis is performed for each transaction in the created cluster (611, 612), and clustering of the command group making up the transaction and clustering of the command cluster are performed. The processing order is set (612, 613). Furthermore, internal analysis of the commands within each command cluster is performed, the processing order of commands within the cluster is set, and source commands are converted accordingly (614, 615). In the above processing, the transaction shown in Figure 5
Single relation column relationship description table 9c
Use the information of The processing content is the same as that of the transaction cluster creation/management unit 5 described above, so a description thereof will be omitted.

FIG. 8 shows a specific example of the processing contents of the transaction cluster creation/management section 5 or the command cluster creation/management section 6.

801 to 806 are query processing languages
SEQUEL (Structured English Query)
This is the source command of a transaction (or command) written in the same language. Taking transaction Tr #1801 as an example, the meaning is that from the column (name and fare) in the relation named resort, fare is
This means choosing one with a (fee) of 5 or less.
Further, the numbers of transaction identifiers Tr#1 to Tr6 indicate that each transaction is input in the numerical order. Each input transaction is clustered based on the relation (or column) to be accessed, and the order of cluster processing is determined. In this example, cluster CL ₁ 807 includes transactions Tr#1,
Tr#3 and Tr#5 enter, transactions Tr#2 and Tr#4 enter cluster CL ₂ 808, and transaction Tr#3 enters cluster CL ₃ 809. Then, the cluster processing order is cluster CL ₁ →
The cluster is determined as CL ₃ . Cluster CL ₁ 8
As shown in Figure 8b, the source command conversion in the processing within 07 converts interval data (Temp Tr#18
01, Tr#1'801'. This data is loaded from the database 3 shown in FIG. 1 into the database buffer 14 in the database processing system 2 during clustering. Therefore, in order to access this, it is necessary to convert the source command. ), and processing requests target small amounts of data.
That is, by converting transaction Tr#3803 into transaction Tr#3'803', the intermediate data temp Tr#1'80
1' can be used.

After the processing of the command cluster creation/management unit 6 as described above is completed, for each command of the command cluster obtained by the processing unit, the number of input/output times of database data from the secondary storage (database 3), Or required for data processing.
Conventional optimization processing for generating an optimal access path for command processing is performed in the command optimization processing unit 7 by evaluating the command processing cost based on CPU time, etc. (616, 617). Next, it sequentially checks for completion of command cluster processing, completion of command cluster processing, completion of transaction cluster processing, and completion of transaction cluster, and executes the processing if it is not completed. (618, 619, 620, 621),
Finally, the code generation/execution unit 8 generates a code (622) and executes database processing (62).
3). In execution, as described above, by scheduling database processing at a higher level, the number of fetches of database data in secondary storage is reduced. That is, in addition to effectively using the data in the database buffer 14, by converting source commands and using intermediate data in the database buffer 14 without directly accessing the database data in secondary storage, a small amount of data can be saved. This is realized as a processing request targeting the data of , and aims to improve performance.

〔Effect of the invention〕

As described above, according to the present invention, by clustering related queries to a database and scheduling database processing based on the clustering, it is possible to efficiently utilize and manage the database buffer, An improvement in processing efficiency is realized.

[Brief explanation of the drawing]

FIG. 1 is a conceptual overall configuration diagram showing an embodiment of the present invention, FIG. 2 is a diagram for explaining a transaction cluster creation/management unit according to an embodiment of the present invention, and FIGS. 4, 5, and 7 are diagrams for explaining information tables created/used by the transaction cluster creation/management section, and FIG. 6 shows a processing flow according to an embodiment of the present invention. FIG. 8 is a diagram showing a specific example of processing by the transaction cluster creation/management section. 5: Transaction cluster creation/management unit, 9: Transaction analysis processing means, 10:
Transaction clustering processing means, 1
1: Transaction/cluster execution schedule formulation processing means, 12: Transaction/cluster intra-cluster analysis processing means, 13: Intra-cluster transaction source command conversion, intra-cluster transaction execution schedule formulation processing means.

Claims (1)

[Claims] 1 In a database processing system that processes database processing requests (hereinafter referred to as queries or transactions) from terminals to a relational database, transaction clustering clusters multiple transactions into transactions that access the same type of data. a processing means 10; a transaction cluster execution schedule formulation means 11 for scheduling transaction clusters generated by the transaction clustering processing means;
a transaction intra-cluster analysis processing means 12 that performs clustering of command groups and scheduling of command clusters for each transaction in each transaction cluster according to a schedule determined by a cluster execution schedule formulating means; means 13 for analyzing the command cluster generated by the transaction intra-cluster analysis processing means according to the transaction cluster execution schedule, converting the source command and scheduling the execution of the intra-cluster transaction; 1. A database query processing device comprising a transaction cluster creation/management unit comprising: