JP5061173B2 - Database management method, database management apparatus, and database management program - Google Patents

Description

  The present invention relates to a database management apparatus having a database that performs a search using an index, and more particularly to a database management method, a database management apparatus, and a database related to a technique for selecting an index in response to access requests from a plurality of user applications. It is suitable for a management program.

  With the development of the information society, the amount of data handled by information systems is increasing day by day, and many companies use database management systems (DBMS) for information systems. A DBMS is a system that responds to an inquiry about data in a database.

  In the information system, a plurality of user application programs (User Application Programs) are operating. Hereinafter, such user application programs are also abbreviated as “UAP” and “user application”. The user application is also called “AP” or the like. The user application acquires data from the database by making an inquiry to the DBMS, and provides a service using the acquired data.

  In DBMS, SQL is often used as an inquiry language for describing an inquiry about data. The user application makes an inquiry by issuing an SQL to the DBMS. As the SQL issued by the user application, there is a static SQL described directly in the user application and a dynamic SQL generated by the user application.

  SQL is a declarative query language, and the target data and data conditions are specified in the query. The procedure for the DBMS to access the database to obtain the result of the inquiry is generated by the DBMS itself. Hereinafter, such a DBMS access procedure is referred to as an “access path”. The access path is also called various names such as a query plan, an access plan, or a query execution plan.

  In recent years, in the development of user applications, there is a tendency that much time and cost are required for tuning the query performance of DBMS. As competition between companies intensifies and the cost awareness of companies with respect to information systems is increasing, there is a need for shortening the development period of user applications and reducing development costs. Therefore, it is necessary to reduce the time and cost required for tuning the query performance to the DBMS in the development of the user application.

  One of the reasons why tuning of the query performance to the DBMS is necessary is that the access path generated by the DBMS is not necessarily an access path with good performance when obtaining the query result. Therefore, the user application developer needs to verify the access path and tune the access path.

  When the DBMS generates an access path, the presence of the index has an effect. An index is a means for accessing specific data in a database at high speed. When the query result is obtained using the index when there is an index for the data that needs to be accessed in order to obtain the query result, the query result is compared with the case where the index is not used. Therefore, the DBMS generates an access path using an index. Therefore, the user application developer can create an index to intervene in determining the access path and perform tuning so that the access path has a better performance when obtaining a query result.

  As a specific example, an index for DBMS data corresponds to, for example, an index in a book. In the case of a book without an index, all pages must be read in order to find target information in the text, which is inefficient. However, if there is an index corresponding to the heading of each chapter, it is possible to limit the number of pages that must be read in order to search for the target information in the text, and the target index is more efficient than a book without an index. Search for information. Furthermore, if there is an index for the keyword included in the text, the target page can be found directly by searching for the keyword from the index, and the target information can be searched more efficiently. As described above, in the DBMS as well as the index in the book, the query result can be efficiently obtained by accessing the database using the index.

  Multiple indexes exist in the database. Since indexes can be created for specific data and combinations of data, a plurality of indexes may exist for one data. When the DBMS generates an access path, an index that is determined to have the best performance when obtaining a query result is selected from all indexes existing in the database (see Non-Patent Document 1).

  When creating an access path, logically add, change, or delete an index by selecting the index that has been determined to have the best performance when obtaining query results from all the indexes that exist in the database. Even when a query is performed, the system can always generate an access path by selecting an index considered to have the best performance when obtaining a query result.

"Database Systems: The Complete Book", 2002, Page 757 to 764, Hector Garcia-Molina, Jeffrey D. Ullman and Jennifer Widom, Prentice-Hall, Inc.

  However, in the conventional DBMS, when selecting an index that is judged to have the best performance when obtaining a query result, the query result is actually obtained using the index, and the performance when each index is actually used is compared. I do not mean. Actually, the conventional DBMS analyzes the inputted SQL, compares the SQL ascertained from the analysis result and the existing index, and inquires at that time, that is, at the time of actually using each index. Select the index that is judged to have the best performance when obtaining the results. For this reason, in the conventional DBMS, depending on the contents of the inquiry and the characteristics of the data, an index that cannot be said to have the best performance when obtaining the inquiry result may be selected.

  The reason for this will be described with reference to the above-described example of the book index. Consider a case where there are two indexes for a book: an index for a keyword in the text and an index for a person name in the text. For example, when the target information is information related to “Beethoven”, the search can be performed using either the index for the keyword or the index for the person name. However, if the index for the person name has fewer items than the index for the keyword, the content of the query “Beethoven” is the person name, so it is more efficient to use the index for the person name. You can search for the information you want. In this way, if it is known that the inquiry is a person name and it is found that there is an index focused on the person name, an index with the best performance for searching for the target information “Beethoven” can be selected. It becomes possible.

  In this regard, the DBMS cannot determine whether the target data specified by SQL and the data condition are person names. Therefore, when an index for a keyword and an index for a person name exist, which index is selected depends on the index selection algorithm of the DBMS itself.

  In this way, the index in the database is created by a developer of a certain user application in order to improve the performance of the user application. However, even if the index improves the performance for a specific user application, other user applications However, the performance is not always improved. That is, for each user application, there is the best index from the viewpoint of performance.

  For example, consider a case where a first user application is first developed using one DBMS, and then a second user application is developed. First, when developing the first user application, the access path is tuned, and the access path that the developer of the first user application has determined that the performance when obtaining the query result is the best in order to satisfy the performance requirement. Create a first index to work. At this time, since only the first index exists in the DBMS, this first index is always selected when the access path of the first user application is generated. Therefore, the developer of the first user application is operating on the access path that the first user application has actually determined to have the best performance when the developer of the first user application seeks the query result. Can be confirmed.

  After that, when developing the second user application, the access path generated using the existing first index cannot satisfy the performance requirement. Therefore, the second index is newly created. Tune the access path by adding. Then, there are two indexes in this DBMS. In the conventional DBMS, when the access path is generated, the selection is made from all the indexes existing at that time, so the first or second index is selected. Here, when the access path of the first user application is generated, the second index that is not intended by the developer is selected, and the performance may be reduced as compared with the case where the first index is selected.

  In other words, the index does not provide the best performance when obtaining a query result for all applications. An index having the best performance when obtaining a query result is required for each user application, such as a first index for the first user application and a second index for the second user application.

  However, in the conventional DBMS, if an index is added to tune the access path of a specific user application, the access path of another user application is affected. This is the same when the index is changed or deleted. Therefore, when adding, changing, or deleting an index, the developer checks the access path corresponding to all user applications, and if the access path changes, the developer evaluates the performance again. If there is a problem, it will be necessary to perform tuning. However, when the information system described above becomes large, a huge number of user applications are operating, so access paths corresponding to all user applications are confirmed, and performance is re-evaluated and tuned as described above. As a result, the development period is prolonged and the development cost is increased.

  The present invention has been made in consideration of the above points, and generates an access path with the best performance when obtaining a query result for each user application without affecting the access paths corresponding to other user applications. The present invention proposes a database management method, a database management apparatus, and a database management program that can freely add, change, or delete an index to be used.

