KR20190032697A - Method for Automatic Optimizing Index Design of Database - Google Patents

Abstract

Provided is a method of automatic optimization of database index design which ensures optimal performance for the inefficiency portion of the indexes occurring in the database and efficiently design indexes. According to the present invention, a method of automatically optimizing a database index design, which is executed through a server accessible to a database management system (DBMS) including one or more tables, index information and meta information comprises the steps of collecting meta information of the DBMS to optimize an index; collecting an execution plan (PLAN); reading an analyzed access path (AAP); generating a refined access path (FAP); comparing the generated refined access path (FAP) with an analysis access path (AAP) of the execution plan generated in the DBMS for each type and confirming whether the redesigned improved access path (FAP) is matched by comparing an analysis access path (AAP) of an execution plan generated in the DBMS by type; and structuring the inconsistent refined access path (FAP) to be included into the analysis access path (AAP) of the execution plan when the refined access path (FAP) and the analysis access path (AAP) are unmatched and applying the same to index information of the DBMS.

Description

Method for Automatic Optimization Index Design of Database Index Design

The present invention is configured to cover a large number of SQLs accessing a table with a minimum index, and to allow an optimizer of a database to establish an optimal execution plan. In particular, The present invention relates to a method of automatically optimizing a database index design that ensures optimum performance and efficiently design indexes.

A major issue in the current database management system is performance improvement. Most of the performance degradation is related to inefficient I / O, and the data used by enterprises is continuously increasing. However, since there is no indexing method that can effectively find the desired data from the increasing data The optimum performance can not be guaranteed.

At present, there is a desperate need for a method for improving the performance of a database, that is, a method for designing and managing an optimal index.

An object of the present invention to overcome the above problems is to provide a method of automatically optimizing a database index design that ensures optimal performance of an inefficiency portion of an index generated in a database and efficiently design indexes.

A method of automatically optimizing a database index design that is executed through a server accessible to a DBMS (Database Management System) including one or more tables, index information, and meta information according to the present invention includes collecting meta information of a DBMS (N > = 1) query messages (QUERY) transmitted from the M (M > = 1) terminals to the DBMS and processed for a specific table among the tables of the DBMS, A second step of collecting an execution plan (PLAN) generated for analyzing and executing a statement; sorting the collected query statements by type, interpreting the classified query syntax of each type, and arranging column values and operators A type of conditional rule including a raw access path (RAP) is read out, and a column value included in the classified execution plan of the classified execution plan A third step of reading an analyzed access path (AAP) including an array of a plurality of objects; and a third step of reading the analyzed access path (AAP) A fourth step of generating a re-designed access path (FAP) in which a column value included in the type of the conditional expression and an array of operators are rearranged for each type based on one or more pieces of reference information including information on the re- (FAP) and an analysis access path (AAP) of an execution plan generated in the DBMS, and comparing the improved access path (FAP) and the analysis access path (AAP) AAP) is not matched, the inconsistent improvement access path (FAP) is structured to be included as an analysis access path (AAP) of the execution plan, and index information of the DBMS It can comprise a sixth step of for.

According to the present invention, the table includes a plurality of columns, and the index information includes a column access path (AP) set by a developer who designed the column structure of each column included in the table, And a column access path (AP) generated through a previous optimization process.

According to the present invention, the meta information includes at least one of a table structure of a table, a number of rows of each column, a number of null values of each column, attribute information of each column, information recorded in each column, The degree of inherentness of the processed column, and the index information set in the current DBMS.

According to the present invention, the first step may further include collecting table-related information about a table to be optimized in the index among the tables provided in the DBMS.

According to the present invention, the first step may include collecting table related information about a table to be optimized in an index among the tables provided in the DBMS and meta information of the table.

According to the present invention, the reference information may be configured to include an intrinsic value for each column in which information recorded in each column is statistically processed without overlapping with each other. In this case, in the fourth step, Rearranging a column value having a high intrinsic value of each column among the two or more column values included in the type so that the column value having a high intrinsic value precedes a column value having a low intrinsic value.

According to the present invention, the reference information may be configured to include an intrinsic value for each column in which the information recorded in each column is statistically processed without overlapping with each other. In this case, in the fourth step, Rearranging the column values of the two or more column values included in the type so that the column value having a low intrinsic value of each column is located later than the column value having the high intrinsic value.

