JPH05342277A - Enquiry processing method and data base processor - Google Patents

Abstract

PURPOSE:To enable high speed enquiry processing by detecting conditional expressions for which evaluation is not required by discrimination from a value substituted at the time of the execution of the enquiry when variables are included in the enquiry. CONSTITUTION:Before an application program (AP) 10 is executed, a part relating to the enquiry corresponding to a screen 50 is compiled beforehand by a data base management system (DMBS 20) and an execution form is generated and preserved. The DMBS 20 is constituted of the enquiry processing for realizing the analysis processing (220) of the enquiry performed before the execution of the enquiry, optimization (221) and execution form conversion (222) and enquiry execution processing for substituting a constant for the variable, selecting processing sequence and then realizing the execution of enquiry code interpretation. Then, by setting indeterminate, infinitesimal and infinity to a column for which the value is not substituted for the variable in an enquiry sentence, the authenticity of the pertinent conditional expression is decided and unrequired conditional expression evaluation is unnecessitated.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to database processing, and more particularly to a query processing method and a database processing apparatus suitable for query processing suitable for a relational database management system.

[0002]

2. Description of the Related Art A database management system (hereinafter DBM
(Abbreviated as S), in particular, a relational DBMS processes a query expressed in a non-procedural language to determine and execute an internal processing procedure. SQ as this database language
L is used (Database Language
SQL ISO 9075: 1989). The main method of conventional query processing is to determine a single internal processing procedure based on a preset rule (for example, Smit
h, J. M. , Et, al. "Optimizing
the Performance of a Rela
regional Database Interface
e "CACM Vol. 18, No. 10, Oct.
1975. ) And a plurality of candidate processing procedures selected by using various statistical information, a method that determines an optimum one by cost evaluation (for example, Selinger,
P. G. , Et. al. "Access Path Selection in
a Relational Database Ma
management System Proc. ACM
-SIGMOD, 1979. ) There is. The former has a small load for creating a processing procedure, but has a problem in the validity of a uniformly set rule, and also has a problem in the optimality of the selected internal processing procedure. Although the latter has a load for managing various statistical information, creating a plurality of candidate processing procedures and evaluating their costs, it provides an optimal processing procedure. Further, as a combination technique of the above two, for example, Sat
oh, K. , Et. al. "Local and Gl
oval Optimization Mechani
sms for Relational Database
se ", Proc. VLDB, 1985.

Further, in many DBMSs, the inquiry process is realized through a two-phase process of an inquiry analysis process and an inquiry execution process. Host language (COBOL, PL / I
When the inquiry language is incorporated in (1), etc., the inquiry is analyzed in advance of the execution of this application program, and one internal processing procedure which is an execution format is created. In this query expression, a host language variable is often described in the search condition expression. The constant is assigned to this variable at the time of execution of the internal processing procedure of the result of the query analysis processing, that is, at the time of query execution. A problem in this case is that a plurality of optimum processing procedures can be considered according to the values assigned to the variables. In order to solve this problem, there is a method in which a plurality of processing procedures are created at the time of preprocessing and the processing procedure is selected according to the value assigned to the variable at the time of query execution (Japanese Patent Laid-Open No. 1-194028,
Graefe, G.M. , Et. al. "Dynamic Query Evaluation
Plans ”, Proc. ACM-SIGMO
D, 1989. ).

Here, the database is assumed to consist of the relations that can be seen from the user in a two-dimensional table format, and the row corresponding to the row of the table is a row. is there. A row is made up of one or more attributes (which are called "columns").

[0005]

The above-mentioned prior art is the most efficient processing procedure based on various statistical information of the database system from the query input by the user. Is automatically determined by the DBMS. Furthermore, when a variable is embedded in the selection condition expression of the query, multiple processing procedures are expanded at the time of query analysis, and the processing procedure is selected according to the value assigned to this variable at the time of query execution. The optimum processing procedure is selected.

The application program executed by the user sets a value corresponding to the variable input to the inquiry, executes the inquiry sentence, and outputs the search result. In the information search, the number of rows in the search result is judged, and several query condition expressions are sequentially added, or the search range by the condition expressions is narrowed so as to further narrow down. According to the conventional technique, in this processing form, a method of dynamically generating an inquiry corresponding to a value set by the user is conceivable. This method requires a query analysis process every time a user executes a query, resulting in a large overhead. In addition, the method of creating in advance a query statement in which a column in which a user sets a value is a query conditional expression has a problem that the number of combinations of corresponding queries becomes enormous.

Therefore, an inquiry is made in which a column considered to be given by the user is used as a conditional expression. Even if the user does not assign values to all variables when executing a query,
A method is effective in which only conditional expressions that are desired to be evaluated are subject to conditional evaluation processing. An object of the present invention is to realize a high-speed query processing by detecting a conditional expression that does not need to be evaluated and evaluated from a value substituted at the time of query execution when the query includes variables.

[0008]

In order to achieve the above-mentioned object, a query input by a user is analyzed as a query processing method in a system that analyzes a query from a user to a database to determine a corresponding processing procedure and executes the procedure. And analyze the query analysis processing step of creating a processing procedure from the analysis result, and a conditional expression that does not require conditional evaluation by judging from the value assigned to the query and the query conditional expression of the processing procedure, A query execution process for realizing a query process by evaluating a conditional expression excluding the detected conditional expression is provided.

Further, the inquiry analysis processing step includes a step of selecting and storing a single or a plurality of internal processing steps from the analysis result and the statistical information of the database, and the inquiry execution step is the selection, A process for selecting and executing a processing procedure consisting only of conditional expressions that require conditional expression evaluation from the stored internal processing procedures is provided.

The value assigned to the inquiry is assumed to be infinitesimal, infinite, or undecided.

Further, in order to achieve the above object, at least a processing device, a main storage device, and an external storage device storing a database are provided, and an inquiry from a user to the database is analyzed to determine a corresponding process. Then, as a database processing device for executing it, the processing device analyzes the input query and creates a processing procedure from the analysis result, a query analysis processing means, a value substituted into the query and the processing procedure. And a query execution processing unit that realizes a query process by detecting a conditional expression that does not require conditional evaluation by judging from the query conditional expression and evaluating the conditional expression excluding the detected conditional expression, Further, the inquiry analysis processing means selects and stores a single or a plurality of internal processing procedures from the analysis result and the statistical information of the database. The query execution processing means is configured to select a processing procedure consisting only of conditional expressions requiring conditional expression evaluation from the selected and stored internal processing procedures, and execute the selected processing procedure. And the value assigned to the query is infinitesimal,
It is assumed to be infinite and undecided.

[0012]

According to the query processing method of the present invention, by setting undetermined, infinitesimal, or infinity to a column that does not assign a value to a variable included in a query statement, the true / false of the corresponding conditional expression can be determined. I can decide. In addition, by selecting a pre-created processing procedure that consists only of conditional expressions that need to be evaluated, it is not necessary to execute query analysis processing and dynamically create the execution format of the processing procedure. Inquiry processing becomes possible.

[0013]

Embodiments of the present invention will now be described in detail with reference to the drawings. FIG. 1 is a schematic explanatory diagram of a database processing to which the present invention is applied. A preferred embodiment of the present invention will be described later. FIG. 2 shows an implementation example of the software configuration in the database processing apparatus. The software configuration includes an application program (hereinafter abbreviated as AP) 10 and 11 created by a user and a database management system for managing the entire database system such as inquiry processing and resource management ( DBMS20),
In the database processing, the data that is the input / output processing target
An operating system (hereinafter, operating system is abbreviated as OS) 30 for reading and writing the data and managing the entire computer system, and data to be processed by the database are stored. And a database 40.

The DBMS 20 generates a system control unit 21 that manages input / output and the like in addition to management and control of the entire DBMS, a query analysis 220 that performs query syntax analysis and semantic analysis, and an appropriate processing procedure. Optimization process 22
1, a code generation 222 for generating a code corresponding to a processing procedure, a logic processing unit 22 including a code interpretation execution unit 223 for executing interpretation of this code, and the DBMS 20.
A data base buffer 24 for storing the data to be processed in the above, a data access processing 230 for realizing the condition judgment, editing, record addition, etc. of the accessed data,
A database buffer control 231 for controlling reading / writing of a database record, a mapping process 232 for managing a storage position of data to be input / output, and an exclusive control of a resource shared by the system are realized. A physical processing unit 2 for executing the physical processing of the data base including the exclusive control 233.
3 and 3.

FIG. 3 shows an example of the hardware configuration of the database processing apparatus to which the present invention is applied.
Specifically, FIG. 3 is an application example in a processing environment in which a host computer operates. FIG. 3 shows a central processing unit (CPU) 60 that holds a main memory unit (MM) 61, a channel unit (CHU) 70 that controls input / output, and an external storage unit 81 (a disk in the embodiment) that stores a database. apparatus
(DK)) and an external storage controller 80 (a disk controller (DKC) for controlling a disk in the embodiment).

FIG. 1 is a schematic explanatory diagram of database processing to which the present invention is applied. Further, FIG. 4 is a schematic explanatory view of the conventional database processing. In this example, a search request for the database is shown. FIG. 4 shows items that the user wants to use as search conditions on the screen 50 corresponding to the AP 10 (the purchase date is 510 to 511 (for example, 510 is the first day of the period, and 511 is the last period of the period)). The sales date is 520 to 521, the amount is 530 to 531, the admission date is 540 to 541, and the birth date is 550 to 551), and the corresponding processing result is output. The inquiry for this processing is as follows. SELECT A. Product id, D.I. Name, D. Telephone number, D. Address FROM Product A, Sales B, Member C, Customer Management D WHERE A. Product id = B. Product id AND
A. Purchase date BETWEEN (_, _) AND B.
Sales date BETWEEN (_, _) ANDB. Amount of money
BETWEEN (_, _) AND B. Member number =
C. Membership number AND C. Membership date BETWEEN
(_, _) AND C. Customer code = D. Customer code AND C. Birth Date BETWEEN (_, _) Where A. The item id A indicates the table name, the item id indicates the column value, and the (_, _) ' 'Indicates that a variable is included.