To solve such problems, the present invention has a memory, in response to access requests from a plurality of user applications to a database having at least one table, index from a plurality of indexes set in the table In the database management method in the database management apparatus that operates the data in the table according to the access path generated by selecting the database , the database management apparatus is configured to determine the index group to be defined based on the plurality of first definition requests. Gets the index group name, to create a plurality of new indices groups by registering the index group in correspondence with the obtained index group name in the index group management table, based on the second definition request, defined The target index and the definition pair First index information including the table name of the table to which the column included in the index belongs, the column name of the column included in the index to be defined, and the index group to which the index should belong The index group name is acquired and stored in the memory, and the first index information is registered in the index management table for each index to be defined, and stored in the memory with reference to the index group management table An index group corresponding to the index group name being acquired is acquired, and the acquired index group and at least one index that should belong to the acquired index group are associated and registered in the index-index group correspondence table Each index group. At least one index that is judged to have the best performance when obtaining a query result when executing each of the user applications corresponding to each of the index groups, and indexing from any of the plurality of user applications Based on the association management file for managing the index group name associated with the user application that has issued the first definition request and has received the specified access request for the group, the user application that has received the access request A specific index group name associated with the index group management table is acquired, a specific index group corresponding to the acquired specific index group name is acquired with reference to the index group management table, and the index-index is acquired. First index information related to a specific index corresponding to the acquired specific index group based on the index group correspondence table is acquired and held in the memory, and the first index information held in the memory; According to the result of comparing the table name and the second index information including the column name acquired from the access request, an index suitable for use in access is determined, and the determined index is and generating an access path using.

Further, in the present invention, in response to an access request from a plurality of user applications for a database having a memory and having at least one table, the index is selected and generated from the plurality of indexes set in the table. In the database management device that operates the data of the table according to the access path thus obtained, the index group name of the index group to be defined is acquired based on the plurality of first definition requests, and the acquired index group name is and registering the index group to the index group management table to create a plurality of new indices groups in correspondence, based on the second definition request, included in the index are defined target, a defined subject index The table name of the table to which the column belongs and the definition target The first index information including the column names of the columns included in the index and the index group name of the index group to which the index should belong are acquired and held in the memory, and each index that is the definition target Registering the first index information in an index management table, referring to the index group management table, obtaining an index group corresponding to an index group name held in the memory, and obtaining the index group; Each user application corresponding to each index group is associated with each index group by associating at least one index to belong to the acquired index group and registering it in the index-index group correspondence table. Run At least one index that is judged to have the best performance when obtaining a query result sometimes belongs, accepts an access request designated by an index group from any of the plurality of user applications, and the first definition request Based on the association management file for managing the index group name associated with the user application that issued the index, a specific index group name associated with the user application that has accepted the access request is acquired, and the index group A specific index group corresponding to the acquired specific index group name is acquired with reference to a management table, and the specific index group corresponding to the acquired specific index group is acquired based on the index-index group correspondence table First index information related to a specific index is acquired and stored in the memory, and the first index information stored in the memory, and a second index including the table name and column name acquired from the access request An index suitable for use in access is determined according to a result of comparing the information with the information, and an access path is generated using the determined index.

In the present invention, selected to the database management system, in response to access requests from a plurality of user applications to a database having at least one table, the index from a plurality of indexes set in the table having a memory In the database management program for operating the data in the table according to the generated access path, the database management device acquires the index group name of the index group to be defined based on the plurality of first definition requests. the in correspondence to the obtained index group name registered in the index group management table to create a plurality of new indices groups the index group, based on the second definition request, and index is defined target, The definition target First index information including the table name of the table to which the column included in the column belongs, the column name of the column included in the index to be defined, and the index of the index group to which the index should belong The group name is acquired and stored in the memory, and the first index information is registered in the index management table for each index to be defined, and stored in the memory with reference to the index group management table. An index group corresponding to the index group name being acquired is acquired, and the acquired index group is associated with at least one index that should belong to the acquired index group and registered in the index-index group correspondence table. By placing each index group, Specify at least one index that is determined to have the best performance when obtaining a query result when executing each user application corresponding to the index group, and specify an index group from any of the plurality of user applications Is associated with the user application that has accepted the access request based on an association management file for managing an index group name associated with the user application that has issued the first definition request. Specific index group names are acquired, a specific index group corresponding to the acquired specific index group name is acquired with reference to the index group management table, and the index-index is also acquired. First index information related to a specific index corresponding to the acquired specific index group based on the acquired group index table is stored in the memory, and the first index information stored in the memory; in response to a second index information to the results against the containing table name and column names acquired from the access request, to determine the index suitable for use when accessing, the determined index It is characterized by generating an access path using it.

  According to the present invention, it is possible to freely add or change an index for generating an access path with the best performance when obtaining a query result for each user application without affecting an access path corresponding to another user application. Or it can be deleted.

It is a conceptual diagram which shows schematic structure of the database management apparatus by this Embodiment. It is a conceptual diagram which shows an index group. It is a functional block diagram which shows an example of a structure of a database management apparatus. It is a figure which shows an example of the column information which comprises an index group management table. It is a figure which shows an example of the index group definition request | requirement (SQL) for creating an index group. It is a figure which shows an example of the column information which comprises an index management table. It is a figure which shows an example of the column information which comprises an index-index group mapping table. It is a figure which shows an example of the index definition request | requirement (SQL) for creating an index. It is a flowchart which shows the procedure at the time of using an index group. It is a flowchart which shows the procedure of the process at the time of defining an index group. It is a flowchart which shows the procedure of the process at the time of defining a new index. It is a flowchart which shows the procedure of the process in the case of producing | generating an access path. It is a figure which shows an example of the output content when an access path is output. It is a flowchart which shows the procedure of the process in the case of changing an index definition. It is a flowchart which shows the procedure of the process in the case of deleting an index definition. It is a flowchart which shows the procedure in the case of changing matching with a user application and an index group. It is a flowchart which shows the procedure of the process in the case of creating the replication of an index group. It is a figure which shows an example of the index group replication request (SQL) for replicating an index group.

  1000 ... 1st user application, 1100 ... 2nd user application, 1200 ... Database management device, 1300 ... 1st index group, 1400 ... 2nd index group, 1800 ... 1st index 1900 ... 2nd index, 3000 ... User application, 3400 ... Database management device, 3410 ... Processor, 3421 ... Access path, 3431 ... Definition request section, 3432 ... DB access request reception section, 3433 ... Discretionary management section, 3434... Access path generation section, 3435... Access path execution section, 3436... Access path display section, 3530 ... Index group management table, 3520 ... Index management table, 3540. Index-Index Group correspondence table, 3550 ...... Table, 3560 ...... index.

  Hereinafter, an embodiment of the present invention will be described in detail with reference to the drawings.

