JP5320787B2 - Search key optimization apparatus and search key optimization program - Google Patents

Description

  The present invention relates to a search key optimizing device and a search key optimizing program, and more particularly to a search key optimizing device and a search key optimizing program for optimizing a search key used by a program logic for searching a database.

In recent years, a system (program logic) using a database (DB) has been designed. Normally, an index (search key) is generated at the time of designing the program logic for the purpose of high-speed access to the DB. Conventionally, an index is generated from, for example, program logic (see, for example, Patent Documents 1 and 2).
JP-T-2001-527243 JP-A-4-273534

  However, when generating an index from the program logic, the index is generated on the premise of the program logic. Therefore, if the program logic itself has a problem in response quality (in terms of search efficiency) at the time of DB access, a response at the time of DB access An index that ignores quality was sometimes generated.

  Therefore, in order to ensure the response quality at the time of DB access, the program logic designer must analyze the program logic and generate an optimal index to ensure the response quality at the time of DB access. There is a problem that the amount of work of the worker increases.

  An embodiment of the present invention has been made in view of the above points. A search key optimization device and a search key optimization program capable of ensuring response quality at the time of database access without increasing the amount of work of a designer are provided. The purpose is to provide.

In order to solve the above-described problem, an embodiment of the present invention is a search key optimizing device for optimizing a search key used by a program logic that operates a database to search the database. A first search key extracting means for extracting one or more search keys to be used for searching the database, and one search key to be used for searching the database from a database definition body in which already defined search keys are defined. Using the second search key extracting means to be extracted as described above, and priority information in which the priority is set for each item of the database constituting the search key according to the degree of narrowing of records when used for search use to perform the suitability check of the search key extracted by the first retrieval key extraction means, the search in a single Was in the order of small item from the degree is large item options in record when that determines a proper check was successful when an item of the database constituting the retrieval key are arranged, suitability check is normally ended Search key validity checking means for updating the database definition body with the search key.

  In addition, what applied the component, the expression, or arbitrary combinations of the component of one Embodiment of this invention to a method, an apparatus, a system, a computer program, a recording medium, a data structure, etc. is also effective as an aspect of this invention. .

  According to an embodiment of the present invention, it is possible to provide a search key optimization device and a search key optimization program that can ensure response quality when accessing a database without increasing the amount of work of a designer.

  Next, the best mode for carrying out the present invention will be described based on the following embodiments with reference to the drawings. In this embodiment, RDB (relational database) will be described as an example of the database. RDB is one data management method in which one record is expressed as an aggregate of a plurality of items (columns) and a set of data is represented by a table called a table. In RDB, records can be easily searched by using items such as personal numbers and names as indexes (search keys). In RDB, a multi-column index (composite item search key) composed of a plurality of items is also used as an index. For the operation of RDB, for example, a language called SQL is generally used.

  In a system (program logic) using RDB, a designer usually defines an index in a database definition body at the time of designing. However, it often happens that there is a difference between the design speculation and the actual program logic.

  In this embodiment, by analyzing the actual program logic and automatically generating the optimal index, it is possible to fill the index gap caused by the difference between the design logic and the actual program logic. .

  FIG. 1 is a hardware configuration diagram of the search key optimizing apparatus according to the present embodiment. The search key optimizing device is composed of an input device 1, an output device 2, a drive device 3, an auxiliary storage device 4, a main storage device 5, an arithmetic processing device 6 and an interface device 7 which are mutually connected by a bus B. The

  The input device 1 includes a keyboard, a mouse, and the like, and is used for inputting various signals. The output device 2 includes a display device and is used to display various windows and data. The interface device 7 includes a modem, a LAN card, and the like, and is used for connecting to a network.

  The search key optimization program of this embodiment is at least a part of various programs for controlling the search key optimization device. The search key optimization program is provided by, for example, distribution of the recording medium 8 or downloading from a network. The recording medium 8 on which the search key optimization program is recorded is a recording medium such as a CD-ROM, a flexible disk, a magneto-optical disk, etc. for recording information optically, electrically or magnetically, a ROM, a flash memory, etc. Various types of recording media such as a semiconductor memory that electrically records information can be used.

  When the recording medium 8 on which the search key optimization program is recorded is set in the drive device 3, the search key optimization program is installed from the recording medium 8 to the auxiliary storage device 4 via the drive device 3. The search key optimization program downloaded from the network is installed in the auxiliary storage device 4 via the interface device 7.

  The auxiliary storage device 4 stores the installed search key optimization program and also stores necessary files, data, and the like. The main storage device 5 reads and stores the search key optimization program from the auxiliary storage device 4 at the time of activation. The arithmetic processing unit 6 implements various processes as will be described later in accordance with a search key optimization program stored in the main storage device 5.

  FIG. 2 is a flowchart showing the processing procedure of the search key optimizing apparatus of this embodiment. The search key optimizing device includes a processing logic (program) 10 for extracting key information from program logic, a processing logic 11 for extracting key information from a database definition body, a processing logic 12 for setting a matching rate when searching, and extraction. Processing logic 13 for checking excess / deficiency from the key information, processing logic 14 for checking the appropriateness of the extracted key information, processing logic 15 for updating / re-registering the database definition body, and program logic review / re-registration to the designer This is a configuration having processing logic 16 to be performed. Execution of the processing logics 10 to 16 is controlled by, for example, JCL (Job Control Language).

  Processing logic 10 that extracts key information from the program logic reads the program logic 21. The processing logic 10 extracts candidate index information 23 as index candidates from the program logic 21. FIG. 3 is an explanatory diagram showing a process of extracting candidate index information that becomes index candidates from the program logic.

  The processing logic 10 extracts index candidate information 23 as index candidates from the description of a database access language such as SQL included in the program logic 21 in the following procedure.

  First, the processing logic 10 extracts descriptions of “EXEC SQL” to “END-EXEC” from the program logic 21 as instructions related to the database. If “DECLARE” and “CURSOR” are included in the extracted description, the processing logic 10 extracts them as index candidates.

  The processing logic 10 extracts the extracted description “WHERE” and after as a search condition, and generates candidate index information 23 from the search condition. “WHERE” specifies a search condition. The search condition represents an index composed of one or more items used for the search and a search method such as a forward match or a complete match. The candidate index is composed of one or more items.

  The processing logic 10 sets the order of the items in the candidate index information 23 in the description order, and warns the designer with the output device 2 or the like when the condition setting priority is in doubt. The condition setting priority order is =, <>, BETWEEN, forward match,. In this embodiment, the condition setting priorities after the forward match are not usable in principle.

  The processing logic 10 extracts “ORDER BY” in the extracted description and extracts it as supplementary information when there is data rearrangement. If there is data rearrangement other than the items in the candidate index information 23, the processing logic 10 may warn the designer with the output device 2 or the like.

  Processing logic 11 that extracts key information from the database definition body extracts index information that has already been defined from the database definition body 22. The processing logic 11 extracts index information for each table, and extracts items constituting the index and whether or not key duplication is permitted as set index information 24 for each index.

  The processing logic 12 for setting the search match rate sets the search match rate as shown in FIG. FIG. 4 is a flowchart of processing for setting the matching rate at the time of search. The processing logic 12 that sets the search match rate proceeds to step S1 and extracts index information that has already been defined from the database definition body 22. The processing logic 12 extracts index information for each table, and extracts the items constituting the index and the presence / absence of key duplication permission as set index information 24 for each index.

  In step S <b> 2, the processing logic 12 extracts a data distribution situation 31 for each composite key configuration candidate item 30 specified for each table from the data stored in the database 29. Here, the process of step S2 will be described with reference to FIG. FIG. 5 is a configuration diagram showing a data distribution situation for each composite key configuration candidate item.

  For example, the processing logic 12 confirms the number of stored records (all cases) for each table from the data stored in the database 29 using a utility. In FIG. 5, the number is 300,000. The processing logic 12 sets a search key (range) for each item in order to measure variation for each composite key configuration candidate item, confirms the number of records within the set key range, and sets the average number as the number of cases. .

  For example, in FIG. 5, the composite key composition candidate item “birth date” has several patterns such as “January 1, 2007”, “January 1, 1939”, “January 1, 1959”, etc. A search key is set, and “9”, which is the average number of search results, such as “10”, “8”, “5”, which are search results, is set as the corresponding number.

  The processing logic 12 determines variations “large”, “medium”, and “small” based on the number of cases based on operability using the number of screen displayable items as an index. For example, in FIG. 5, when the number of displayable screens is set to 20, the number of search results 20 or less that can display all search results without a screen operation is regarded as “small”. In addition, in FIG. 5, when the number of displayable screens is 20, the search results can be displayed with 1 to 4 screen operations, and 100 or less results can be displayed as “medium”, and the search is performed with 5 or more screen operations. Over 100 items that can display all results are regarded as “large”.

  Proceeding to step S3, the processing logic 12 sets a search-time matching rate from the set index information 24, the data distribution status 31 for each composite key configuration candidate item, and the composite key configuration candidate business characteristic 32. The composite key composition candidate business characteristic 32 stipulates whether the composite key composition candidate item is often used for complete business or used for front coincidence.

  For example, in FIG. 5, the composite key composition candidate item “name kana (name)” matches forward, the composite key composition candidate item “birth date” matches exactly, and the composite key composition candidate item “gender” is only for sorting after the search. Use, etc. are stipulated.

  The processing logic 12 sets the search match rate 25 in the priority order of the search methods “complete match> forward match> ambiguous search” and variation “small> medium> large” defined in the composite key composition candidate business characteristic 32. To do. The search match rate 25 refers to the priority for each composite key configuration candidate item. For example, in this embodiment, the priority order for each composite key configuration candidate item is set with a two-digit number. The composite key composition candidate item that does not allow duplication is set with a priority of “1x” (x: 0 to 9). The composite key composition candidate item used for complete matching is set with a priority of “2x” (x: 0 to 9). The composite key composition candidate item used for the forward match is set with a priority of “3x” (x: 0 to 9). The composite key composition candidate item that is not used as a search key is set with a priority of “9x” (x: 0 to 9).

  For example, an example composite key composition candidate item “personal number: composite key composition candidate item that does not allow duplication” for specifying an individual has a priority set to “10”. “Address: composite key composition candidate item used for complete match, small variation” has the priority set to “21”. “Birth date: Compound key configuration candidate item used for perfect match, small variation” has a priority set to “22”. “Name: composite key composition candidate item used for forward matching, large variation” has the priority set to “31”. “Gender: Compound key composition candidate item not used as search key” has a priority set to “90”.

  The smaller the 2-digit number that sets the priority, the higher the priority. In FIG. 5, the composite key composition candidate item having a smaller average number (small variation) is set with a higher priority.

  The processing logic 13 for checking the excess / deficiency from the extracted key information and the processing logic 14 for checking the appropriateness of the extracted key information are based on the above-described candidate index information 23, the set index information 24 and the matching rate 25 at the time of search. Perform multi-column index optimization.

  The processing logic 13 that performs the excess / deficiency check from the extracted key information matches the set index information 24 to check the excess / deficiency of the candidate index information 23 to obtain candidate index information 26.

  As will be described later, the processing logic 14 that performs the appropriateness check of the extracted key information performs the appropriateness check of the candidate index information 26 based on the candidate index information 26, the set index information 24, and the search match rate 25.

  For example, the appropriateness check of the candidate index information 26 is performed by matching the set index information 24 with the candidate index information 26 and using the search matching rate 25 to determine whether there is an index to be added. Check if there is an index that never happens.

  The processing logic 14 that performs the appropriateness check of the extracted key information updates the candidate index information 26 to the appropriate candidate index information 27 based on the result of the appropriateness check. Further, the processing logic 14 extracts the contents 28 to be reviewed by the program logic 21 based on the result of the appropriateness check.

  Here, the appropriateness check of the candidate index information 26 performed by the processing logic 14 will be described with a specific example. FIG. 6 is an explanatory diagram showing an example of the set index information, candidate index information, and search time matching rate.

  The set index information 61 is an example in which the items “personal number”, “address”, “name”, and “birth date” are already defined as index information. Further, the candidate index information 62 is an example in which six index candidates composed of combinations of the items “personal number”, “address”, “name”, and “birth date” are extracted from the program logic 21.

  The matching rate 63 at the time of retrieval includes items “personal number”, “address”, “birth date”, “name”, “gender” in priority order “10”, “21”, “22”, “31”, “ In this example, “90” is set. The following description will be made on the assumption of the set index information 61, the candidate index information 62 and the search match rate 63 of FIG.

  The processing logic 14 encodes the items of the set index information 61 and the candidate index information 62 as the set index information 64 and the candidate index information 65 of FIG. FIG. 7 is an explanatory diagram showing the set index information and candidate index information after encoding. In the items of the set index information 64 and the candidate index information 65 in FIG. 7, a two-digit number representing the same priority order as the priority order set in the search match rate 63 is set.

  For each of the six index candidates in the candidate index information 65, the processing logic 14 rearranges the order of the items in ascending order of two-digit numbers representing the priority order as the candidate index 66 in FIG. FIG. 8 is an explanatory diagram showing candidate index information after rearranging items based on priority.

  The processing logic 14 compares the order of the items for each of the six index candidates of the candidate index information 65 in FIG. 7 and the candidate index information 66 in FIG. 8 and checks a two-digit number representing the priority order. Check for appropriateness.

  The index candidate a) of the candidate index information 65 in FIG. 7 is excluded from the index candidates because the order of the items is different. The processing logic 14 extracts the order “birth date” and “name” of the items of the index candidates a) as the contents 28 to be reviewed by the program logic 21.

  The index candidate b) of the candidate index information 65 in FIG. 7 is left as an index candidate because the order of the items matches. The index candidate c) of the candidate index information 65 in FIG. 7 uses items “gender” set with a two-digit number “90” representing a priority order not used as a search key. “Gender” is removed. The processing logic 14 extracts, as the content 28 to be reviewed by the program logic 21, the exclusion of the item “gender” of the index candidate c) and the gender determination in the content check after the search.

  The index candidates d) of the candidate index information 65 in FIG. 7 are left as index candidates because the order of the items matches. The index candidate e) of the candidate index information 65 in FIG. 7 is excluded from the index candidates because the order of the items is different. The processing logic 14 extracts the order “address”, “date of birth”, and “name” of the items of the index candidates e) as the contents 28 to be reviewed by the program logic 21.

  Further, the index candidate f) of the candidate index information 65 in FIG. 7 includes an item “personal number” in which a two-digit number “10” representing a priority order not allowing duplication with other items is set. Since it is used redundantly with the item “name”, it is removed from the index candidates. The processing logic 14 extracts the possibility that there is a fundamental problem in the specification as the contents 28 to be reviewed by the program logic 21.

  The candidate index information 66 in FIG. 8 becomes the candidate index 67 in FIG. 9 by the suitability check by the processing logic 14. FIG. 9 is an explanatory diagram showing candidate indexes after the appropriateness check. The candidate index information 67 in FIG. 9 is absorbed by the index candidate a) because the index candidate d) is the same as the index candidate a).

  The processing logic 14 matches the set index information 64 with the candidate index information 67, and determines additional candidate indexes such as the candidate index information 68 and the set index information 69 in FIG. FIG. 10 is an explanatory diagram of processing for determining an index of an additional candidate.

  The processing logic 14 rearranges the five index candidates of the candidate index information 67 in the priority order set for the items as the candidate index information 68. Further, the processing logic 14 rearranges the indexes defined in the set index information 64 in the priority order set for the items, as the set index information 69. When there are multiple indexes that have the same item (first configuration item) constituting the top of the index, the index is rearranged in ascending order of the number of items constituting the index (number of configuration items), and the number of configuration items is the same. Sort by the priority set in the item.

  The processing logic 14 determines an index in the candidate index information 68 that is not in the set index information 69 as an additional candidate index. FIG. 10 is an example in which index candidate b), index candidate e) and index candidate a) are determined as additional candidate indexes.

  The processing logic 14 generates the scheduled setting index information 70 as shown in FIG. 11 using the index candidate a), the index candidate b) and the index candidate e) as additional candidate indexes. FIG. 11 is an explanatory diagram showing an example of setting schedule index information.

  The processing logic 14 determines whether or not the index of the setting schedule index information 70 may be actually used. Index a) and index g) are continuously used because there is no other index that overlaps the top item. Further, since any one of the index b), index c), and index d) is used with the item “address” as the first constituent item, the index d) determined to be the most effective in the matching rate at the time of retrieval. And index b) and index c) are deleted.

  In addition, as index e) and index f), only one index with the item “birth date” as the first constituent item is used, and therefore index f) determined to be the most effective in the matching rate at the time of search is adopted. And delete the index e).

  When items other than the head configuration item are disjoint within the same index of the first configuration item, a new index (an index covering all items) is created based on the matching rate at the time of search.

  The processing logic 15 for updating / re-registering the database definition body updates / re-registers the database definition body 22 by using the appropriate candidate index information 27 updated by the processing logic 14. Further, the processing logic 16 that causes the designer to review / re-register the program logic designs the review / re-registration of the program logic 21 by using the contents 28 to be reviewed of the program logic 21 extracted by the processing logic 14. A warning for the person to perform is displayed on the output device 2 or the like.

  As described above, according to this embodiment, an index (database definition body 22) can be generated from the program logic 21 while being conscious of the response quality at the time of DB access, and the database access can be performed without increasing the amount of work for the designer. The response quality at the time can be secured.