The AP 10 reads the input value set on the screen 50 (100) and generates the inquiry (10).
1). This SQL statement is compiled to create an execution format (102), and the completion of the creation is returned to AP10, requesting the query execution from AP10 (103), and outputting the search result output (104) until there is no output row. (10
5) Processed. In this compilation request (102),
The DBMS 20 is called. The logic processing unit 22 executes the syntax analysis and the semantic analysis of the query sentence (220), determines the optimum processing procedure for determining the order of viewing the table that can answer the request earliest (221), and executes this processing procedure. (222). In the execution request (103), the DBM
The code interpretation execution 223 of S20 is called, the database 40 including the product table 41, the sales table 42, the member table 43, and the customer management table 44 is accessed, and the item designated by the inquiry is output. As described above, in FIG. 4, it is necessary to dynamically generate the query according to the value specified by the user.

FIG. 1 shows screen 5 before AP 10 is executed.
The part related to the inquiry corresponding to 0 is previously stored in the DBMS 20.
Compile with, generate the executable and save it. Specifically, the execution form of the part related to the inquiry is created through the query analysis (220), the optimization process (221), and the code generation (222). The AP 10 reads the input value set on the screen 50 (100), requests the query execution (103), and processes the search result output (104) until there are no output rows (105). Also,
In the execution request (103), the code interpretation execution unit 223 of the DBMS 20 is called, and the product table 41, the sales table 42, the member table 43, and the customer management table 44.
The database 40 consisting of is accessed to output the item designated by the inquiry.

