KR100315601B1 - Storing and re-execution method of object-oriented sql evaluation plan in dbms - Google Patents

Abstract

1. TECHNICAL FIELD OF THE INVENTION

The present invention relates to a method for storing and replaying an object-oriented query execution plan.

2. The technical problem to be solved by the invention

The present invention realizes a method and method for storing and re-executing an object-oriented query execution plan which compresses, stores, and executes an execution plan that is a result of query optimization for an object-oriented query in a storage database management system (MDBMS). The aim is to provide a computer readable recording medium having recorded thereon a program for the purpose of recording the program.

3. Summary of Solution to Invention

The present invention includes a first step of generating an execution plan by optimizing an object-oriented query; In the main memory database management system (MDBMS), the data resides in memory, so when the execution plan is stored, the execution plan is configured in a compressed form within a range that can be re-executed to store as many execution plans as possible in the allowed memory. Storing the second step; And a third step of quickly decrypting and rerunning the stored execution plan.

4. Important uses of the invention

The present invention is used in a main memory database management system (MDBMS).

Description

Storage and re-execution method of object-oriented sql evaluation plan in dbms in main memory database management system

The present invention stores an execution plan that is the result of query optimization in a main-memory database management system (MDBMS) so that n (n is a natural number) is used to minimize the time required for query optimization itself. The present invention relates to a method of storing and re-executing an oriented query execution plan and a computer-readable recording medium recording a program for realizing the method. In particular, in a main memory database management system (MDBMS), all data reside in a memory. The present invention relates to a method for storing and re-executing an object-oriented query execution plan that generates, efficiently stores, and executes an execution plan in a compressed form, and a computer-readable recording medium recording a program for realizing the method.

First, the conventional background technologies will be described.

"Access Path Selection in a Relational Database Management System", published in 1978 by the PG Selinger at the ACM SIGMOD Conference (pp23-34), is a disk-based relational database management system. The present invention relates to a query optimization method in System R.

After analyzing advanced query (SQL), this method presents a systematic method for creating an execution plan by determining the order of joins, join algorithm, and each table access method. Determining the join order generates all possible join orders and then determines the minimum cost join order and the join algorithm for each join based on the cost. After the query conditions are separated into unit conditions, the selectivity of each unit condition is calculated, and the access method of each table is determined based on the selectivity. That is, after generating all possible execution plans for the query, their cost is estimated and the least-cost execution strategy is established.

In 1987, "The Exodus Optimizer Generator" published by "G. Graefe" in "ACM SIGMOD Conference (pp160-172)" relates to a query optimization module generator.

In most database management systems, the query optimization technique is designed and implemented in a fixed form in the database management system source code, but this is the idea of automatically generating the query optimization module itself. For automatic generation, we define the necessary factors, rules, and cost model, and when inputted, the automatic generator creates the appropriate query optimization module. By adjusting the arguments, rules, and cost model, the user can create various types of query optimizers experimentally, selecting the appropriate one.

In 1993, the method published by J. Blakeley at the ACM SIGMOD Conference (pp287-296) relates to a rule-based query optimization technique in a disk-resident object-oriented database management system. This method first uses a query transformation rule to transform the algebraic tree, which is the result of parsing, into an optimal algebra tree. The key idea is to use a physical algebraic rule that considers the use of indexes and the like with logical algebraic conversion rules. It is used at the same time.

In addition, US Pat. No. 5,345,585 (Method for optimizing processing of join queries by determining optimal processing order and assigning optimal join methods to each of the join operations: September 6, 1994) With regard to the processing technique, data is stored in relations in table form, and the stored data is retrieved using a join operation. However, the join operator is characterized by a high processing cost, and thus is the most important area to be considered in the optimization technique.

This join operation optimization method adopts a method of randomly selecting an initial calculation order for a join operation and then improving it to an optimal join order by the "KBZ (Krishanmurthy, Boral and Zaniolo)" algorithm. The key idea of this method is to determine the optimal join processing order in a way that has the complexity of a polynomial function.

In addition, US Pat. No. 5,301,317 (System for adapting query optimization effort to expected execution time: April 5, 1994) relates to a system for automatically determining an appropriate amount of resources required for query optimization according to the predicted query execution time. .