The present invention may have the following configurations as described below.
(Appendix 1)
A search key optimizing device for optimizing a search key used by a program logic for operating a database to search the database,
First search key extracting means for extracting one or more search keys used for searching the database from the program logic;
Second search key extraction means for extracting one or more search keys used for searching the database from a database definition body in which search keys that have already been defined are defined;
The search key extracted by the first search key extraction means using priority order information in which the priority order for use in the search is set for each item of the database constituting the search key A search key optimizing device comprising: a search key adequacy check unit that performs an aptitude check and updates the database definition body with the search key for which the aptitude check has been normally completed.
(Appendix 2)
In the priority information, for each item of the database constituting the search key, the degree of narrowing of records when used for search is set,
The search key appropriateness check means is appropriate when the items of the database constituting the search key are arranged in the order of items from a large item to a small item when a single record is used for search. The search key optimizing device according to supplementary note 1, wherein it is determined that the check is normally completed.
(Appendix 3)
The priority information further includes, among the items of the database constituting the search key, the item for uniquely narrowing down records when used for search, and the item not used for search,
The search key appropriateness check means extracts, as an inappropriate search key, the search key configured to include the item for uniquely narrowing down records when used for search and the item not used for search. The search key optimizing device according to appendix 2.
(Appendix 4)
The search key appropriateness check means displays the search key extracted as an inappropriate search key on a display means, and displays the program logic as an inappropriate program logic on the display means. Device.
(Appendix 5)
The search key appropriateness check means extracts, from the plurality of search keys, the search key having the same first item among the items of the database constituting the search key, and the priority order information from the extracted search key The search key optimizing device according to appendix 1, wherein the search key other than the search key with the highest priority is deleted based on the above.
(Appendix 6)
A search key optimization device for optimizing a search key used by a program logic for operating a database to search the database,
First search key extracting means for extracting one or more search keys used for searching the database from the program logic;
Second search key extraction means for extracting one or more search keys used for searching the database from a database definition body in which search keys that have already been defined are defined;
The search key extracted by the first search key extraction means using priority order information in which the priority order for use in the search is set for each item of the database constituting the search key A search key optimizing program for functioning as a search key adequacy check means for updating the database definition body with the search key for which aptitude check has been normally completed.

  The present invention is not limited to the specifically disclosed embodiments, and various modifications and changes can be made without departing from the scope of the claims. Note that the search time matching rate 25 may be manually set by the designer based on past experience.