According to the present invention, the reference information may be configured to include an intrinsic value for each column in which the information recorded in each column is statistically processed without overlapping with each other. In this case, in the fourth step, And excluding column values of the two or more column values included in the type of which the inherent degree is less than or equal to a certain ratio.

According to the present invention, in the fourth step, the column values combined with the '=' operator among the combinations of two or more column values and operators included in the type of the conditional rule are rearranged so as to be prioritized over column values combined with other operators May comprise a step.

According to the present invention, the reference information may be configured to include the number of null values of each column, and in this case, the fourth step may include the steps of: And rearranging the column values having a large number of columns to be arranged higher than the column values having a small number of null values.

According to the present invention, the reference information may be configured to include the number of null values of each column, and the fourth step may include a step of, when the number of null values of the two or more column values included in the type of the conditional expression is small Rearranging the column values such that the column values are later than the column value with a large number of null values.

According to the present invention, in the fifth step, the column value included in the improvement approach path (FAP) and the array of the operator, the column value included in the analysis access path (AAP) and the array of the operator are compared Step < / RTI >

According to the present invention, the fifth step includes a step of comparing the array of the column values included in the improved approach path (FAP) with the array of the column values included in the analysis access path (AAP) Lt; / RTI >

In the fifth step, an operator combined with a column value included in the improvement access path (FAP) is compared with an operator combined with a column value included in the analysis access path (AAP), and a certain ratio And if so, whether or not they match.

According to the present invention, the sixth step may further include a step of repeating the first to sixth steps when the improvement access path (FAP) and the analysis access path (AAP) do not match .

According to the present invention, the sixth step may include: stopping the process of generating an improved access path (FAP) when the improved access path (FAP) and the analyzed access path (AAP) match, The method may further comprise scheduling the first to sixth steps to be performed again.

According to the present invention, there is an advantage that an optimal performance can be guaranteed for an inefficient portion of an index generated in a database, and an index can be efficiently designed.

1 is a diagram showing a configuration of an automatic optimization system of a database index design according to an embodiment of the present invention.
2 is a diagram illustrating an automatic optimization process of a database index design according to an embodiment of the present invention.
3 is a diagram illustrating a process of providing a selective discount service using a payment plug-in advertisement according to an embodiment of the present invention.
4 is a diagram illustrating an example of providing a selective discount service using a payment plug-in advertisement according to an embodiment of the present invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Hereinafter, preferred embodiments of the present invention will be described in detail with reference to the accompanying drawings. It should be understood, however, that the drawings and the following detailed description are exemplary and explanatory and are intended to provide further explanation of the invention, and are not to be construed as limiting the present invention.

In other words, the following embodiments correspond to the preferred embodiment of the preferred embodiment of the present invention. In the following embodiments, a specific configuration (or step) is omitted, or a specific configuration (or step) (Or steps), or an embodiment that incorporates functions implemented in more than one configuration (or step) into any one configuration (or step), a particular configuration (or step) It will be apparent that the present invention is not limited to the embodiments described below. Therefore, it should be clearly stated that various embodiments corresponding to subsets or combinations based on the following embodiments can be subdivided based on the filing date of the present invention.

In the following description of the present invention, a detailed description of known functions and configurations incorporated herein will be omitted when it may make the subject matter of the present invention rather unclear. The terms used below are defined in consideration of the functions of the present invention, which may vary depending on the user, intention or custom of the operator. Therefore, the definition should be based on the contents throughout the present invention.

As a result, the technical idea of the present invention is determined by the claims, and the following embodiments are merely means for effectively explaining the technical idea of the present invention to a person having ordinary skill in the art to which the present invention belongs Only.

1 is a diagram showing a configuration of an automatic optimization system of a database index design according to an embodiment of the present invention.

In more detail, FIG. 1 illustrates a simple configuration of an automatic optimization system of a database index design executed on a server accessible to a DBMS (Database Management System) including one or more tables, index information, and meta information, One of ordinary skill in the art will be able to refer to and / or modify this Figure 1 to deduce various implementations of the configuration of the automatic optimization system of the database index design, And the technical features thereof are not limited only by the method shown in FIG.

Referring to FIG. 1, an automatic optimization system of a database index design is accessible to a DBMS 135 including one or more tables, index information, and meta information, and includes a QUERY query for a specific table in the DBMS 135, (FAP) and an analysis access path (AAP) of an execution plan generated within the DBMS 135. The FAP includes a plurality of access paths And a DBMS 135 including one or more tables, index information, and meta information.