The key idea of this method is the cost of the query optimization phase (ie, the cost of predicting the cost of the alternatives) and the run-time benefits of finding and implementing the best execution plan. The intention is to perform query optimization at an appropriate level of profit by analyzing the differences. If it is determined that there is no benefit, query optimization reduces the number of alternatives to consider. In the opposite case, the search space is increased by increasing the number of alternatives. To determine if there is a benefit or not, use compile-time parameters and heuristics.

In addition, US Pat. No. 5,822,747 (System and method for optimizing database queries: April 5, 1994) relates to a query optimization technique for establishing an optimal execution plan for a structured query language (SQL) query.

In the case of nested queries in SQL queries, the query optimizer creates an alternative execution plan and then applies implementation rules and transformation rules to produce the least expensive one. The query optimizer minimizes the search space by not applying all the conversion rules in the process of creating alternative execution plans, but by applying only the parts that are considered to be beneficial. When creating an execution plan for which query optimization is the first alternative, use implementation rules to create an arbitrary execution plan and store its estimated cost as a baseline. In the future, when you create a new execution plan using rules, you should control the creation of the execution plan only if it does not exceed this threshold, so that you do not create excessive search space.

The conventional query optimization techniques described above are techniques for a disk-based database management system (DDBMS) in most cases, and techniques for establishing an execution strategy to minimize the number of disk accesses required for query processing.

However, it is important to support a high performance real-time property in order for a main memory database management system (MDBMS) to be used in an exchange or a communication system. For this purpose, it is desirable to optimize the query in detail, store the result of the query optimization generated at a cost, and reuse it n times.

In other words, in order to satisfy the high performance real-time characteristics in main memory database management system (MDBMS), it is necessary to have a query optimization technique that establishes a minimum cost execution plan for a given query, and to optimize the execution plan for frequently used queries. There is a need for a method that reduces query query optimization time by allowing it to be reused more than n times after being stored in the database.

However, such a technique has not been proposed in main memory database management system (MDBMS). In particular, there have been no cases of studying the execution and storage of execution plans for object-oriented queries.

Accordingly, the present invention, which is designed to meet the above requirements, executes an object-oriented query that compresses, stores, and executes an execution plan that is a query optimization result for an object-oriented query in a main memory database management system (MDBMS). It is an object of the present invention to provide a method of storing and re-executing a plan and a computer-readable recording medium recording a program for realizing the method.

That is, in the present invention, since the data resides in the memory in the main memory device database management system (MDBMS), it is compressed in a range that can be re-executed to store as many execution plans as possible in the allowed memory when storing the execution plans. The present invention provides a method of storing and re-executing an object-oriented query execution plan, which constructs and stores an execution plan, quickly decodes and executes the execution plan, and a computer-readable recording medium recording a program for realizing the method. There is a purpose.

1 is a structural diagram of an embodiment of an execution plan generated by a query optimizer according to the present invention;

Figure 2 is an illustration of an embodiment of the configuration and storage method of the execution plan according to the present invention.

Figure 3 is an embodiment flow diagram for the decryption and re-execution method of the execution plan according to the present invention.

* Explanation of symbols for the main parts of the drawings

11: query optimizer 21: execution plan table

22: conditional table

The method of the present invention for achieving the above object, in the storage and re-execution method of the object-oriented query execution plan applied to the main memory device database management system (MDBMS), the first step of optimizing the object-oriented query query to generate the execution plan ; In the main memory database management system (MDBMS), the data resides in memory, so when the execution plan is stored, the execution plan is configured in a compressed form within a range that can be re-executed to store as many execution plans as possible in the allowed memory. Storing the second step; And a third step of quickly decoding and re-executing the stored execution plan.

On the other hand, the present invention, the main memory device database management system (MDBMS) having a processor, a first function for generating an execution plan by optimizing the object-oriented query language; In the main memory database management system (MDBMS), the data resides in memory, so when the execution plan is stored, the execution plan is configured in a compressed form within a range that can be re-executed to store as many execution plans as possible in the allowed memory. A second function of storing; A computer readable recording medium having recorded thereon a program for realizing a third function of quickly decrypting and executing the stored execution plan is provided.

The above objects, features and advantages will become more apparent from the following detailed description taken in conjunction with the accompanying drawings. Hereinafter, exemplary embodiments of the present invention will be described in detail with reference to the accompanying drawings.