(1) Schematic Configuration of Database Management Device According to this Embodiment FIG. 1 shows a schematic configuration of a database management device 1200 according to this embodiment. The database management device 1200 is a computer such as a workstation or a mainframe. In the present embodiment, the database management device 1200 is also illustrated as a DBMS (Database Management System).

  The database management device 1200 includes a processor, memory, cache, hard disk drive, and interface (not shown) as hardware resources. In the database management apparatus 1200, the processor implements the functions described later by executing the database management program read from the hard disk drive on the memory. In the database, a table for managing each data by column and row is created, and an index (to be described later) can be set in the column of each table. The index is a function corresponding to an index of a book set by the user so that a search process or the like can be easily performed when data is searched for each table in the database.

  In the present embodiment, a new concept “index group” is defined, and at least one index is grouped by belonging to the index group. Specifically, the index definition information 1500 used when generating the access path is grouped, rather than grouping the index entities themselves. The index definition information 1500 is information indicating the definition content of the created index. When a query (access request) is made from the user application, the database management program receives the query specified by the index group and obtains the query result from the indexes belonging to the index group. The index is determined to be good, and an access path is generated.

  The database management apparatus 1200 defines a first index group 1300 (hereinafter also referred to as “IDXGRP_A”) and a second index group 1400 (hereinafter also referred to as “IDXGRP_B”) as such index groups. The illustrated example shows an image of processing executed by the database management apparatus 1200 when the first user application 1000 (abbreviated as “UAP1”) associated with the first index group 1300 is executed. Show. In the illustrated example, the database management device 1200 executes processing when the second user application 1100 (abbreviated as “UAP2”) associated with the second index group 1400 is executed. The image is shown.

  The first index group 1300 belongs to an index for generating an access path with the best performance when obtaining a query result when the first user application 1000 is executed. In the first index group 1300, the first index 1800 exists as one of the indexes to which the first index group 1300 belongs.

  The second index group 1400 belongs to an index for generating an access path with the best performance when obtaining a query result when the second user application 1100 is executed. In the second index group 1400, a second index 1900 exists as one of the indexes to which the second index group 1400 belongs.

  First, a case where the first user application 1000 is executed will be described. When generating an access path, the database management program selects an index that is determined to have the best performance when obtaining a query result from the indexes included in the first index group 1300 in the index definition information 1500 described above. To do. In the first index group 1300, only the index defined when tuning is performed when the first user application 1000 is developed exists, and the first user group 1000, as intended by the developer of the first user application 1000, One index 1800 (corresponding to IDX_A in the figure) is selected.

  The database management program generates an access path 1600 for the first user application 1000 that uses the first index 1800. Thereafter, the database management program executes this access path 1600 to actually access the database. Two operations are shown in the access path 1600 of the first user application 1000, and the first and second operations are sequentially executed as these two operations.

  In the first operation, the database management program accesses the first index 1800 selected when the access path is generated. Specifically, as the first operation, the database management program accesses the first index 1800 (IDX_A) and acquires data that meets the conditions of the name and the belonging code. On the other hand, in the second operation, the database management program accesses the employee table 2000 to acquire the employee code corresponding to the data acquired in the first operation, and the execution result for the query of the first user application 1000 2100 is determined.

  Next, a case where the second user application 1100 is executed will be described. In this case, when generating the access path, the database management program determines that the performance for obtaining the query result is the best from the indexes included in the second index group 1400 in the index definition information 1500. Select. In the second index group 1400, only the index defined when tuning is performed when the second user application 1100 is being developed exists, as the developer of the second user application 1100 intends. , The second index 1900 (corresponding to IDX_B shown in the figure) is selected.

  The database management program generates an access path 1700 for the second user application 1100 that uses the second index 1900. Thereafter, the database management program executes this access path 1700 to actually access the database. One operation (third operation) is shown in the access path 1700 of the second user application 1100, and the third operation is executed. In the third operation, the database management program accesses the second index 1900 selected when the access path is generated, calculates the grouping for each affiliation code, and counts the number of rows. As described above, the database management program obtains the result 2200 for the query of the second user application 1100.

  Next, a comparative example for the present embodiment will be described. In this comparative example, when the access paths of the first and second user applications 1000 and 1100 are generated, it is determined that the performance for obtaining the query result is the best among all the indexes of the index definition information 1500. Select an index. In this comparative example, there are indexes other than the indexes defined when tuning is performed during the development of each user application. However, the index does not provide the best performance for query results for all user applications. The first user application 1000 needs an index having the best performance when obtaining a query result for each user application, such as a first index 1800 and a second user application 1100 a second index 1900. It becomes. Therefore, for example, when the first user application 1000 is executed, the second index 1900 that is not intended by the developer of the first user application 1000 may be selected to generate an access path. Then, since the procedure for accessing the database (corresponding to the access path) actually changes, the first user application 1000 does not operate at the performance intended by the developer, and the performance is degraded.

  As described above, in the present embodiment, the index to be selected is compared with the case of selecting the index that is determined to have the best performance when obtaining the query result from all the indexes as in the prior art. It becomes possible to limit. Therefore, according to the present embodiment, an index for generating an access path with the best performance when obtaining a query result for each user application can be freely added without affecting the access paths of other user applications. , Can be changed or deleted. As a result, according to the present embodiment, when a user application is developed, it is possible to reduce the man-hours required for confirming the influence on the access path of another user application. The concept of the present embodiment is as described above. Next, a more detailed concept will be described.

  FIG. 2 is a diagram for explaining the concept of the index group, and the first and second index groups 1300 and 1400 will be specifically described with reference to the respective drawings. In FIG. 2, the employee table 2000 is shown on the left side, the first index group 1300 to which the first index 1800 belongs is shown on the upper right, and the second index 1900 belongs to the lower right. A second index group 1400 is shown. The employee table 2000 includes a first index 1800 (IDX_A) defined in the set of the name column 202 and the affiliation code string 203 and a second index 1900 (IDX_B) defined in the affiliation code string 203. Existing.

  Both the first and second indexes 1800 and 1900 are indexes for the data of the belonging code string 203. In this embodiment, in order to make a plurality of indexes belong to the first and second index groups 1300 and 1400, the index selected at the time of access path generation is specified by specifying the index group. .