The DBMS 135 collectively refers to software that manages a database and provides an environment in which application programs can share and use the database, and may include one or more tables, index information, and meta information.

Here, the table included in the DBMS 135 may include a plurality of columns.

In addition, the index information included in the DBMS 135 includes a column access path (AP) set by a developer who designed the column structure of each column included in the table, and a column access path And an access path (AP).

Further, the meta information included in the DBMS 135 includes a table structure, a number of rows of each column, a number of null values of each column, attribute information of each column, and information recorded in each column, It is possible to include one or more index information set in the current DBMS, the degree of inheritance of each column statistically processed.

Here, the column structure of the table is a table structure in which each column has some information (e.g., 'COL3', 'COLL4', 'COL5', 'COLUMN', 'COL6' Information recording, or the like) recorded on the recording medium.

The attribute information of each column of the table records the attribute of each column (for example, 'COL3' records 'String', 'COL4' records 'Occupation Code' assigned to each occupation, and 'COL5' And 'COL6' records the letter 'M' in the case of a man and records the letter 'F' in the case of a woman).

The intrinsic value of each table in the table is information about duplication of information, and the degree of intrinsic value of COL5 which is not duplicated at all such as 'resident number' is '100' If only 'M' identifying the gender is recorded (ie, if the entire column is duplicated), the intrinsic can be considered as '0', where the intrinsic may be pre-generated and collected in the state stored in meta information, The information stored in each column of the table can be statistically processed and then collected and collected in real time.

The index information of the table may include at least one of an access path (AP) of a column arbitrarily set by a developer and an access path (AP) of a column generated through a previous optimization process. An access path (AP) May be collected in a state pre-stored in the meta information, or may be extracted and collected in real time from the index information at the time of collection.

The server 100 shown in FIG. 1 is a generic term for a functional configuration to be implemented on the server side for automatic optimization of a database index design according to the present invention, and is preferably provided on a path between the terminal 140 and the DBMS 135 Lt; / RTI > servers.

For example, the server 100 may include the DBMS 135 or may be interworked with a server (not shown) provided with the DBMS 135, and may be connected to a specific physical server But it is not limited.

Referring to FIG. 1, the server 100 includes a meta information collection unit 105 for collecting meta information of a DBMS 135, a query transmitted to process a specific table of the DBMS 135 in the terminal 140, An execution plan collecting unit 110 for collecting a query QUERY and collecting an execution plan (PLAN) corresponding to the inquiry statement, an analysis access path for an execution plan corresponding to the query query classified by type, An access path reading unit 115 for reading an access path (AAP) of the access path, and an access path generating unit 115 for generating an improved access path (FAP) by rearranging the array of the column value and the operator based on the at least one reference information, (FAP) and an analysis access path (AAP) for each type, and a matching check unit (125) for checking whether or not the FAP and the analysis access path (AAP) (AAP) 130. The structured application unit 130, As shown in FIG.

The meta information collecting unit 105 may collect meta information of the DBMS 135 to optimize the index according to the present invention. The meta information may include a column structure of a table of the DBMS 135, The number of null values of each column, the attribute information of each column, the degree of inheritance of each column in which the information recorded in each column is statistically processed without overlapping the information, and the index information set in the current DBMS 135 can do.