5 and 6 show a DBMS to which the present invention is applied.
It is a flowchart of the processing in. The DBMS performs query analysis processing (220), optimization (221), and execution format conversion (222) that are performed before query execution, and substitutes constants into variables and selects a processing procedure,
The query execution process realizes the code interpretation and execution (223) of the query.

Each processing unit will be described. (A) Query analysis processing ● Query analysis 220 ... Executes the syntax analysis and semantic analysis of the input query sentence. ● Optimization processing 221 ... Determines the proportion of data satisfying the conditions from various conditional expressions appearing in the query. Estimate and create a valid access path candidate (especially selecting an index) based on a preset rule and create a candidate for a processing procedure. Code generation 222 ... Expand processing procedure candidates into an execution format ( b) Query execution processing ● Code interpretation execution 223 ... Based on the assigned constants
Detect conditional expressions that do not require evaluation, determine the processing procedure,
Interpret and execute this processing procedure.

A detailed processing flow of each processing unit will be described. In the query analysis processing 90, first, in the query analysis (220), the syntax analysis and the semantic analysis (220) of the query sentence are performed.
0) After that, there is an index definition in the column that appears in the query condition expression in the optimization process 221 (for example, it is described in the "Introduction to Database" Ohmsha, by Dr. Ryosuke Hotaka, supervised by Junro Emura, illustrated computer series, and Dr. Economics). It is checked whether to do (2210). If there is no index, only the table scan (scan from the first row) is selected as the processing procedure (2211). Otherwise, it is checked whether a variable appears in the query conditional expression (2212). If the variable does not appear,
A processing procedure for preferentially using the index of the column having the smallest selectivity is created (2213). When a variable appears, the index of the column appearing in the query is registered as an access path candidate (2214), and a plurality of processing procedures corresponding to each index are created (2215). Further, in the code generation 222, the processing procedure candidates are expanded into the execution format.