(2) Configuration of Database Management Device in the Present Embodiment FIG. 3 shows an example of the configuration of a data processing system including the database management device 3400. The data processing system includes a host computer 3100, a terminal 3200, a database management device 3400, and an external storage device 3500. These host computer 3100, terminal 3200, database management device 3400, and external storage device 3500 are connected via a network 3300.

  In the host computer 3100, a user application 3000 (the first and second user applications 1000 and 1100) is running. The user application 3000 makes an inquiry to the database by issuing SQL, which is an inquiry language for the relational data model database, to the database management apparatus 3400 via the network 3300. The result of the inquiry is returned from the database management apparatus 3400 to the user application 3000 of the host computer 3100 via the network 3300. The user application 3000 processes and uses the inquiry result received from the database management apparatus 3400.

  The terminal 3200 issues a definition request by issuing an SQL to the database management apparatus 3400 via the network 3300. This definition request includes addition, change or deletion of the table 3550, addition, change or deletion of an index 3560 (corresponding to the index 1800 or 1900), or addition, change or deletion of an index group. The execution result of the definition request is returned from the database management apparatus 3400 to the terminal 3200 via the network 3300.

  The database management apparatus 3400 includes a processor 3410, a memory 3420, a network interface 3450, and a bus 3440. The processor 3410 and the memory 3420 are connected by an internal bus as a part of the bus 3440. The memory 3420 stores programs and other data. The processor 3410 executes a program such as the database management program 3430 stored in the memory 3420.

  The memory 3420 is a storage medium that stores programs and data. Specifically, the memory 3420 stores a database management program 3430 and an access path 3421 generated by the database management program 3430.

  The database management program 3430 manages the table 3550 and the index 3560. A table 3550 is a collection of data conceptually expressed in a table format, and an index 3560 is a means for efficiently accessing the data. The database management program 3430 includes a definition request reception unit 3431, a database (hereinafter also referred to as “DB”) access request reception unit 3432, a dictionary management unit 3433, an access path generation unit 3434, an access path execution unit 3435, and an access path display unit 3436. Have.

  The definition request reception unit 3431 receives a definition request from the terminal 3200 or the like, and controls the entire database management program 3430. Specifically, the definition request reception unit 3431 adds, changes or deletes the table 3550 requested from the terminal 3200 or the like, adds, changes or deletes the index 2560, or adds, changes or deletes an index group. Analyze change requests. Thereafter, the definition request reception unit 3431 requests the dictionary management unit 3433 for processing in response to the request.

  That is, the definition request receiving unit 3431 analyzes the definition request by SQL received from the terminal 3200 or the like, grasps the contents of the processing to be performed by the dictionary management unit 3433, and requests the dictionary management unit 3433 to perform processing. Thereafter, the definition request reception unit 3431 returns the execution result of the process for the definition request to the terminal 3200 that is the request source.

  The DB access request reception unit 3432 receives a DB access request from the user application 3000 and controls the entire database management program 3430. Specifically, the DB access request reception unit 3432 analyzes the DB access request requested from the user application 3000 or the like, and requests the access path generation unit 3434 for processing in order to generate an operation execution procedure for the database. . Next, the DB access request reception unit 3432 requests the access path execution unit 3435 to execute the access path 3421 output by the access path generation unit 3434 and to actually access the database. Thereafter, the DB access request reception unit 3432 returns the execution result of the processing for the DB access request to the user application 3000 that is the request source.

  The dictionary management unit 3433 has a function of managing various definition information such as an index management table 3520, an index group management table 3530, an index-index group correspondence table 3540, a logical schema structure constituting the database, and table information. Specifically, the dictionary management unit 3433 defines the definition information corresponding to the table 3550, the definition information for the index 3560 (index management table 3520), and the definition information for the index group (index group management table 3530, index-index group correspondence). Table 3540) is managed.

  In addition, when the definition request is made, the dictionary management unit 3433 receives the request from the definition request reception unit 3431 and analyzes the analysis result, and the index management table 3520, the index group management table 3530, and the index-index group correspondence table 3540. Record in etc. Furthermore, when a DB access is requested, the dictionary management unit 3433 provides definition information required when the access path generation unit 3434 executes processing based on a request from the DB access request reception unit 3432.

  The access path generation unit 3434 has a function of generating an access path when there is a DB access request. Specifically, the access path generation unit 3434 generates an appropriate access path for accessing the table 3550 and the index 3560. The generated access path is output to the memory 3420.

  When there is a DB access request, the access path execution unit 3435 actually executes access to the database according to the execution procedure determined by the access path 3421 output to the memory 3420 generated by the access path generation unit 3434. Specifically, the access path execution unit 3435 has a function of accessing the table 3550 and the index 3560 to obtain an execution result for the DB access request.

  The access path display unit 3436 has a function of displaying an access path in order to confirm the execution procedure determined by the access path 3421 generated by the access path generation unit 3434 and output to the memory 3420.

  The processing units 3431 and the like arranged in the memory 3420 described above are assumed to be realized by a program, but instead, can be realized by an object or hardware. Each processing unit 3431 arranged in the memory 3420 is stored in a storage medium such as a CD-ROM (Compact Disk Read Only Memory) or a DVD-ROM (Digital Versatile Disk Read Only Memory), and an OS (Operating System). Alternatively, it is installed in the memory 3420 by an installation processing unit included in the database system.

  The external storage device 3500 stores an environment definition file 3510, an index management table 3520, an index group management table 3530, an index-index group correspondence table 3540, table data 3550, and an index 3560. The external storage device 3500 is a storage device on which a magnetic disk in which data is stored magnetically is mounted, for example.

  The environment definition file 3510 records settings for customizing the operation of the database management program 3430. The environment definition file 3510 is created when the database management program 3430 is constructed. The environment definition file 3510 is a file described in text, for example.

  The contents of the environment definition file 3510 are recorded in a table (not shown) for recording setting values of the environment definition file managed by the database management system when the database management program 3430 is started. While the database management program 3430 is operating, the database management program 3430 does not directly refer to the environment definition file 3510 but acquires the setting values from the table (not shown) in which the setting values of the environment definition file 351 are recorded. The index management table 3520, the index group management table 3530, the index-index group correspondence table 3540, the table data 3550, and the index 3560 will be described later.

  FIG. 4 shows an example of the column information constituting the index group management table 3530. The index group management table 3530 has a table format in which one record is defined for each defined index group. An index group can be defined regardless of the table or index. The index group management table 3530 includes an index group ID 401 and an index group name 402. The index group ID 401 is an identifier that is uniquely assigned to each index group name 402. Regarding the index group name 402, “IDXGRP_A” represents the first index group 1300, and “INDGRP_B” represents the second index group 1400. The index group management table 3530 corresponds to an example of an index group definition request to be described later. A specific correspondence will be described with reference to FIG.

  FIG. 5 shows an example of an index group definition request for creating an index group. The index group definition request is described in SQL. The index group definition request indicates that an index group whose index group name is “IDXGRP_A” is defined. The index group referred to here corresponds to the first index group 1300.

  The index group management table 3530 described above corresponds to the example of the index group definition request shown in FIG. The first record 403 of the index group management table 3530 has an index group name “IDXGRP_A”, and corresponds to the index group name “IDXGRP_A” described in the index group definition request shown in FIG. Also, an index group deletion request corresponding to the index group shown in FIG. 5 can be realized.

  FIG. 6 shows an example of column information constituting the index management table 3520. The index management table 3520 has a table format in which one record is provided for each column constituting the defined index. The index management table 3520 includes, as column information, an index ID 601, an index name 602, a table name 603, an index configuration column name 604, and a version 605.

  A table name 603 represents the name of the table in which the index is defined. The index component column name 604 represents the column name of the table in which the index is defined. A version 605 represents the version of the index.

  Since the version 605 exists, it is possible to create indexes of different entities with the index name 602 having the same name. Since the index name 602 is unique among all the indexes 3560, it is possible to change the version 605 with the same name and belong to one or more index groups.

  Thus, in the present embodiment, an index can belong to both index groups A and B. Here, a case is considered in which there is a first user application 1000 that operates using the index group A and a second user application 1100 that operates using the index group B.

  When the concept of the version is not used to make an index belong to a plurality of index groups, for example, in order to improve the performance of the first user application 1000, the access path of the first index 1800 is tried to be changed. When the definition is to be changed, if the first index 1800 is changed directly, the access path of the second user application 1100 may also change. Therefore, the effect of changing an index spreads to index groups other than the index group to which the index belongs.

  Therefore, in the present embodiment, when the first index 1800 belonging to a plurality of index groups is changed, the first index 1800 having the same name with a different version is created. As an example, the version of the first index 1800 belonging to the index group A is “2”, and the version of the first index 1800 belonging to the index group B is “1”.

  In the present embodiment, the first index 1800 (version 1) belonging to the second index group 1400 is not changed, but is changed to the newly created first index 1800 (version 2). By adding, there are the following advantages. That is, in this way, while improving the performance of the first user application 1000 operating using the first index group 1300, the access path of the second user application 1100 is not affected. Can be.

  FIG. 7 shows an example of column information constituting the index-index group correspondence table 3540. In this index-index group correspondence table 3540, the processor 3410 defines the correspondence between the index group and the index. That is, the index-index group correspondence table 3540 has a table format in which one record is provided for each relationship between the defined index and the index group to which the index belongs. One index can belong to a plurality of index groups. The index-index group correspondence table 3540 includes an index ID 801 and an index group ID 802 as column information. The index ID 801 is an identifier that is uniquely assigned to each index. The index group ID 802 is an identifier that is uniquely assigned to the index group to which the index belongs.

  Also, the index-index group correspondence table 3540 shown in FIG. 7 corresponds to an example of an index definition request described later in FIG. A specific correspondence will be described later with reference to FIG.

  FIG. 8 is a diagram illustrating an example of an index definition request for creating an index. This index definition request is also described in SQL. The index definition request is an index definition request that defines an index for the name column 202 and the affiliation code column 203 that are columns of the employee table 2000 and belongs to the index group named first index group 1300 (IDXGRP_A). Is shown.

  This index definition request indicates that an index whose index name is “IDX_A” is defined by a portion from “CREATE” to “IDX_A”. The definition of indexes for the name 202 column and the affiliation code 203 column of the employee table 2000 is shown in the part from “ON” to “INDEXGROUP”. The part after “INDEXGROUP” represents the name of the index group to which the defined index belongs. Further, this index definition request designates the name of an index group that has been created based on the index group definition request shown in FIG.

  The index management table 3520 shown in FIG. 6 and the index-index group correspondence table 3540 shown in FIG. 7 correspond to the example of the index definition request shown in FIG. 8 as described above. Referring to the first record 606 and the second record 607 in the index management table 3520 shown in FIG. 6, the index whose index name is “IDX_A” is the table name “Employee”, the column names “NAME”, and “DIVISION”. It is.

  Therefore, it can be seen that the record 606 and the record 607 correspond to the index definition request shown in FIG. Then, referring to the first record 803 of the index-index group correspondence table 3540 shown in FIG. 7, the index whose index ID is “1” is associated with the index group whose index group ID is “1”. ing. It can be seen that the index whose index ID is “1” is “IDX_A” based on the index management table 3520 shown in FIG. Further, it is understood that the index group whose index group ID is “1” is “IDXGRP_A” based on the index group management table 3530. For this reason, it is indicated that the index 1800 (IDX_A) belongs to the index group 1300 (IDXGRP_A).

  Here, the index change request can be realized by using “ALTER” instead of “CREATE” used in the SQL statement describing the index definition request shown in FIG. An index deletion request can be realized by using “DELETE” instead of “CREATE” used in the SQL statement describing the index definition request.

  Further, in addition to making an index group belong to an index group when a new index is created by an index definition request, an index addition request for making an existing index belong to another index group can also be realized.