According to the present invention, the meta information collecting unit 105 may collect table related information about a table for optimizing an index among the tables provided in the DBMS 135. [

Here, the table-related information is information necessary for generating an improved access path (FAP) among information not included in the meta information. For example, if only the meta information includes the inherent degree, only the meta information may be collected. If the intrinsic value is not included in the meta information, it is preferable to extract it in real time using the table related information.

In addition, according to the present invention, the meta information collection unit 105 may collect table related information about a table to be optimized in an index among the tables provided in the DBMS 135 and meta information of the table.

For example, a plurality of tables (e.g., a membership information table and a payment history table) may exist in one DBMS 135, and table related information and meta information are collected for each table in order to optimize the index for each table .

When the meta information of the DBMS 135 to be optimized by the index is collected through the meta information collection unit 105, the execution plan collection unit 110 acquires M (M? 1) terminals 140 from the DBMS 135, (N > = 1) query statements to be processed for a specific table among the tables of the DBMS 135 and analyzes the query statement in the DBMS 135 to execute an execution plan Can be collected.

The access path reading unit 115 reads out a query sent to the specific table through the execution plan collecting unit 110 and an execution plan generated to analyze and execute the query in the DBMS 135, The type of the conditional statement including the raw access path (RAP) in which the column values and operators are arranged is read out by classifying the collected query statements by type, interpreting the classified query syntax of each type .

In addition, the access path reading unit 115 can read an analyzed access path (AAP) including an array of operators and column values included in the classified execution plans of the classified execution plans have.

The access path generation unit 120 reads the type of the conditional expression including the raw access path (RAP) through the access path reading unit 115, and outputs the column value included in the classified execution plan and the operator (AAP) including the arrangement of the meta information is read out, one or more reference information including the information included in the collected meta information or information included in the meta information, A redefined access path (FAP) can be generated by rearranging the column values included in the type of the conditional expression and the array of operators by type.

In the course of generating the improved access path (FAP), the access path generation unit 120 generates the access path FAP based on the combination of two or more column values and operators included in the type of the conditional expression, It is possible to rearrange columns so that they are arranged prior to the combined column values.

According to the method of the present invention, the reference information as a basis for generating the improved access path (FAP) includes the degree of inheritance for each column in which the information recorded in each column is statistically processed without overlapping the information And the access path generation unit 120 may rearrange the column values having higher intrinsic values of the columns among the two or more column values included in the type of the conditional precedence than those having lower intrinsic values.

According to another embodiment of the present invention, the reference information used as a basis for generating the improved access path (FAP) includes the degree of inheritance for each column in which the information recorded in each column is statistically processed without being duplicated And the access path generation unit 120 may rearrange the column values of the two or more column values included in the type of the conditional expression so that the column value having a low intrinsic value of each column is later than the column value having a high intrinsic degree.

According to another embodiment of the present invention, the reference information used as a basis for generating the improved access path (FAP) includes the degree of inheritance for each column in which the information recorded in each column is statistically processed without duplication of information And the access path generation unit 120 may exclude a column value having a certain degree or less of the inherent degree among the two or more column values included in the type of the conditional expression.

According to another embodiment of the present invention, the basis information for generating the improved access path (FAP) may include the number of null values of each column, and the access path generating unit 120 ) May rearrange the column values of the two or more column values included in the type of the conditional expression such that the column value having a large number of null values precedes the column value having a small number of null values.

According to another embodiment of the present invention, the basis information for generating the improved access path (FAP) may include the number of null values of each column, and the access path generating unit 120 ) Can rearrange the column values of the two or more column values included in the type of the conditional rule such that the column value having the smallest number of null values is later than the column value having the large number of null values.

When the improved access path (FAP) in which the column values included in the type of the conditional expression and the array of the operators are rearranged for each type is generated through the access path generation unit 120, (AAP) of the execution plan generated in the DBMS 135 by comparing the FAP with the improved access path (FAP).

According to the embodiment of the present invention, the matching confirmation unit 125 compares the column value included in the improvement access path (FAP) with the array of the operator and the array of the column value and the operator included in the analysis access path (AAP) To confirm that they match.

In this case, the matching confirmation unit 125 can be regarded as belonging to a category matching both the column value and the operator.

According to another embodiment of the present invention, the matching confirmation unit 125 compares the array of the column values included in the improvement approach path (FAP) with the array of the column values included in the analysis access path (AAP) Can be confirmed.

In this case, the matching confirmation unit 125 can be regarded as belonging to a matching category even if the column values match and the operators are different.

According to another embodiment of the present invention, the matching confirmation unit 125 determines whether or not the combination of the operator combined with the column value included in the improvement access path (FAP) and the column value included in the analysis access path (AAP) Operators can be compared to see if they match more than a certain percentage within the allowed range.

In this case, the matching confirmation unit 125 determines whether or not the column values of the improvement access path (FAP) and the analysis access path (AAP) are in the same order, If they are the same, they can be regarded as matching.

If the structured application unit 130 determines that the improved access path FAP and the analyzed access path AAP do not match through the matching acknowledgment unit 125, And can be applied to the index information of the DBMS 135 by being structured so as to be included as an analysis access path (AAP) of the execution plan.