It is a hardware block diagram of the search key optimization apparatus of a present Example. It is a flowchart showing the process sequence of the search key optimization apparatus of a present Example. It is explanatory drawing showing the process which extracts the candidate index information used as the index candidate from a program logic. It is a flowchart of the process which sets the coincidence rate at the time of a search. It is a block diagram showing the data distribution situation for every composite key structure candidate item. It is explanatory drawing which shows an example of set index information, candidate index information, and a search time matching rate. It is explanatory drawing which shows the set index information and candidate index information after encoding. It is explanatory drawing which shows the candidate index information after the rearrangement of the item based on a priority. It is explanatory drawing which shows the candidate index after an appropriateness check. It is explanatory drawing of the process which determines the index of an addition candidate. It is explanatory drawing which shows an example of setting schedule index information.

Explanation of symbols

DESCRIPTION OF SYMBOLS 1 Input device 2 Output device 3 Drive device 4 Auxiliary storage device 5 Main storage device 6 Arithmetic processing device 7 Interface device 8 Recording medium 10 Processing logic for extracting key information from program logic 11 Extracting key information from database definition Processing logic 12 Processing logic for setting matching rate at search 13 Processing logic for checking excess / deficiency from extracted key information 14 Processing logic for checking appropriateness of extracted key information 15 Processing to update / re-register database definition body Logic 16 Processing logic that causes the designer to review / re-register program logic 21 Program logic 22 Database definition body 23, 26, 27 Candidate index information 24 Set index information 25 Matching rate at search 28 Look at program logic Data distribution of should do contents 29 database 30 composite key configuration candidate 31 each composite key configuration candidate 32. composite key candidate configurations business characteristics