First, the main concept of the present invention will be described as follows.

In the present invention, the execution plan resulting from the query optimization is configured and stored in a compressed form, and the stored execution plan is executed again. Unlike the disk-based database management system (DBMS), the execution plan is compressed and stored to maximize the limited memory resources in the main memory database management system (MDBMS), where all data reside in memory. In particular, model the storage form to follow the object relational model. Next, when rerunning the execution plan, the execution plan represented by the object relational model is read, reinterpreted and executed, and the solution is returned.

1 is a structural diagram of an embodiment of an execution plan generated by the query optimizer according to the present invention.

In the execution result for a given SQL (Structured Query Language) statement, each class included in the query is represented by one node, and each node has the following fields.

Class information (T1, T2, ...): A field with information about a class that contains pointers in memory where objects of that class are stored, the size of each object, the offset and data type information of each property. It is composed.

Join algorithm (Null, Ptr-based, ...): A field that indicates how to join between the class just accessed and the class now being accessed. Null for the first class and class after the second. In the case of OID (Object Identifier), it is expressed as "Ptr-based", otherwise it is expressed as "Nested-loop".

The index to be used (Index (att1), T1.att3, ...). This field specifies the access method to be used when retrieving objects from the current class. If you use an index on the attribute (att), use "Index (att In the case of using an OID (Object Identifier) stored as an attribute value, "Table name. Attribute name" (for example, T1.att3), and a sequence (SEQ) for sequential access.