(3) Database Management Method in this Embodiment FIG. 9 shows a procedure when the database management program 3430 uses an index group. The operation of the database management program 3430 is controlled by the processor in the database management apparatus 1200. In the following description, the database management program 3430 (or each of its functional modules) will be mainly described as the main body that executes each step instead of the processor. In step S901, the definition request receiving unit 4341 receives and analyzes the index group definition request and the index definition request issued by the terminal 3200 or the like. Thereafter, the dictionary management unit 3433 is called based on the analysis result, and executes the definition based on the request. The procedures of the index group definition request and the index definition request will be described later with reference to FIGS.

  In step S902, the dictionary management unit 3433 associates the user application (hereinafter also abbreviated as “UAP”) with the index group to which the index used by the user application belongs. The user application association is specified in the environment definition file 3510.

  Specifically, for example, in the description format of the environment definition file 3510, a description for specifying an association between an index group and a user application, “INDEX GROUP user application name = index group name”, is provided. By describing “GROUP UAP1 = IDXGRP_A”, the first user application 1000 (corresponding to UAP1) and the first index group 1300 are associated with each other.

  In the case where a user application that has been operating in a conventional DBMS is used in the database management apparatus 1200 according to the present embodiment (or when it is not necessary to prepare a plurality of index groups, for example), the present embodiment It is conceivable that the user wants to use it in the same manner as a DBMS that does not adopt the. Specific examples include, for example, when it is desired to omit specification of an index group in an index definition request, or when it is not desired to describe the correspondence relationship with an index group every time a user application is developed.

  Therefore, a description format for specifying an index group that is assumed when the specification of an index group is omitted, such as “DEFAULT INDEX GROUP index group name”, is newly provided in the description format of the environment definition file 3510. By describing as “DEFAULT INDEX GROUP IDXGRP_DEFAULT”, it is also possible to perform association so that “IDXGRP_DEFAULT” is assumed when the index group specification is omitted.

  The content of the environment definition file 3510 is a table (not shown) for recording the setting values of the environment definition file managed by the database management program 3430 when the database management program 3430 is started, instead of having such a file format. May be recorded. In such a configuration, the database management program 3430 acquires the setting value from a table (not shown) for recording the setting value of the environment definition file without directly referring to the environment definition file 3510 during operation. Accordingly, the terminal 3200 or the like requests the database management program 3430 to change the table (not shown) for recording the setting value of the environment definition file 3510 using SQL, and thereby the user application and the index group. It is also possible to change the association.

  In the present embodiment, instead of using such an environment definition file 3510, a user application-index group correspondence table (not shown) for recording the correspondence between user applications and index groups is stored in an external storage. It may be prepared in the apparatus 3500 to manage the correspondence between the user application and the index group.

  Here, as a merit of showing the correspondence between the user application and the index group using the environment definition file 3510 or the user application-index group correspondence table (not shown) as described above, the existing user application Or, there is no need to change the SQL directly. If a user application or SQL is to be changed when using the database management apparatus 1200 that does not employ the present embodiment, all tests performed at the time of user application development must be redone at the user application development site. According to such a configuration, by using the environment definition file 3510 or the user application-index group correspondence table (not shown) or the like, the correspondence relationship between the user application and the index group is shown, so that existing user applications or SQL It is possible to introduce the database management apparatus 1200 in the present embodiment or change the corresponding index group without correcting the above.

  In step S903, the access path display unit 3436 outputs the access path 3421 generated by the access path generation unit 3434 in a predetermined format. Such a format may be any output content as long as it can be understood by the developer of the user application. As an example of such output contents, the contents shown in FIG. Details of the contents of FIG. 13 will be described later.

  When the access path display request is made, the access path display unit 3436 displays the access path. In addition, the access path display unit 3436 displays the index group together with this access path. This access path is an access procedure generated using an index belonging to this index group. As an application for displaying the access path in this way, there may be mentioned a case where a user application is developed or a survey for performance improvement is performed. Note that the access path display unit 3436 normally does not display such an access path while the user application provides a service.

  In step S904, the access path execution unit 3435 accesses the database according to the access path 3421, creates an answer to the inquiry request, and returns it to the user application 1000 or the like. Examples of such an answer include execution results 2100 and 2200 shown in FIG.

<Index group creation step>
In the index group creation step, the index and the index group are associated with each other and the correspondence relationship is maintained. The association between the index and the index group is performed based on a definition request from the user. A specific example thereof will be described below with reference to FIGS.

  FIG. 10 shows a processing procedure when the database management program 3430 defines an index group. This processing is started when the processor 3410 executes the definition request receiving unit 3431. In this index group creation step, the processor creates an index group by grouping a plurality of indexes.

  The processor 3410 first executes the definition request receiving unit 3431. The request reception unit 3431 acquires the index group name of the index group to be defined from the index group definition request input by the terminal 3200 or the like, and holds it in the memory 3420 (step S1001). For example, when the index group definition request shown in FIG. 5 is input, the memory 3420 holds “IDXGRP_A” which is the name of the index group to be defined.

  Thereafter, the processor 3410 executes the dictionary management unit 3433. The dictionary management unit 3433 associates an index group name held in the memory 3420 and a unique value for each index name as an index group ID, and registers them in the index group management table 3530 (step S1002). For example, when the index group definition request shown in FIG. 5 is input, the record 403 shown in FIG. 4 is registered.

  FIG. 11 is a flowchart showing a processing procedure when the database management program 3430 newly defines an index. This processing is started when the processor 3410 executes the definition request receiving unit 3431.

  The definition request reception unit 3431 receives the index definition request input from the terminal 3200 or the like, the index name of the index to be defined, the table name to which the column included in the index belongs, the column name included in the index, and The index group name to which the index belongs is acquired and stored in the memory 3420 (step S1101).

  For example, when the index definition request shown in FIG. 8 is input, “IDX_A” that is the index name to be defined, “Employee” that is the name of the table to which the column included in the index belongs, and the index The included column names “NAME” and “DIVISION” and the index group name “IDXGRP_A” to which the index belongs are stored.

  Next, the processor 3410 executes the dictionary management unit 3433 to set a unique value for each index name, table name, column name, and index held in the memory 3420 as an index ID, and further update the version. It is registered as “0” in the index management table 3520 (step S1102). Multiple column names can be specified. When a plurality of column names are designated, one association is created for each column name and registered. For example, when the index definition request shown in FIG. 8 is input, the record 606 and the record 607 shown in FIG. 6 are registered.

  Thereafter, the dictionary management unit 3433 refers to the index group management table 3530, and acquires the index group ID corresponding to the index group name held in the memory 3420. This index ID is associated with the index ID assigned as a unique value for each index and registered in the index-index group correspondence table 3540 (step S1103). For example, when the index definition request shown in FIG. 8 is input, the index ID “1” corresponding to the index group name “IDXGRP_A” is acquired from the record 403 shown in FIG. The record 803 shown in FIG. 7 is registered in association with the index ID “1” given as a unique value.

  In this embodiment, as a specific example of an index group creation method, an index group creation method for defining an index group by defining an index group after defining the index group has been described. However, as another example, an existing index group creation method is described. There is also a method of creating a new index group from one or more indexes. For example, when index 1, index 2, and index 3 exist as existing indexes, a case where an index group called IDXGRP_NEW is created from these three indexes will be described. As an index group definition request (SQL) for realizing this, “CREATE INDEXGROUP“ IDXGRP_NEW ”FROM“ index 1 ”,“ index 2 ”,“ index 3 ”” and the like can be considered.

  At this time, the definition request accepting unit 3431 obtains the index group name to be defined and the index name to which it belongs from the above-described index group definition request input by the terminal 3200 or the like, and stores it in the memory 3420.

  Next, the processor 3410 executes the dictionary management unit 3433 to register a unique value for the index group name held in the memory 3420 in the index group management table 3530 as an index group ID.

  Thereafter, the dictionary management unit 3433 refers to the index management table 3520, and acquires an index ID corresponding to the index name held in the memory 3420. This index ID is associated with the index group ID assigned as a unique value for each index group, and is registered in the index-index group correspondence table 3540.