According to an embodiment of the present invention, the structured application unit 130 may receive the structured information from the DBMS (AAP) through the meta information collection unit 105 when the improved access path FAP and the analyzed access path AAP do not match. 135) for collecting table related information about a table to be optimized in an index among the tables provided in the DBMS (135), and analyzing the query statement in the DBMS (135) And an access path reading unit (115) for classifying the collected query statements according to types, interpreting the classified query syntax of each type, and generating a raw access path in which column values and operators are arranged The access path generation unit 120 reads the information included in the collected meta information and the information included in the meta information, Generating an improved access path (FAP) by rearranging an array of operators and column values included in a type of a conditional expression based on one or more pieces of reference information including information that is confirmed or statistically processed; (AAP) of the execution plan generated within the DBMS 135 and the improved access path (FAP) generated by the rearrangement is checked for each type, have.

According to another embodiment of the present invention, when the improvement access path FAP and the analysis access path AAP are matched, the structuring application 130 stops the process of generating the improvement access path FAP, Collecting table related information about a table to be optimized in the index among the tables provided in the DBMS 135 through the meta information collecting unit 105 after the elapse of the predetermined time, Collecting an execution plan generated for analyzing and executing the query statement in the DBMS 135; classifying the collected query statements according to types by the access path reading unit 115; Reading a type of a conditional statement including a raw access path (RAP) in which a column value and an operator are arranged by interpreting a syntax of a query inquiry for each type, Based on at least one piece of reference information including information obtained from the table or information statistically processed by using the information included in the meta information, and rearranging the array of the column values and the operators included in the type of the conditional, (FAP) and an analysis access path (AAP) of an execution plan generated in the DBMS (135) through the matching confirmation unit (125) ) Are compared for each type and it is possible to perform scheduling again to check whether they are matched

FIG. 2 is a diagram illustrating an automatic optimization process of a database index design according to an embodiment of the present invention.

2 is a diagram illustrating an example of an automatic optimization system of the database index design shown in FIG. 1, in which a server 100 of a database index design system includes one or more tables, a query message for a specific table of the DBMS 135 including index information and meta information QUERY ") and an execution plan (PLAN) are collected and read out. As a person skilled in the art, referring to and / or modifying FIG. 2, It is to be understood that the present invention is not limited to the above-described embodiments, and various modifications and changes may be made without departing from the scope of the present invention.

Referring to FIG. 2, an automatic optimization process of the database index design shown in FIG. 1 is performed by the server 100 of the automatic index optimization system of the database index design shown in FIG. 1, (200). ≪ / RTI >

The server 100 collects meta information of the DBMS 135 to optimize the index, wherein the meta information includes a column structure of a table of the DBMS 135, the number of rows of each column, the number of null values of each column, The attribute information of each column, the information recorded in each column, the intrinsic value of each column in which the unique ratio is statistically processed, and the index information set in the current DBMS 135.

In addition, the server 100 may collect table-related information about a table to be optimized in the index among the tables provided in the DBMS 135 in the process of collecting meta information of the DBMS 135 to optimize the index The table-related information is information necessary for generating an improved access path (FAP) among information not included in the meta information. For example, if only the meta information includes the inherent degree, only the meta information may be collected. However, If the inheritance is not included in the meta information, it can be extracted in real time using the table related information.

In addition, the server 100 may collect table-related information about a table to be optimized in an index among the tables provided in the DBMS 135 and a table-related information of a table to be optimized in the process of collecting meta information of the DBMS 135, Meta information can be collected.

When the meta information of the DBMS 135 to be optimized for the index is collected, the server 100 is transferred from the at least one terminal 140 to the DBMS 135 and processed for a specific table among the tables of the DBMS 135 One or more query statements are collected (205).

Then, the server 100 collects an execution plan (PLAN) generated to analyze and execute the query statement in the DBMS 135 (210).

When a query to be processed for the specific table and an execution plan to analyze and execute the query in the DBMS 135 are collected, the server 100 classifies the collected query statements by type 215).

If the collected query statements are classified according to types, the server 100 interprets the query syntax of each classified type, and generates a conditional statement including a RAP that includes a column value and an operator, (225).

Then, the server 100 reads an analysis access path (AAP) including the column values and the array of operators included in the classified execution plans of the classified execution plans (230).

FIG. 3 is a diagram illustrating an automatic optimization process of a database index design according to an embodiment of the present invention.