Condition (att2 θ v, att4 θ v ', ...): A field indicating the condition to check after getting an object from each class. In the case of join condition, two fields to be checked after both objects to be joined are accessed. Attached to the first accessed class.

Pointer to next node: This field represents the pointer to the next class to be accessed.

FIG. 2 is a diagram illustrating an embodiment of a method of configuring and storing an execution plan according to the present invention, and shows a state in which the information illustrated in FIG.

Here, the description of each field is as follows.

Table pointer (table-ptr): A field that represents a pointer to a class. For example, address T1 represents the starting address of where an object of class T1 is stored.

Join-algo: This field indicates the join algorithm.

Acc-path: A field indicating an access path, where index or sequential access is used.

Conditions: This field indicates the conditions imposed on the current table.

Next: This field indicates the starting address of the next table to be accessed.

Each node illustrated in FIG. 1 is represented by one execution plan table (EvalPlanTable) object and as many condition table objects (CondTable) objects as in FIG. 2. Each field of the execution plan table (EvalPlanTable) object has the same meaning as that of each field of FIG. 1 as described above, but is converted into a pointer in memory. However, in the case of a condition, two or more conditions may be imposed on each table. Therefore, the condition is stored in a separate class (CondTable) 22, which is indicated by a pointer.

That is, in FIG. 1, the first node is represented by the first object of the execution plan table (EvalPlanTable) 21 and the first object of the condition table (CondTable) 22 of FIG. 2, and the second node of FIG. The second object of the execution plan table (EvalPlanTable) 21 and the second and third objects of the condition table (CondTable) 22 are represented. The compressed representation of FIG. 2 is the same as the object format of the object-relationship model provided by the real-time database system (RTDBS), so that the user can select the objects of the execution plan table (EvalPlanTable) 21 and the condition table (CondTable) 22. You can inquire or change the query. In other words, in FIG. 1, execution plan information and condition information are distributed to each node so that it is not suitable to be stored with wasted memory, but in the proposed method, information is stored by integrating and storing them in two tables (EvalPlanTable and CondTable). Compress and save.

3 is a flowchart illustrating a method of storing an object-oriented query execution plan according to an exemplary embodiment of the present invention, and is an algorithm showing a method of generating and storing an execution plan for storage of FIG. 2 from a previously generated execution plan of FIG. 1.

First, when a user receives a command to generate an execution plan for a given SQL (30), the system determines whether a storage execution plan table (EvalPlanTable) and a condition table (CondTable) exist (31).

If it is determined in step 31 that the execution plan table (EvalPlanTable) and the condition table (CondTable) do not exist, the execution plan table (EvalPlanTable) and the condition table (CondTable) are stored in the form of classes in the memory database. Create (32, 33).

Here, the execution plan table (EvalPlanTable) and the condition table (CondTable) for storage are places to store all the execution plans and conditions for various escrow (SQL). Therefore, instead of creating a table every time an execution plan for SQL is created, it is created only once, and all execution plans created afterwards are stored as objects in these two tables.

Next, information is extracted from each node of FIG. 1 made by the query optimizer and recorded in an execution plan table (EvalPlanTable) in the form of an object (34). In this case, if the object to be recorded is t1, the information contained in the object t1 is a table number, a type of join algorithm, an access path, a pointer to a condition, and next information.

Next, the condition for each node is divided into a number of unit conditions. For example, if the condition is "a1 = 20 and a2 = a3", two unit conditions "a1 = 20" and "a2 = a3" Each unit condition is stored as an object in a condition table (CondTable), and is referred to as the value of attribute conditions in the object t1 of the execution plan table (EvalPlanTable) for storage (35). Then, it is determined whether the current unit condition is the last unit condition (36).

If it is determined in step 36 that the current unit condition is not the last unit condition, the process returns to step 35 and repeats the separation of unit conditions until the last unit condition included in the condition field of the node. If the current unit condition is the last unit condition, it is determined whether it is the last node included in the execution plan (37).

As a result of the determination in step 37, if it is not the last node included in the execution plan, the process returns to the process 34 of extracting information from the node and recording the information in the execution plan table in the form of an object, repeating the loop, and executing the execution plan. In the case of the last node included in, the object creation and storage of the execution plan table for the number of nodes included in the execution plan is terminated.

FIG. 4 is a flowchart illustrating an example of a method of decrypting and re-executing an execution plan according to the present invention, which is an algorithm describing a method of reading and executing an execution plan stored in the form of FIG. 2.

First, when a user makes a query about an execution plan stored SQL, the system searches for an execution plan object corresponding to the query in an execution plan table (EvalPlanTable) using a hashing technique (41). At this time, it is checked whether there is a corresponding object (42), and if not, the query is already processed, so the result [] array, which is an object array that collects solutions, is returned (43).

Meanwhile, if the corresponding object exists (42), the object to be joined next is fetched from the database using the access path given in the object indicated by the pointer Ptr (44). At this time, if the object indicated by the pointer Ptr is the first object of the execution plan, it is not necessary to bring the object to be joined. The database object obtained in step 44 is marked Oi.

Then, it is checked whether the object Oi satisfies the condition imposed on the node (45). At this time, the condition imposed on the node is found by following the conditions field of the pointer (Ptr), and all conditions are examined by checking the condition until the next field of the CondTable becomes null. Check if it is satisfied (45). The process of reading and inspecting an object is repeated until the object that satisfies all conditions is found (44, 45). If the object Oi satisfies all the conditions, store the object Oi in result [k ++], the array that stores the result objects (46), and redirect the pointer (Ptr) to point to the next object, the next object in the execution table. (47), the process proceeds to step 42 to check whether the pointer is null and continues execution.

The method of the present invention as described above may be implemented as a program and stored in a computer-readable recording medium (CD-ROM, RAM, ROM, floppy disk, hard disk, magneto-optical disk, etc.).

The present invention described above is not limited to the above-described embodiments and the accompanying drawings, and various substitutions, modifications, and changes can be made in the art without departing from the technical spirit of the present invention. It will be apparent to those of ordinary knowledge.

As described above, the present invention can reduce the time required for the query optimization itself by compressing and storing the execution plan in the development of the query optimization module of the main memory device database management system (MDBMS), and executing it n times. By improving the performance of the database management system, it is possible to increase the real-time performance of the main memory database management system, thereby contributing to the improvement of the overall system, and minimizing the storage space considering the main memory database management system (MDBMS). It can be effective.

Claims (10)

In the storage and re-execution method of the object-oriented query execution plan applied to the main memory database management system (MDBMS), A first step of optimizing the object-oriented query and generating an execution plan for storing; In the main memory database management system (MDBMS), the data resides in memory, so when the execution plan is stored, the execution plan is configured in a compressed form within a range that can be re-executed to store as many execution plans as possible in the allowed memory. Storing the second step; A third step of re-executing by reading an object that satisfies a condition by inspecting the object of the execution plan stored in the compressed form How to save and rerun the object-oriented query execution plan comprising a. The method of claim 1, Each node of the storage execution plan in the first step is A class information field representing information about a class; A join algorithm field indicating a join method between the class accessed immediately before and the class to be accessed now; An index field to be used to specify the access method to be used when retrieving an object from the current class; A condition field indicating a condition to check after importing an object from each class; And Pointer field to the next node that represents a pointer to the class to be accessed next. How to save and rerun the object-oriented query execution plan comprising a. The method of claim 1, The compressed execution plan in the second step, A table pointer field representing a pointer to a class; A join algorithm field indicating a join algorithm; An access path field indicating an access path; A condition field indicating a condition imposed on the current table; And Next field indicating the starting address of the table to be accessed next How to save and rerun the object-oriented query execution plan comprising a. The method according to any one of claims 1 to 3, The third step, A fourth step of searching for an execution plan object corresponding to the input query by using a hashing technique in an execution plan table (EvalPlanTable); A fifth step of returning an object array in which a solution is collected if a corresponding object is not found as a result of the fourth step; And As a result of the inspection in the fourth step, if the corresponding object exists, the object is read from the database until the object satisfying the condition is checked, and after the result object satisfying the condition is stored in the array in which the result object is stored, the next object is measured. A sixth step of determining and repeating from the fourth step How to save and rerun the object-oriented query execution plan comprising a. The method of claim 4, wherein The sixth step, A seventh step of reading an object to be joined next from the database by using an access path given in the object indicated by the pointer Ptr; An eighth step of checking whether the read object satisfies a condition imposed on the node; If the result of the determination of the eighth step is not satisfied, the ninth step is repeated from the seventh step until an object satisfying all conditions is found; And As a result of the determination of the eighth step, if the object satisfies all the conditions, the object is stored in the array in which the result object is stored, and after the pointer Ptr is reassigned to point to the next object of the execution plan table, the inspection of the fourth step is performed. Ten steps to repeat from the process How to save and rerun the object-oriented query execution plan comprising a. In a main memory database management system (MDBMS) having a processor, A first function of optimizing an object-oriented query and generating an execution plan for storing; In the main memory database management system (MDBMS), the data resides in memory, so when the execution plan is stored, the execution plan is configured in a compressed form within a range that can be re-executed to store as many execution plans as possible in the allowed memory. A second function of storing; A third function of inspecting an object of the execution plan stored in the compressed form and re-executing an object satisfying the condition A computer-readable recording medium having recorded thereon a program for realizing this. The method of claim 6, Each node of the execution plan generated by the first function is A class information field representing information about a class; A join algorithm field indicating a join method between the class accessed immediately before and the class to be accessed now; An index field to be used to specify the access method to be used when retrieving an object from the current class; A condition field indicating a condition to check after importing an object from each class; And Pointer field to the next node that represents a pointer to the class to be accessed next. A computer-readable recording medium having recorded thereon a program for realizing this. The method of claim 6, The execution plan compressed in the second function is A table pointer field representing a pointer to a class; A join algorithm field indicating a join algorithm; An access path field indicating an access path; A condition field indicating a condition imposed on the current table; And Next field indicating the starting address of the table to be accessed next A computer-readable recording medium having recorded thereon a program for realizing this. The method according to any one of claims 6 to 8, The third function, A fourth function of checking whether a corresponding object exists by searching a execution plan object corresponding to the input query by using a hashing technique in an execution plan table (EvalPlanTable); A fifth function of returning an array of objects in which solutions are collected if a corresponding object does not exist as a result of the check in the fourth function; And As a result of the inspection in the fourth function, if the object exists, the object is read from the database until the object satisfying the condition is examined and the object satisfying the condition is stored in the array where the result object is stored. A sixth function that is determined and repeated from the inspection process of the fourth function A computer-readable recording medium having recorded thereon a program for realizing this. The method of claim 9, The sixth function is A seventh function of reading the object to be joined next from the database using the given access path in the object indicated by the pointer Ptr; An eighth function of checking whether the read object satisfies a condition imposed on the node; A ninth function that repeats from the seventh function until an object that satisfies all conditions is found, as a result of the determination in the eighth function; And As a result of the determination in the eighth function, if the object satisfies all the conditions, the object is stored in the array in which the result object is stored, and the pointer Ptr is reassigned to indicate the next object in the execution plan table. 10th function to repeat from the inspection process A computer-readable recording medium having recorded thereon a program for realizing this.