<Access path creation step>
FIG. 12 shows a processing procedure when the database management program 3430 generates an access path. This process is started when the processor 3410 executes the DB access request reception unit 3432. In this access path creation step, the DB access request acceptance unit 3432 accepts an access request designated by an index group, and selects an index that has been determined to have the best performance when obtaining a query result from among the indexes included in the index group. Select an access path using that index.

  Specifically, first, the DB access request reception unit 3432 receives a DB access request in which an index group is designated from any user application 1000 or the like. Next, the DB access request reception unit 3432 acquires the index group name associated with the user application 3000 that issued the DB access request with reference to the setting value specified in the environment definition file 3510 (step S1201).

  Next, the processor 3410 executes the dictionary management unit 3433. The dictionary management unit 3433 refers to the index group management table 3530 and acquires the index group ID corresponding to the index group name acquired in step S1201. The dictionary management unit 3433 refers to the index-index group correspondence table 3540, and acquires an index ID corresponding to the index group ID. Thereafter, the dictionary management unit 3433 refers to the index management table 3520, acquires index information corresponding to the index ID, and stores the index information in the memory 3420 (step S1202).

  Next, the processor 3410 executes the DB access request reception unit 3432. The DB access request reception unit 3432 obtains column names and table names that are conditions for searching for data, and column names and table names that are projection targets from the DB access request input by the user application 3000 ( Step S1203). The user application 3000 is a generic name for the first and second user applications 1000 and 1100 described above.

  Next, the processor 3410 executes the access path generation unit 3434. The access path generation unit 3434 compares the index information stored in the memory 3420 as described above with the table name and column name acquired in step S1203, and is suitable for use when actually accessing the database. As the index, the index having the best performance when obtaining the query result is determined. Then, the access path generation unit 3434 generates an access path using the index, and stores this access path in the memory 3420 (step S1204).

<Access path display step>
FIG. 13 shows an example of output contents when the database management program 3430 outputs an access path. In the illustrated example, an example of an access path output when a DB access request is made by the first user application 1000 is shown. In this access path display step, the processor 3410 executes the access path display unit 3436 to display the access path generated by using the index belonging to the index group together with the index group on a display device (not shown). indicate.

  Specifically, as the output contents, SQL indicating the DB access request issued by the first user application 1000 is output from the first line to the fourth line. In the sixth line, the index group name associated with the first user application 1000 is output. The fifth line represents a title indicating that the content of the sixth line below is an index group. From the eighth line onward, the procedure for accessing the data in the database is output. The seventh line represents a title indicating that the content after the eighth line below is an access path.

  The part “1: SELECT STATEMENT” indicates that the SQL is a SELECT statement. The part “2:! SCAN” indicates that the SQL is simple SQL that does not include the minimum value, the maximum value, the set function value, and the join search. The part “3 :! TABLE Employee” indicates the table name to be searched. The portion “4:! INDEX IDX_A [1] (2)” indicates that the access path is a search using an index. Further, this part indicates that the index name to be used is “IDX_A”, the version is “1”, and the number of columns constituting the index constituting “IDX_A” is two.

  Furthermore, the part “5:! SEARCH TYPE RANGE” indicates an access method when using an index. “6:! SEARCH COLUMNS” and “7:! NAME (1), DIVISION (2)” indicate the column name and the position on the index in which the index search condition is specified. The part “8:! SCAN TYPE INDEX” indicates that the search is performed using an index and that a table is also accessed. The portion “9:! RDAREA NUMBER 1” indicates the number of database areas to be accessed when the access path is executed. “10 :! WITH SHARE LOCK” indicates the type of exclusion when accessing the database.

<Index change step>
FIG. 14 shows a processing procedure when changing the index definition. The index change step represents a procedure in which the processor 3410 changes an index belonging to an index group. This index changing step includes another index creating step and a correspondence changing step. First, the concept will be explained. In the above another index creation step, the processor 3410 directly changes the index itself when the index belongs to a single index group, while the index is changed to a plurality of index groups. If it belongs, the index is duplicated to create another new index using the same name. Next, in the correspondence change step, the processor 3410 changes the correspondence between an index group to which another index among a plurality of index groups should belong and another index.

  Next, specifically, the process shown in FIG. 14 is started when the definition request receiving unit 3431 is executed by the processor 3410. The definition request receiving unit 3431 acquires the index name of the index to be changed and the index group name to which the index belongs from the index change request input by the terminal 3200 or the like (step S1401).

  Next, the processor 3410 refers to the index management table 3520 by executing the dictionary management unit 3433, and acquires the index ID corresponding to the index name acquired in step S1401.

  Since there is a possibility that indexes with the same index name exist, the dictionary management unit 3433 further refers to the index group management table 3530 to obtain an index group ID corresponding to the index group name acquired in step S1401. get.

  The dictionary management unit 3433 refers to the index-index group correspondence table 3540 and acquires the index-index group information in which the correspondence between the acquired index ID and the index group ID is established, thereby specifying the index ID to be changed. .

  Next, the dictionary management unit 3433 refers to the index management table 3520, acquires index information to be changed corresponding to the index ID to be changed, and stores it in the memory 3420 (step S1402).

  Next, the dictionary management unit 3433 refers to the index-index group correspondence table 3540, acquires the index group ID corresponding to the index ID to be changed, and counts the number (step S1403). Next, if the number of index group IDs counted in step S1403 is greater than “1”, the dictionary management unit 3433 performs the following process (step S1404).

  First, the dictionary management unit 3433 changes the version of the held index information and sets a unique ID for each index again, and newly registers it in the index management table 3520 (step S1405). This is because indexes with the same index name but different versions have different index entities, and therefore the index ID must be reassigned.

  In step S1405, the dictionary management unit 3433 applies an index change request to the index information registered in the index management table 3520 (step S1406). Next, the dictionary management unit 3433 refers to the index-index group correspondence table 3540, and indicates an index-index group correspondence that indicates the correspondence between the index group ID of the index group specified in the index change request and the index ID to be changed. Search for information. The dictionary management unit 3433 changes the data in the index-index group management table 3540 by changing these index IDs to the index IDs of the index information registered in the index management table in step S1405 (step S1407).

  The reason for duplicating the index information and separating the index entities as described above is that each index such as the first index 1800 can belong to both the first and second index groups 1300 and 1400. It is. Here, there is a first user application 1000 that operates using the first index group 1300 and a second user application 1100 that operates using the second index group 1400. Think about the case. For example, when the definition of the first index 1800 is changed to change the access path of the first user application 1000 in order to improve the performance of the first user application 1000, the first index 1800 is directly changed. If it is changed directly, the access path of the second user application 1100 may change, and the influence of changing the index spreads to other than the specific index group.

  Therefore, when changing the first index 1800 belonging to a plurality of index groups, the first index 1800 having the same name with a different version is created. As an example, the version of the first index 1800 belonging to the first index group 1300 is “2”, and the version of the first index 1800 belonging to the second index group 1400 is “1”. To do. By changing the first index 1800 (version 2) newly created without changing the first index 1800 (version 1) belonging to the second index group 1400, the index The performance improvement of the first index group 1300 operating using the group A is realized, and the access path of the second index group 1400 is not affected.

  In step S1404, if the number of index group IDs is equal to “1”, the dictionary management unit 3433 performs the following process. In other words, the discretionary management unit 3433 refers to the index management table 3520, searches for index information corresponding to the index ID to be changed, applies the index change request, and changes it directly (step S1408).