3, after the process of FIG. 2, an improved access path (FAP) is created, and the generated improved access path (FAP) and an analysis access path of an execution plan generated within the DBMS 135 (AAP) are compared with each other to confirm that they are matched and applied to the index information of the DBMS 135 structured to include the unmatched improvement access path (FAP) as an analysis access path (AAP) Those skilled in the art will be able to refer to and / or modify Figure 3 to deduce various implementations of the automatic optimization process of the database index design, All of the above-described embodiments are included, and the technical features of the present invention are not limited only by the method shown in FIG.

Referring to FIG. 3, the automatic optimization process of the database index design shown in FIG. 1 is performed by the server 100 of the automatic optimization system of the database index design shown in FIG. 1, ) Is read out, and after an analysis access path (AAP) including an array of operators and column values included in the classified execution plan is read, the information included in the collected meta information (300) from the process of confirming one or more reference information including information confirmed or statistically processed from the table using the information included in the meta information.

Here, the reference information may include the degree of intrinsics for each column in which the information recorded in each column is statistically processed without being duplicated.

In addition, the reference information may include the degree of intrinsics for each column in which the information recorded in each column is statistically processed without being duplicated.

In addition, the reference information may include the number of NULL values of each column.

When one or more pieces of reference information are identified as described above, the server 100 generates an improved access path (FAP) by rearranging the column values and the array of operators included in the type of the conditional expression based on the identified one or more pieces of reference information, (305).

Here, in the process of generating the improved access path (FAP), the server 100 determines whether the column value combined with the '=' operator among the combinations of two or more column values and operators included in the type of the conditional statement is combined with another operator It is possible to rearrange the column values so that they are arranged prior to the column values.

In addition, the server 100 can rearrange the column values having higher intrinsic values of the respective columns among the two or more column values included in the type of the conditional expression so that the column values have a higher degree of uniqueness.

In addition, the server 100 can rearrange the column values of the two or more column values included in the type of the conditional expression so that the column value having a low intrinsic value of each column is later than the column value having a high intrinsic degree.

In addition, the server 100 may exclude column values having a certain degree or less of the inherent degree among the two or more column values included in the type of the conditional expression.

In addition, the server 100 can rearrange the column values having a larger number of null values than the column values having a smaller number of null values, among the two or more column values included in the type of the conditional statement.

In addition, the server 100 can rearrange the column values of the two or more column values included in the type of the conditional expression so that the column value having a small number of null values is disposed later than the column value having a large number of null values.

When the improved access path (FAP) is created by rearranging the column values and the array of the operators included in the type of the conditional statement by type, the server 100 transmits the improved access path FAP and the DBMS 135 (AAP) of the execution plan generated in the corresponding execution plan (310).

Here, the server 100 may compare the column values included in the improved access path (FAP) with the array of the operators and the column values included in the analysis access path (AAP) .

In addition, the server 100 can compare the arrangement of the column values included in the improvement access path (FAP) with the arrangement of the column values included in the analysis access path (AAP) to confirm that they match.

In addition, the server 100 compares an operator combined with a column value included in the improvement access path (FAP) and an operator combined with a column value included in the analysis access path (AAP) Or more.

If it is determined that the improvement access path (FAP) and the analysis access path (AAP) do not match, the server 100 may repeatedly perform the process of generating the improved access path (FAP).

On the other hand, if it is confirmed that the improvement access path FAP and the analysis access path AAP are matched, the server 100 terminates the process of generating the FAP (step 315) ) Is structured to be included as the analysis access path (AAP) of the execution plan and applied to the index information of the DBMS 135 (320).

The server 100 checks whether the predetermined period of time has elapsed since the application of the index information (325). If the predetermined period has elapsed, the server 100 schedules 330 to perform the improved access path generation process again.

100: server 105: meta information collecting unit
110: execution plan collecting unit 115: access path reading unit
120: access path generating unit 125: matching check unit
130: structured application unit 135: DBMS
140: terminal

Claims (16)