Claims (4)

A search key optimizing device for optimizing a search key used by a program logic for operating a database to search the database,
First search key extracting means for extracting one or more search keys used for searching the database from the program logic;
Second search key extraction means for extracting one or more search keys used for searching the database from a database definition body in which search keys that have already been defined are defined;
For each item of the database that constitutes the search key, the first search key extraction means uses priority information in which priority is set according to the degree of narrowing of records when used for search. Appropriateness of the extracted search key is checked, and the items of the database constituting the search key are arranged in the order from the item with the highest degree of narrowing down the records when used in a single search. A search key optimizing device comprising: a search key adequacy check unit that determines that the appropriateness check has been normally completed when it is in progress and updates the database definition body with the search key for which the appropriateness check has been normally completed. The priority information further includes, among the items of the database constituting the search key, the item for uniquely narrowing down records when used for search, and the item not used for search,
The search key appropriateness check means extracts, as an inappropriate search key, the search key configured to include the item for uniquely narrowing down records when used for search and the item not used for search. The search key optimizing device according to claim 1 . The search key proper check means, and displays on the display means the search key extracted as inappropriate search key, the search key according to claim 2, wherein the displaying on said display means the program logic as Inappropriate program logic Optimization device. A search key optimization device for optimizing a search key used by a program logic for operating a database to search the database,
First search key extracting means for extracting one or more search keys used for searching the database from the program logic;
Second search key extraction means for extracting one or more search keys used for searching the database from a database definition body in which search keys that have already been defined are defined;
For each item of the database that constitutes the search key, the first search key extraction means uses priority information in which priority is set according to the degree of narrowing of records when used for search. Appropriateness of the extracted search key is checked, and the items of the database constituting the search key are arranged in the order from the item with the highest degree of narrowing down the records when used in a single search. A search key optimizing program for determining that the appropriateness check has been completed normally when it is present and functioning as a search key adequacy check means for updating the database definition with the search key for which the appropriateness check has been completed normally.

Images