In the inquiry execution processing 91, a code which is an execution form of a processing procedure is interpreted and executed. Code interpretation execution 2
At 23, it is checked whether or not the substitution process for the variable appearing in the query has been executed (2230). If it has been executed even once, the processing procedure of the execution format is interpreted and executed (223).
7). Otherwise, if there is only one processing procedure (22
31), the processing procedure of the execution format is interpreted and executed (223).
7). When there are a plurality of processing procedures, it is checked whether the values to be substituted include infinity, infinity, and undecided (2232). Here, infinity means the largest possible column value, infinitesimal means the smallest possible column value, and undecided means all possible column values. Shall mean.
If it is not included, select the processing procedure that consists only of the index defined in the column that requires conditional expression evaluation (2
235), the execution-type processing procedure is interpreted and executed (22).
37). Otherwise, the true / false of the corresponding conditional expression is determined (2233 to 2234). The processing procedure corresponding to the conditional expression determined to be true is a predetermined processing procedure, the processing procedure corresponding to the conditional expression determined to be false is a predetermined processing procedure, and is also determined to be true or false. As the processing procedure corresponding to the conditional expression that did not exist, a processing procedure consisting only of the index defined in the column that requires conditional expression evaluation is selected (2236), and the execution-type processing procedure is interpreted and executed (2237). .. The predetermined processing procedure corresponding to the conditional expression determined to be true is a processing procedure that outputs all rows, and the predetermined processing procedure corresponding to the conditional expression determined to be false is all the row. Is a processing procedure that does not output.