1. A method executed via a server accessible to a Database Management System (DBMS) comprising one or more tables, index information and meta information,
A first step of collecting meta information of a DBMS to optimize an index;
M (M > = 1) terminals collects N (N > = 1) query statements (QUERY) transmitted to the DBMS and processed for a specific table among the tables of the DBMS, analyzes the query statement in the DBMS, A second step of collecting an execution plan (PLAN) that is generated in order to generate the execution plan (PLAN);
The type of the conditional clause including the raw access path (RAP) in which the column value and the operator are arranged by interpreting the collected query statement by type, analyzing the classified query syntax of each type, A third step of reading an analyzed access path (AAP) including an array of operators and column values included in the classified execution plan of the classified execution plan;
A column value included in the type of the conditional expression and an array of operators based on one or more pieces of reference information including the information included in the collected meta information or the information included in the meta information, A fourth step of generating a re-designed access path (FAP) rearranged by type;
A fifth step of comparing the redesigned improvement access path (FAP) and the analysis access path (AAP) of the execution plan generated in the DBMS by type to check if they are matched; And
Wherein said inconsistent improvement approach path (FAP) is structured to be included as an analysis access path (AAP) of said execution plan when said improvement access path (FAP) and said analysis access path (AAP) The method comprising the steps of: a.
The method according to claim 1,
Wherein the table comprises a plurality of columns,
The index information may include at least one of a column access path (AP) set by a developer who designed the column structure of each column included in the table and a column access path (AP) generated through a previous optimization process And an access path to the database index.
The method of claim 1,
The column structure of the table, the number of rows of each column, the number of null values of each column, the attribute information of each column, the degree of inheritance of each column statistically processed without duplication of information recorded in each column, Wherein the database index design information includes at least one set index information.
4. The method according to claim 1 or 3,
Further comprising the step of collecting table related information about a table to be optimized in the index among the tables provided in the DBMS.
4. The method according to claim 1 or 3,
And collecting the table-related information about the table to be optimized in the index among the tables provided in the DBMS and the meta information of the table.
The method according to claim 1,
Wherein the reference information includes an intrinsic value for each column in which information recorded in each column is statistically processed without being duplicated,
Wherein the fourth step includes rearranging the column values having higher intrinsic intensities of the columns among the two or more column values included in the type of the conditional expression so as to preferentially arrange the column values having lower intrinsic intensities Automatic optimization of database index design.
The method according to claim 1,
Wherein the reference information includes an intrinsic value for each column in which information recorded in each column is statistically processed without being duplicated,
Wherein the fourth step includes rearranging a column value having a low intrinsic value of each column among the two or more column values included in the type of the conditional rule so as to be later than a column value having a high intrinsic value Automatic optimization of database index design.
The method according to claim 1,
Wherein the reference information includes an intrinsic value for each column in which information recorded in each column is statistically processed without being duplicated,
Wherein the fourth step includes excluding column values of the two or more column values included in the type of the conditional expression, the column values having a certain degree or less of the inherent degree.
The method according to claim 1,
The fourth step includes rearranging the column values combined with the '=' operator among the combinations of two or more column values and operators included in the type of the conditional rule such that the column values are prioritized over column values combined with other operators Wherein the database index design is automatically optimized.
The method according to claim 1,
Wherein the reference information includes a number of null values of each column,
Wherein the fourth step includes rearranging a column value having a larger number of null values among the two or more column values included in the type of the conditional expression so that the column value is arranged prior to the column value having a smaller number of null values. Automatic optimization method of.
The method according to claim 1,
Wherein the reference information includes a number of null values of each column,
Wherein the fourth step includes rearranging a column value having a small number of null values of at least two of the column values included in the type of the conditional rule so as to be later than a column value having a large number of null values. Automatic optimization method of.
The method as claimed in claim 1,
And comparing the column value and the array of operators included in the improvement access path (FAP) with the array of the operator and the column value included in the analysis access path (AAP) Automatic optimization method of design.
The method as claimed in claim 1,
And comparing the array of column values included in the improvement access path (FAP) with the array of column values included in the analysis access path (AAP) to confirm whether or not they are matched. Way.
14. The method of claim 13,
Comparing an operator combined with a column value included in the improvement access path (FAP) and an operator combined with a column value included in the analysis access path (AAP) and checking whether the operator matches a predetermined ratio or more within an allowable range Wherein the database index design is automatically optimized.
The method as claimed in claim 1,
Further comprising the step of repeating the first through sixth steps when the improvement access path (FAP) and the analysis access path (AAP) do not match each other. .
The method as claimed in claim 1,
If the improvement access path FAP and the analysis access path AAP are matched, the process of generating the improvement access path FAP is stopped and the first through sixth steps are performed again after a predetermined period of time has elapsed The method of claim 1, further comprising the step of:

Images