<Index deletion step>
FIG. 15 is a flowchart showing a processing procedure when the database management program 3430 deletes an index definition. First, the concept will be described. In the index deletion step, the processor 3410 deletes the index belonging to the index group. That is, in this index deletion step, the processor 3410 directly deletes the index when the deletion target index belongs to a single index group, and the deletion target index belongs to a plurality of index groups. In this case, instead of deleting the index itself, the correspondence relationship between the index group and the index is deleted.

  More specifically, first, this process is started by the processor 3410 executing the definition request receiving unit 3431. The definition request reception unit 3431 acquires the index name of the index to be deleted and the index group name to which the index belongs from the index deletion request input by the terminal 3200 or the like (step S1501).

  Next, the processor 3410 refers to the index management table 3520 by executing the dictionary management unit 3433, and acquires the index ID corresponding to the index name acquired in step S1501.

  Since there is a possibility that an index with the same index name exists, the discretionary management unit 3433 further refers to the index group management table 3530 and refers to the index group corresponding to the index group name acquired in step S1401. Get an ID.

  The dictionary management unit 3433 refers to the index-index group correspondence table 3540 and acquires the index-index group information in which the correspondence between the acquired index ID and the index group ID is established, thereby identifying the index ID to be deleted. .

  Thereafter, the dictionary management unit 3433 refers to the index-index group correspondence table 3540, acquires the index group ID corresponding to the index ID to be deleted, and counts the number (step S1502).

  Next, when the number of index group IDs counted in step S1502 is larger than “1”, the dictionary management unit 3433 corresponds to the deletion target index ID acquired in step S1502 from the index management table 3520. The index information is deleted (step 1504).

  Thereafter, the dictionary management unit 3433 searches the index-index group correspondence table 3540 for index-index group correspondence information indicating the correspondence between the index group ID of the index group specified in the index deletion request and the index ID to be deleted. It deletes (step S1505).

<User application-index group association change>
FIG. 16 shows a procedure for changing the association between the user application and the index group. First, it is confirmed whether or not the environment definition file 3510 describes the association (hereinafter simply referred to as “association”) between the user application whose index is to be changed and the index group (step S1601). In step S1601, when the association is not described in the environment definition file 3510, a description for designating the association is newly added to the environment definition file 3510 (step S1602).

  If the association is described in the environment definition file in step S1601, the association described in the environment definition file 3510 is changed (step S1603). By changing the association as described above, the following usage can be performed.

  For example, consider a case where the performance of the first user application 1000 that already provides a service is improved. Note that the first user application 1000 is an application that operates using the first index group 1300. First, considering an index for creating an access path with the best performance when obtaining a query result, the index is defined for the second index group 1400. Next, an access path is confirmed and tested using the second index group 1400.

  If there is no problem, the index group associated with the first user application 1000 is changed from the first index group 1300 to the second index group 1400. In this way, it is possible to perform a test when the index is changed in an environment where the first user application 1000 is already operating. Further, when this is done, it is not necessary to redefine the index created when this test is performed, and it can be used as it is.

<Index group replication step>
FIG. 17 shows a processing procedure when a copy of an index group is created. First, the concept will be described. In the index group duplication step, the processor 3410 duplicates the index group and also duplicates the correspondence relationship with the index belonging to the index group.

  More specifically, this processing is started when the processor 3410 executes the definition request receiving unit 3431. The definition request reception unit 3431 acquires and holds the index group name that is the replication source and the index group name that is the replication destination from the index group replication request input by the terminal 3200 or the like (step S1801). Next, the processor 3410 executes the dictionary management unit 3433 to register the held copy destination index group name in the index group management table 3530 (step S1802).

  The dictionary management unit 3433 refers to the index group management table 3530 and acquires an index group ID corresponding to the index group name of the copy source that is held. Furthermore, the dictionary management unit 3433 refers to the index-index group correspondence table 3540, and acquires index-index group correspondence information corresponding to the index group ID (step S1803).

  Thereafter, the dictionary management unit 3433 changes the index group ID of the index-index group correspondence information acquired in step S1803 to the index group ID registered in step S1802. Furthermore, the dictionary management unit 3433 newly registers the index-index group correspondence information after the change in the index-index group correspondence table 3540 (step S1804).

  FIG. 18 shows an example of an index group duplication request (SQL) for duplicating an index group. The index group duplication request shown in the figure indicates that an index group whose index group name is “IDXGRP_A” (corresponding to the first index group 1300) is duplicated to a new index group “IDXGRP_NEW”.

  FIG. 17 described above shows a case where an index group is duplicated in the same database management program 3430. However, it is also possible to create a copy of the index group for another database management system by specifying the index group and exporting the index definition information to a file or the like.

  By duplicating the index group in this way, the following usage can be performed.

  For example, consider a case where the performance of the first user application 1000 that already provides a service is improved. Note that the first user application 1000 is an application that operates using the first index group 1300. First, the index group is duplicated to create a second index group 1400 to which the same index as the first index group 1300 belongs. Next, the performance is improved by adding, changing, or deleting an index to the second index group 1400. Then, using the second index group 1400, the access path is confirmed and tested. If there is no problem, the index group associated with the first user application 1000 is changed from the first index group 1300 to the second index group 1400 using the above-described user application-index group association change. It becomes possible to improve performance by changing to.

  In addition, by exporting the index definition information to a file etc., the index group can be migrated from the database management system used for development to the database management system used for production that actually provides services. Alternatively, an index group can be migrated from a database management system that is actually used to provide services to a database management system that is used for development.

<Access path execution step>
In the access path execution step, the processor 3410 executes the access path execution unit 3435 to execute the access path generated in the index group creation step as described above, thereby allowing DB access from the terminal 3200 or the like. In response to the request, operations such as searching, changing, updating, registering or deleting the data in the table 3550 are performed.