The true / false evaluation of the conditional expression in 2233 is as follows. However, Sup: infinity, Inf: infinity, Unknown: undecided, and Cn indicates a certain column in the database. case1: selection condition expression (=, <>,>,> =, <, <
=) 1. Cn = variable n: If the variable n is Sup or Inf,
false. If it is Unknown, it is true. 2. Cn <> variable n: 1. The result of is logically inverted. (<
> Indicates that they are not equal) 3. Cn> Variable n: False if the variable n is Sup or Unknown. If it is Inf, it is true. 4. Cn> = variable n: False if the variable n is Sup. I
If nf and Unknown are true. 5. Cn <variable n: False if the variable n is Inf or Unknown. True if Sup. 6. Cn <= variable n: False if the variable n is Inf. S
If it is up or Unknown, it is true. case 2: compound conditional expression (AND, OR) 1. True / false evaluation is performed on each selection conditional expression, and Boolean evaluation of AND and OR is applied. case3: IN conditional expression (conditional expression defined in SQL) 1. Cn IN (variable 1, variable 2, ..., Variable n)
Cn = variable 1 ORCn = variable 2 OR. ． ．
OR Cn = Expand to variable n and apply case2. case4: BETWEEN conditional expression 1. Cn BETWEEN (variable 1, variable 2) is Cn
> = Variable 1 ANDCn <= expand to variable 2 and case
Apply 2. For example, if Cn> = variable 1 is true and Cn <= variable 2 is true, then the boolean evaluation is true. In addition, according to the above inquiry contents, A. Stocking date BETWEEN ( ， ) As an example, Cn is A. Stocking date, ( ， The variables shown in parentheses are variable 1 and variable 2, respectively. The inquiry is executed through the processing steps so far.

The processing flow has been described above. The inquiry processing method of the present invention is not limited to the combined use of the rule using the statistical information and the cost evaluation, but only the cost evaluation can be performed as long as a processing procedure for providing an appropriate database reference characteristic information can be obtained. The present invention can also be applied to a DBMS that performs optimization processing such as only rule usage, cost evaluation and rule usage in combination. The present invention can be realized via a software system of a large-scale computer or a composite processor system in which a dedicated processor is prepared for each processing unit.

[0025]

EFFECTS OF THE INVENTION According to the present invention, 1) By setting an indefinite, infinitesimal small, or infinite large value to a column in which a value is not assigned to a variable included in a query statement, true or false of a corresponding conditional expression is set. Since it is possible to determine, 2) there is no need to evaluate additional conditional expressions.
By selecting from the pre-created processing procedures that consist only of conditional expressions that need to be evaluated, it is not necessary to execute the query analysis processing and dynamically create the execution format of the processing procedure. Processing becomes feasible.

[Brief description of drawings]

FIG. 1 is a schematic explanatory diagram of database processing.

FIG. 2 is a software configuration diagram in the database processing system.

FIG. 3 is a hardware configuration diagram of a database processing device to which the present invention is applied.

FIG. 4 is a schematic explanatory diagram of conventional database processing.

FIG. 5 is a flowchart of processing in the database management system.

FIG. 6 is a flowchart of processing in the database management system.

[Explanation of symbols]

 10, 11 Application program 20 Database management system 22 Logical processing unit 220 Query analysis 221 Optimization 222 Code analysis 223 Code interpretation execution 30 Operating system 40 Database 50 Screen 60 Central processing unit 61 Main storage device 70 Channel device 80 Disk control device 81 Disk apparatus

Claims (6)

[Claims] 1. A query processing method in a database processing system for analyzing a query from a user to a database to determine a corresponding processing procedure, and executing the query, wherein an input query is analyzed and the analysis result is used. A query analysis processing step for creating a processing procedure, and a conditional expression that does not require conditional evaluation by judging from a value assigned to the query and a query conditional expression of the processing procedure,
A query processing method comprising a query execution process for realizing a query process by evaluating a conditional expression excluding the detected conditional expression. 2. The inquiry processing method according to claim 1, wherein the inquiry analysis processing step includes a step of selecting and storing a single or a plurality of internal processing procedures from the analysis result and the statistical information of the database. The query processing method comprises the step of selecting and executing a processing procedure consisting only of conditional expressions requiring conditional expression evaluation from the selected and stored internal processing procedures. .. 3. The query processing method according to claim 1 or 2, wherein the value assigned to the query is infinitesimally small, infinitely large, or undecided. 4. A processor and at least a main memory,
An external storage device in which a database is stored, in a database processing device that analyzes a query from a user to the database to determine a corresponding process, and executes the process, the processing device analyzes the input query. , A query analysis processing unit that creates a processing procedure from the analysis result, and a conditional expression that does not require conditional evaluation by judging from a value assigned to the query and a query conditional expression of the processing procedure,
A database processing device, comprising query execution processing means for realizing a query process by evaluating a conditional expression excluding the detected conditional expression. 5. The database processing apparatus according to claim 4, wherein the inquiry analysis processing means selects and stores a single or a plurality of internal processing procedures from the analysis result and the statistical information of the database. The query execution processing means is configured to select, from the selected and stored internal processing procedures, a processing procedure consisting only of conditional expressions that require conditional expression evaluation, and execute the selected processing procedure. A database processing device characterized by the above. 6. The database processing device according to claim 4 or 5, wherein the value assigned to the inquiry is
A database processing device characterized by being infinitely small, infinitely large, and undecided.