(4) Effects of this embodiment As described above, in the above embodiment, the processor 3410 creates an index group 1300 by grouping a plurality of indexes 1500, and the processor. 3410 receives the specified access request of the index group 1300, selects an index considered to have the best performance when obtaining a query result from the indexes included in the index group 1300, and uses the index An access path creation step for generating an access path is executed.

  In the above embodiment, the database management device 1200 groups a plurality of indexes to create an index group 1300, and a dictionary management unit 3433 as an index group management unit, and a specified access request for the index group 1300 And an access path creation unit that selects an index determined to have the best performance when obtaining a query result from the indexes included in the index group 1300 and generates an access path using the index. . This access path creation unit includes the DB access request reception unit 3432, the discretionary management unit 3433, and the access path generation unit 3434 in the above embodiment.

  In this way, an operation such as adding, changing, or deleting an index from a certain user application (for example, the first user application 1000) can limit the range of influence on the access path. The access path of the user application 1100) can be prevented from being affected. Therefore, an index for generating an access path with the best performance when obtaining a query result for each user application without affecting the access path corresponding to the access request by the second user application 1100 is freely added. , Can be changed or deleted.

  In the present embodiment, the processor 3410 operates the data in the table according to the access request by executing the generated access path.

  In this way, even when a new second user application 1100 is added, only the indexes belonging to the specific index group 1300 are used as intended by the developer of the first user application 1000. By executing the access path, it is possible to prevent the execution performance from changing without affecting the other access path with the addition, change or deletion of the index operation.

  In this embodiment, the processor 3410 groups index definition information instead of grouping the indexes themselves. The definition information corresponds to the index definition information 1500 in the present embodiment.

  In the present embodiment, the processor 3410 displays the index group 1300 on the display device (not shown) together with the access path generated using the index belonging to the index group 1300.

  In the present embodiment, when processor 3410 attempts to change index 1800 belonging to index group 1300, when index 1800 belongs to a single index group 1300, it directly changes index 1800 itself. When the index 1800 belongs to a plurality of index groups 1300 and 1400, the index 1800 is duplicated to create another new index 1900 using the same name, and among the plurality of index groups 1300 and 1400 Then, the correspondence relationship between the index group 1300 to which another index belongs and the other index is changed.

  In this way, even if a certain index belongs to a plurality of index groups 1300 and 1400, the change of the index is made to the access path of the second user application 1100 that uses the other index group 1400. It can be made not to affect.

  In this embodiment, when the processor 3410 tries to delete an index belonging to the index group 1300, if the index to be deleted belongs to a single index group 1300, the index is directly deleted and deleted. When the target index belongs to a plurality of index groups 1300 and 1400, the correspondence relationship between the index group 1300 and the index is deleted instead of deleting the index itself.

  In this way, even if a certain index belongs to a plurality of index groups 1300, the deletion of the index affects the access path of the second user application 1100 that uses another index group 1400. You can avoid giving.

  In the present embodiment, in the index group creation step, the processor 3410 accepts an index group definition request for defining the index group 1300 and creates the index group 1300, while creating an index for the index group 1300. An index definition request for definition is received and an index is defined in the index group 1300.

  In this embodiment, the processor 3410 executes a correspondence definition step for defining the correspondence between the index group 1300 and the like and the index. In the present embodiment, the definition of the correspondence relationship is managed by the index-index group correspondence table 3540 or the environment definition file 3510.

  In this way, in order to change the access path of the user application, only by changing the index group 1300 to which the user application belongs without repeating the addition, deletion or change of the index belonging to a certain index group 1300. The access path can be changed without adding such an index.

  In the present embodiment, the processor 3410 duplicates the index group 1300 and also duplicates the correspondence relationship with the index belonging to the index group 1300.

  In this way, it is possible to duplicate the access path of the user application and perform a performance improvement trial.

  In the present embodiment, the database management device 1200 defines the index group 1300 by grouping a plurality of indexes as the index group management unit, while the index to be assigned to the index group 1300 is determined. A definition management unit 3433 to be defined is provided. Further, the database management apparatus 1200 serves as the access path creation unit, the DB access request reception unit 3432 that receives the access request, and the index group 1300 based on the access request received by the DB access request reception unit 3432. A dictionary management unit 3433 as an index selection unit that selects an index that has been determined to have the best performance in obtaining a query result from the included indexes, and a performance in obtaining a query result selected by the dictionary management unit 3433 The access path generating unit 3434 generates an access path using the index determined to be the best.

(5) Other Embodiments The above embodiment is an example for explaining the present invention, and is not intended to limit the present invention only to these embodiments. The present invention can be implemented in various forms without departing from the spirit of the present invention. For example, in the above-described embodiment, the processing of various programs is described sequentially, but this is not particularly concerned. Therefore, as long as there is no contradiction in the processing result, the processing order may be changed or the operation may be performed in parallel.

Claims (3)

A memory, the table in accordance with the access path in response to the access request, generated by selecting an index from a plurality of indexes set in the table from a plurality of user applications to a database having at least one table In the database management method in the database management device that operates the data of
The database management device includes:
Based on the plurality of first definition requests, the index group name of the index group to be defined is acquired, and the index group is registered in the index group management table in correspondence with the acquired index group name to create a new one. create a multiple of the index group,
Based on the second definition request, the definition target index, the table name of the table to which the column included in the definition target index belongs, and the column name of the column included in the definition target index And the index group name of the index group to which the index should belong and is stored in the memory, and the first index information is indexed for each index to be defined Register in the management table,
The index group corresponding to the index group name held in the memory is acquired with reference to the index group management table, and the acquired index group and at least one of the acquired index groups to belong to By associating the index with each other and registering it in the index-index group correspondence table, the performance when obtaining the query result when executing each user application corresponding to each index group is the highest in each index group. At least one index that is considered good
Based on an association management file for managing an index group name associated with the user application that has issued the first definition request by accepting an access request designated for an index group from any of the plurality of user applications , Obtain a specific index group name associated with the user application that received the access request,
A specific index group corresponding to the acquired specific index group name is acquired with reference to the index group management table, and the specific index group is acquired based on the index-index group correspondence table. The first index information related to the specific index to be acquired is stored in the memory, and the first index information stored in the memory, and the second name including the table name and the column name acquired from the access request depending on the result of against the index information, and determines the index suitable for use when accessing the database management method characterized by generating an access path by using the determined index. In response to an access request from a plurality of user applications for a database having a memory and having at least one table, the table is selected according to an access path generated by selecting an index from the plurality of indexes set in the table. In the database management device that operates the data of
Based on the plurality of first definition requests, the index group name of the index group to be defined is acquired, and the index group is registered in the index group management table in correspondence with the acquired index group name to create a new one. create a multiple of the index group,
Based on the second definition request, the definition target index, the table name of the table to which the column included in the definition target index belongs, and the column name of the column included in the definition target index And the index group name of the index group to which the index should belong and is stored in the memory, and the first index information is indexed for each index to be defined Register in the management table,
The index group corresponding to the index group name held in the memory is acquired with reference to the index group management table, and the acquired index group and at least one of the acquired index groups to belong to By associating the index with each other and registering it in the index-index group correspondence table, the performance when obtaining the query result when executing each user application corresponding to each index group is the highest in each index group. At least one index that is considered good
Based on an association management file for managing an index group name associated with the user application that has issued the first definition request by accepting an access request designated for an index group from any of the plurality of user applications , Obtain a specific index group name associated with the user application that received the access request,
A specific index group corresponding to the acquired specific index group name is acquired with reference to the index group management table, and the specific index group is acquired based on the index-index group correspondence table. The first index information related to the specific index to be acquired is stored in the memory, and the first index information stored in the memory, and the second name including the table name and the column name acquired from the access request A database management apparatus , wherein an index suitable for use in access is determined according to a result of collating with index information, and an access path is generated using the determined index. The database management device having a memory, at least one table in response to access requests from a plurality of user applications to a database with access paths generated by selecting an index from a plurality of indexes set in the table In the database management program that operates the data of the table according to
In the database management device,
Based on the plurality of first definition requests, the index group name of the index group to be defined is acquired, and the index group is registered in the index group management table in correspondence with the acquired index group name to create a new one. to create a multiple of the index group,
Based on the second definition request, the definition target index, the table name of the table to which the column included in the definition target index belongs, and the column name of the column included in the definition target index And the index group name of the index group to which the index should belong and is stored in the memory, and the first index information is indexed for each index to be defined Register it in the management table,
An index group corresponding to the index group name held in the memory is acquired with reference to the index group management table, and the acquired index group and at least one of the acquired index groups should belong to the index group By associating the index with each other and registering it in the index-index group correspondence table, the performance when obtaining the query result when executing each user application corresponding to each index group in each index group is the highest. At least one index that is considered good
Based on an association management file for managing an index group name associated with the user application that has issued the first definition request by accepting an access request designated for an index group from any of the plurality of user applications , To obtain a specific index group name associated with the user application that received the access request,
A specific index group corresponding to the acquired specific index group name is acquired with reference to the index group management table, and the specific index group is acquired based on the index-index group correspondence table. The first index information related to the specific index to be acquired is stored in the memory, and the first index information stored in the memory and the second name including the table name and the column name acquired from the access request A database management program characterized by causing an index suitable for use in access to be determined according to a result of collating with index information and generating an access path using the determined index.