JP2009276941A - Determination device, program, and determination method - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide a determination device, a program, and a determination method for performing efficient table partition. <P>SOLUTION: This determination device for determining which of a plurality of table storage regions should each of a plurality of division tables obtained by dividing a table in a database be arranged in includes: an acquisition part for acquiring an access request to a table; a specification part for specifying an access range in the table from the acquired access request; a calculation part for calculating the access frequency of each record range of the table from the access range of each access request; and a determination part for determining a table storage region in which each division table should be arranged based on the access frequency of each record range of the table. <P>COPYRIGHT: (C)2010,JPO&INPIT

Description

  The present invention relates to a determination device, a program, and a determination method. In particular, the present invention relates to a determination device, a program, and a determination method for determining the arrangement of a divided table obtained by dividing a database table.

  With the establishment of the SOX (Survey Oxley) Act, etc., the amount of data that must be preserved for business use increases, so that a storage system having a very large storage capacity such as a SAN (Storage Area Network) is used. It has become. However, with the increase in data to be retained, the maintenance cost for retaining data has also increased. Therefore, efficient data management, such as storing frequently accessed data in a storage device with high maintenance cost but good access performance, and storing data with low access frequency in storage device with poor access performance but low maintenance cost. Has become necessary. Hereinafter, such data management is referred to as information life cycle management.

  Further, as the capacity of the database increases, a technique called table partition is known in which one table in a database is divided into a plurality of divided tables and stored in physically different storage areas (for example, Patent Documents 1 and 2). According to such a table partition technology, for example, it is easy to load a large number of new records into a table and to delete records whose retention period has passed from the table.

JP 2001-142752 A JP 2002-41333 A

  By the way, each divided table divided by the table partition is preferably stored in the storage device in consideration of information life cycle management. However, in the table partition, since the table division range is specified by the user, it is difficult to perform the optimum division when the table to be divided includes a huge amount of records.

  Then, an object of this invention is to provide the determination apparatus, program, and determination method which can solve said subject. This object is achieved by a combination of features described in the independent claims. The dependent claims define further advantageous specific examples of the present invention.

  According to a first aspect of the present invention, there is provided a determination device for determining which of a plurality of divided tables obtained by dividing a table in a database is to be arranged in a plurality of table storage areas, wherein an access request for the table is issued. An acquisition unit to be acquired, a specifying unit for specifying an access range in the table from the acquired access request, a calculation unit for calculating an access frequency for each record range of the table from the access range of each access request, and a record range of the table A determination device is provided that includes a determination unit that determines a table storage area in which each divided table is to be arranged based on the access frequency for each. In addition, a method and a program for causing a computer to function as the determination device are provided.

  In the second aspect of the present invention, there is provided a determination device that determines in which of a plurality of table storage areas each of a plurality of divided tables obtained by dividing a table in a database, and each of the plurality of tables The acquisition unit that acquires the access request for each table, the specific unit that specifies the access range in each table from the access request acquired for each table, and the access range of each access request for each table, A plurality of table storages, comprising: a calculation unit that calculates an access frequency for each record range; and a determination unit that determines a table storage area in which each divided table is to be placed based on the access frequency for each record range of the table. The area is a memory to which each table storage area is allocated. The determination unit is configured to select a table storage area in order of higher priority, and a record range having an access frequency equal to or higher than a reference access frequency in a plurality of tables. Dividing a record range with an access frequency equal to or higher than the reference access frequency from each of a plurality of tables and an adjustment unit that adjusts the reference access frequency so that the total data size is within the usable size of the selected table storage area A dividing unit that generates a dividing table, and an arrangement determining unit that decides that a dividing table obtained by dividing a record range having an access frequency equal to or higher than the reference access frequency should be arranged in the selected table storage area. A decision device is provided.

  The above summary of the invention does not enumerate all the necessary features of the present invention, and sub-combinations of these feature groups can also be the invention.

  Hereinafter, the present invention will be described through embodiments of the invention. However, the following embodiments do not limit the invention according to the scope of claims, and all combinations of features described in the embodiments are included. It is not necessarily essential for the solution of the invention.

  FIG. 1 shows a configuration of a database apparatus 10 according to the present embodiment. The database device 10 includes a database server 12, a plurality of storage devices 14, and a determination device 20. The database server 12 receives a database request from another device and executes database processing according to the received database request.

  Each of the plurality of storage devices 14 stores a table in a database managed by the database server 12. Each of the plurality of storage devices 14 has a table storage area 30 for storing a table assigned to the storage device 14. The table storage area 30 is called a table space, for example. Further, each of the plurality of storage devices 14 may be configured to be connected to the database server 12 via the network 16.

  The determination device 20 determines the arrangement of tables to be stored in the plurality of table storage areas 30. The detailed configuration of the determination device 20 will be described in FIG.

  FIG. 2 shows an example of a plurality of table storage areas 30 and a plurality of divided tables 40 stored in the plurality of table storage areas 30. The database device 10 distributes a plurality of divided tables 40 obtained by dividing the tables in the database in a plurality of table storage areas 30. That is, the database apparatus 10 stores a plurality of divided tables 40 generated by executing table partitions in a plurality of table storage areas 30. Further, the database device 10 executes table partitioning for a plurality of tables in the database. Further, as an example, the database device 10 uses, for each of a plurality of tables, a table column (for example, a primary key of the table or a date type column) as a key, and each key value range specified for each table. A plurality of divided tables 40 may be distributed and arranged in a plurality of table storage areas 30.

  The plurality of table storage areas 30 are prioritized in descending order of access performance of the storage devices 14 to which the respective table storage areas 30 are assigned. The high access performance of the storage device 14 may be, for example, a high throughput of the storage device 14. Further, the high access performance of the storage device 14 may be, for example, a high access speed.

  FIG. 3 shows a functional configuration of the determination apparatus 20 according to the present embodiment together with a plurality of table storage areas 30. The determination device 20 determines in which of the plurality of table storage areas 30 each of the plurality of divided tables 40 obtained by dividing the table in the database. Then, the table storage area 30 changes the storage position of each of the plurality of divided tables 40 according to the determined arrangement.

  The determination device 20 includes an acquisition unit 52, a specification unit 54, a calculation unit 56, and a determination unit 58. The acquisition unit 52 acquires an access request for each of the plurality of tables.

  The specifying unit 54 specifies the access range in each table from the access request acquired for each table. The calculation unit 56 calculates the access frequency for each record range of each table from the access range of each access request for each table.

  The determining unit 58 determines the table storage area 30 in which each divided table 40 is to be placed based on the access frequency for each record range of the table. For example, the determination unit 58 may include a selection unit 62, an adjustment unit 64, a division unit 66, and an arrangement determination unit 68.

  The selection unit 62 selects the table storage area 30 in descending order of priority. The adjustment unit 64 has an access frequency equal to or higher than the reference access frequency in the plurality of tables, and the total data size of the record range is within the usable size of the table storage area 30 designated in advance or selected by the selection unit 62. The reference access frequency is adjusted as follows.

  The dividing unit 66 divides the at least one table into a plurality of divided tables 40 based on the access frequency for each record range of the at least one table. For example, the dividing unit 66 may generate the divided table 40 by dividing a record range having an access frequency equal to or higher than the reference access frequency from each of the plurality of tables.

  The arrangement determining unit 68 determines each divided table 40 and the table storage area 30 where each non-divided table is to be arranged based on the access frequency for each record range of each table. For example, the arrangement determining unit 68 should arrange that the division table 40 obtained by dividing the record range having an access frequency equal to or higher than the reference access frequency should be arranged in the table storage area 30 designated in advance or selected by the selection unit 62. decide.

  FIG. 4 shows a processing flow of the determination apparatus 20 according to the present embodiment. The determination apparatus 20 executes the following steps S11 to S16 when the plurality of divided tables 40 obtained by dividing the table in the database are distributed and arranged in the plurality of table storage areas 30.

  First, in step S11, the calculation unit 56 selects one or a plurality of key (for example, column) candidates for designating a range of values for dividing the table for each of the plurality of tables in the database. Set. The calculation unit 56 may set a candidate key in accordance with information input from the user.

  Subsequently, in step S12, the acquisition unit 52 monitors access to a plurality of tables in the database, and acquires access requests to the plurality of tables given during a certain period. For example, the acquisition unit 52 may acquire an access request for a plurality of tables by acquiring a log of a query given to the database server 12 from an external device. For example, the acquisition unit 52 may acquire an access request for each table obtained by monitoring each of the plurality of tables for several hours, one day, one month, or the like.

  Subsequently, in step S <b> 13, the specifying unit 54 specifies an access range in each table from each access request given during a certain period acquired for each table. For example, the specifying unit 54 may specify a range of records read or written by each access request as an access range in the table.

  For example, the specifying unit 54, for each key that is one or more candidates set in step S11 in one or more records accessed by the access request, from each access request acquired for each table. A range of values may be specified. For example, when a key that stores a value representing a date for a certain table is selected as a candidate, the specifying unit 54 selects the date range of the key in one or more records read or written by each access request (for example, , X year X month X year to Y year Y month Y day) may be specified.

  For example, the specifying unit 54 may use one or more candidate keys in a record read or written by the query based on a query search condition given to the database server 12 from an external device. A range of values for each may be specified. An example of a specifying method based on the query search condition will be described in detail with reference to FIG.

  Subsequently, in step S14, the calculation unit 56 calculates the access frequency for each record range of each table from the access range of each access request for each table. As an example, the calculation unit 56 accumulates the access made to each record of each table for a certain period from the access range of each access request to each table, and the access frequency for each record range of each table. May be calculated.

  As an example, the calculation unit 56 calculates the value for each record in each table from the range of values for each candidate key set in step S11 in the record accessed by each access request for each table. Access may be accumulated for a certain period of time for each value range of the key. Then, the calculation unit 56 may calculate the access frequency for each value range for each of a plurality of keys as one or a plurality of candidates based on the accumulation result.

  Subsequently, in step S15, the determining unit 58 sets the table storage area 30 in which each divided table 40 and each table that is not divided are to be arranged based on the access frequency for each record range of each table. decide. As an example, the determination unit 58 does not divide each of the plurality of tables based on the access frequency for each value range for each of one or more candidate keys. You may determine the table storage area 30 which should arrange | position each table. Details of the processing in step S15 will be described with reference to FIG.

  In step S16, the determination unit 58 changes the arrangement of the division table 40 according to the arrangement determined in step S15. When the determination device 20 finishes the process of step S <b> 16, it ends the flow.

  FIG. 5 shows an example of the access frequency for each range of candidate key values for each of a plurality of tables. In the present embodiment, the calculation unit 56 calculates the access frequency for each key value range for each of one or a plurality of candidate keys for each of a plurality of tables that are targets of data partitions in the database. calculate.

For example, when the key K ₁₁ of the table T ₁ in the database is selected as a candidate, the calculation unit 56 accesses the table T ₁ for each range of values of the key K ₁₁ as shown in FIG. Calculate the frequency. Further, for example, when the key K ₂₁ of the table T ₂ in the database is selected as a candidate, the calculation unit 56 performs the range of the value of the key K ₂₁ for the table T ₂ as shown in FIG. The access frequency is calculated. Further, for example, when the key K ₂₂ of the table T ₂ in the database is selected as a candidate, the calculation unit 56 performs the range of the value of the key K ₂₂ for the table T ₂ as shown in FIG. The access frequency is calculated. Further, for example, when the key K ₃₁ of the table T ₃ in the database is selected as a candidate, the calculation unit 56 performs the range of the value of the key K ₃₁ for the table T ₃ as shown in FIG. The access frequency is calculated. Further, for example, when the key K ₃₂ of the table T ₃ in the database is selected as a candidate, the calculation unit 56 sets the range of the value of the key K ₃₂ for the table T ₃ as shown in FIG. The access frequency is calculated.

  Further, as an example, the calculation unit 56 generates a histogram representing the access frequency for each range of values when arranged in the order of the values of the keys selected as candidates, as shown in (A) to (E) of FIG. It may be calculated. Further, as an example, the calculation unit 56 generates a function representing the access frequency for the key value by approximating a histogram as shown in FIGS. 5A to 5E by the least square method or the like. Also good.

FIG. 6 shows a processing flow of step S15 shown in FIG. 7, the key _{K 11} of table _{T 1,} the key _{K 21} of table _{T 2,} in the case of a combination of keys _{K 31} of the table _{T 3,} one example of the access frequency and the reference access frequency for each range of values for each key Show. 8, the key _{K 11} of table _{T 1,} the key _{K 22} of table _{T 2,} in the case of a combination of keys _{K 31} of the table _{T 3,} one example of the access frequency and the reference access frequency for each range of values for each key Show. 9, the key _{K 11} of table _{T 1,} the key _{K 21} of table _{T 2,} in the case of a combination of keys _{K 32} of the table _{T 3,} one example of the access frequency and the reference access frequency for each range of values for each key Show. Figure 10 is a key _{K 11} of table _{T 1,} the key _{K 22} of table _{T 2,} in the case of a combination of keys _{K 32} of the table _{T 3,} one example of the access frequency and the reference access frequency for each range of values for each key Show.

  In step S15 shown in FIG. 4, the determination unit 58 executes the following processes of step S20 to step S28. First, in step S <b> 20, the selection unit 62 selects any one of the plurality of table storage areas 30. In this case, the selection unit 62 selects the table storage area 30 in descending order of priority. Then, the selecting unit 62 causes the adjusting unit 64, the dividing unit 66, and the arrangement determining unit 68 to execute the following processing from step S21 to step S27 for each selected table storage area 30 (S20, S28).

  When the table storage area 30 is selected, subsequently, in step S <b> 21, the adjustment unit 64 calculates an available size representing the capacity of the divided table 40 that can be stored in the selected table storage area 30. For example, the adjustment unit 64 may calculate a predetermined ratio (for example, 80%) with respect to the total capacity of the selected table storage area 30 as the usable size. Thus, the adjustment unit 64 can prevent the total capacity of the table storage area 30 from being exceeded even when the capacity of the divided table 40 stored in the selected table storage area 30 varies.

  Subsequently, the adjustment unit 64 designates one of the one or a plurality of keys selected as candidates in step S11 for each of the plurality of tables. Then, the adjustment unit 64 executes the process of step S23 for each key combination designated for the plurality of tables (S22, S24).

For example, when a key as shown in FIG. 5 is selected as a candidate, the adjustment unit 64 may execute the process of step S23 for each of the following four combinations.
First combination: Table _{T 1} of the key _{K 11,} table _{T 2} of the key _{K 21,} key _{K 31} of the table _{T 3}
Second combination: Table _{T 1} of the key _{K 11,} table _{T 2} of the key _{K 22,} key _{K 31} of the table _{T 3}
Third combination: Table _{T 1} of the key _{K 11,} key _{K 21} of table _{T 2,} the key _{K 32} of the table _{T 3}
Fourth combination: Table _{T 1} of the key _{K 11,} key _{K 22} of table _{T 2,} the key of the table _{T 3 K 32}

  In step S23, the adjustment unit 64 sets the reference data so that the total data size of the record range having an access frequency equal to or higher than the reference access frequency in the plurality of tables is within the usable size of the selected table storage area 30. Adjust access frequency. In the present embodiment, the adjustment unit 64 has a total data size of records within a usable size among records having a specified key value that has an access frequency equal to or higher than the reference access frequency in a plurality of tables. The reference access frequency is adjusted as follows. More specifically, the adjustment unit 64 may adjust the reference access frequency as follows.

  First, the adjustment unit 64 sets an arbitrary initial value as the reference access frequency. Subsequently, for each of the plurality of tables, the adjustment unit 64 calls a histogram representing an access frequency for each range of values when arranged in the order of values of designated keys, or a function representing the histogram. Subsequently, for each of the plurality of tables, the adjustment unit 64 compares the access frequency for each specified key value range with the reference access frequency, and has a specified access frequency that is equal to or higher than the reference access frequency. Extract a range of key values.

  Subsequently, the adjustment unit 64 calculates the total data size of the records in which the specified key value in the plurality of tables matches the extracted value range. For example, the adjustment unit 64 calculates, for each of a plurality of tables, the number of records in which the specified key value matches the extracted value range, and calculates the preset capacity per record. The individual data size for each table may be calculated by multiplying the number of records. For example, the adjustment unit 64 may calculate the total data size by adding up the individual data sizes of the plurality of tables.

  Subsequently, the adjustment unit 64 compares the total data size with the usable size. Subsequently, the adjustment unit 64 increases the reference access frequency if the total data size is larger than the usable size, and sets the reference access frequency low if the total data size is smaller than the usable size. The adjustment unit 64 repeats the above processing to adjust the reference access frequency so that the total data size becomes the lowest value that does not exceed the usable size. In this way, the adjustment unit 64 makes the total data size of the records within the usable size among the records having the designated key value having the access frequency equal to or higher than the reference access frequency in the plurality of tables. The reference access frequency can be adjusted.

  The adjustment unit 64 executes the process of step S23 for all combinations of candidate keys, and adjusts the reference access frequency for each of all combinations of candidate keys. When the key as shown in FIG. 5 is selected as a candidate, for example, the reference access frequency as shown in FIGS. 7 to 10 may be adjusted.

That is, the adjusting section 64, the first combination (i.e., key _{K 11} of table _{T 1,} the key _{K 21} of table _{T 2,} the key combination _{K 31} of the table _{T 3)} for, as shown in FIG. 7 Adjust to the standard access frequency. Adjusting section 64, a second combination (i.e., key _{K 11} of table _{T 1,} the key _{K 22} of table _{T 2,} the key combination _{K 31} of the table _{T 3)} for the reference access as shown in FIG. 8 Adjust to frequency. Adjusting section 64, the third combination (i.e., key _{K 11} of table _{T 1,} the key _{K 21} of table _{T 2,} the key _{K 32} Table _{T 3)} for, in reference access frequency as shown in FIG. 9 adjust. Adjusting section 64, the fourth combination (i.e., key _{K 11} of table _{T 1,} the key _{K 22} of table _{T 2,} the key combination _{K 32} of the table _{T 3)} for the reference access as shown in Figure 10 Adjust to frequency.

  When the adjustment of the reference access frequency is completed for all combinations of candidate keys (S24), the adjustment unit 64 advances the process to step S25. In subsequent step S25, the adjustment unit 64 selects a combination of keys to be used for dividing each of the plurality of tables so that the adjusted reference access frequency becomes lower. For example, the adjustment unit 64 may select a combination of keys with the lowest adjusted reference access frequency among all combinations of candidate keys.

In this case, as an example, the adjustment unit 64 has, for at least one table, a record range having an access frequency equal to or higher than the reference access frequency when the records in the table are arranged in the order of the key values. A key used for dividing the table may be selected on the condition that the table is not divided. For example, if the table T ₃ shown in FIG record range with a reference access frequency or access frequency when arranged in value order of the key, it is divided into seven. In such a case, for example, if the reference number is set to 6 or less, the adjustment unit 64 selects another key combination even if the reference access frequency of the key combination shown in FIG. 9 is the lowest. . Thereby, the adjustment part 64 can prevent that the number of the division | segmentation tables 40 increases too much.

  Subsequently, in step S <b> 26, the dividing unit 66 divides the at least one table into a plurality of divided tables 40 based on the access frequency for each record range of the at least one table. For example, the dividing unit 66 divides a record range having an access frequency equal to or higher than the reference access frequency from each of the plurality of tables. In this case, as an example, the dividing unit 66 arranges the records of the plurality of tables in the order of the key values of the combination selected in step S25, and sets each table in the state of being arranged in this manner as the reference access frequency. You may divide | segment according to the range of the key value used as the above.

  Subsequently, in step S <b> 27, the arrangement determining unit 68 determines that the division table 40 obtained by dividing the record range having an access frequency equal to or higher than the reference access frequency should be arranged in the selected table storage area 30. After completing the process of step S27, the selection unit 62 returns to step S20, selects the table storage area 30 with the next highest priority, and executes the processes of step S21 to step S27. When the selection unit 62 divides all the record ranges of all the tables and determines that they should be arranged in the table storage area 30, the flow ends.

  The determination device 20 as described above selects a key combination to be used for dividing each of the plurality of tables so that the reference access frequency becomes lower. Thereby, the determination apparatus 20 can store up to records with a lower access frequency in the table storage area 30 when arranging them in the predetermined table storage area 30 in order from a record with a higher access frequency. . That is, the determination device 20 can store a larger number of records in the predetermined table storage area 30 when arranging them in the predetermined table storage area 30 in order from the record with the higher access frequency. Thereby, according to the determination apparatus 20, an efficient table partition can be performed.

  Note that the determination unit 58 determines the combination of keys used for dividing the divided table 40 to be arranged in the table storage area 30 with the highest priority in the divided table to be arranged in the table storage area 30 with the second or higher priority. It may be used as a combination of keys used for 40 divisions. In this case, the adjustment unit 64 prioritizes the second and subsequent priority table storage areas 30 instead of adjusting the reference access frequency for each candidate key combination in steps S22 to S24. The reference access frequency is adjusted only for the combination of keys used for the division of the division table 40 to be arranged in the table storage area 30 with the highest rank. In this case, the adjustment unit 64 does not have to perform the process in step S25 for the table storage areas 30 with the second and subsequent priorities.

FIG. 11 is a diagram for explaining a processing example of the determination device 20 according to the modification of the present embodiment. More specifically, (A) in FIG. 11, the key _{K 11} of table _{T 1,} the key _{K 21} of table _{T 2,} in the case of a combination of keys _{K 31} of the table _{T 3,} the access frequency of each value of each key, An example of the 1st standard access frequency and the 2nd standard access frequency is shown. (B) in FIG. 11, the key _{K 11} of table _{T 1,} the key _{K 22} of table _{T 2,} in the case of a combination of keys _{K 31} of the table _{T 3,} the access frequency of each value of each key, a first reference access An example of a frequency and the 2nd standard access frequency is shown. Note that the determination device 20 according to this modification has substantially the same function and configuration as the determination device 20 according to the present embodiment described with reference to FIGS. Members having the same configuration and function are denoted by the same reference numerals in the drawings, and description thereof will be omitted except for differences.

  When ranking the access performance for each of the plurality of table storage areas 30 having different access performance, there may be a portion where the difference in access performance is large and a portion where the difference in access performance is small. In such a case, when the divided table 40 is arranged in order from the record with the higher access frequency, a larger number of records are stored in the table storage area 30 on the upper side than the portion with the large difference in access performance. It is preferable to do.

  For example, the first reference access frequency shown in FIGS. 11A and 11B is the reference used when calculating the divided table 40 to be placed in the first table storage area 30 with the priority order. An example of access frequency is shown. Further, the second reference access frequency shown in FIGS. 11A and 11B is arranged in an area in which both the first table storage area 30 and the second table storage area 30 are combined. An example of the reference access frequency used when calculating the division table 40 to be shown is shown.

  When the performance difference between the first table storage area 30 and the second table storage area 30 is larger than the performance difference between the second table storage area 30 and the third table storage area 30, the adjustment unit Preferably, 64 selects a combination of keys used for dividing each of the plurality of tables so that the first reference access frequency is lower. For example, in the example of FIG. 11, the adjustment unit 64 preferably selects the combination (A).

  In contrast, the performance difference between the second table storage area 30 and the third table storage area 30 is greater than the performance difference between the first table storage area 30 and the second table storage area 30. If larger, it is preferable that the adjustment unit 64 selects a combination of keys used for dividing each of the plurality of tables so that the second reference access frequency becomes lower regardless of whether the first reference access frequency is high or low. . For example, in the example of FIG. 11, the adjustment unit 64 preferably selects the combination (B).

  Therefore, the adjustment unit 64 receives the specification of the priority table storage area 30 having a large performance difference in advance so that the reference access frequency adjusted for the pre-specified priority table storage area 30 becomes lower. You may select the key combination used for each division | segmentation of a some table. Thereby, according to the determination apparatus 20 which concerns on this modification, a table partition can be performed more efficiently.

  FIG. 12A shows an example of an SQL statement issued to the database server 12 from an external device. FIG. 12B shows an example of a range specifying SQL statement for acquiring the access range of the table when the SQL statement of FIG. 12A is issued.

  For example, the specifying unit 54 specifies a range for obtaining the maximum value and the minimum value of the key value from at least one record satisfying the search condition specified by the access request in the acquired access request target table. The access range in the table may be specified by issuing an access request to the database server 12. As an example, the specifying unit 54 selects the maximum value of key values that are candidates for division from at least one record that satisfies the search condition specified by the select statement given to the database server 12 from an external device. A range specifying select statement for obtaining the minimum value may be issued to the database server 12.

  For example, assume that a select statement as shown in FIG. 12A is given to the database server 12 from an external device. In this case, the specifying unit 54 issues a range determination select statement as shown in FIG.

  That is, the specifying unit 54 duplicates a phrase representing the search condition (phrase after “where” in FIG. 12A), and one or a plurality of candidates in the record acquired by the search condition. Generate a select statement for range specification that obtains the maximum and minimum values of each key value. Then, the specifying unit 54 issues the generated range specifying select statement to the database server 12 and obtains a response result. In this manner, the specifying unit 54 can specify a range of values for each of one or more candidate keys in one or more records accessed by an access request for each table.

  FIG. 13 shows an example of a hardware configuration of a computer 1900 according to the present embodiment. A computer 1900 according to this embodiment is connected to a CPU peripheral unit having a CPU 2000, a RAM 2020, a graphic controller 2075, and a display device 2080 that are connected to each other by a host controller 2082, and to the host controller 2082 by an input / output controller 2084. Input / output unit having communication interface 2030, hard disk drive 2040, and CD-ROM drive 2060, and legacy input / output unit having ROM 2010, flexible disk drive 2050, and input / output chip 2070 connected to input / output controller 2084 With.

  The host controller 2082 connects the RAM 2020 to the CPU 2000 and the graphic controller 2075 that access the RAM 2020 at a high transfer rate. The CPU 2000 operates based on programs stored in the ROM 2010 and the RAM 2020 and controls each unit. The graphic controller 2075 acquires image data generated by the CPU 2000 or the like on a frame buffer provided in the RAM 2020 and displays it on the display device 2080. Instead of this, the graphic controller 2075 may include a frame buffer for storing image data generated by the CPU 2000 or the like.

  The input / output controller 2084 connects the host controller 2082 to the communication interface 2030, the hard disk drive 2040, and the CD-ROM drive 2060, which are relatively high-speed input / output devices. The communication interface 2030 communicates with other devices via a network. The hard disk drive 2040 stores programs and data used by the CPU 2000 in the computer 1900. The CD-ROM drive 2060 reads a program or data from the CD-ROM 2095 and provides it to the hard disk drive 2040 via the RAM 2020.

  The input / output controller 2084 is connected to the ROM 2010, the flexible disk drive 2050, and the relatively low-speed input / output device of the input / output chip 2070. The ROM 2010 stores a boot program that the computer 1900 executes at startup and / or a program that depends on the hardware of the computer 1900. The flexible disk drive 2050 reads a program or data from the flexible disk 2090 and provides it to the hard disk drive 2040 via the RAM 2020. The input / output chip 2070 connects the flexible disk drive 2050 to the input / output controller 2084 and inputs / outputs various input / output devices via, for example, a parallel port, a serial port, a keyboard port, a mouse port, and the like. Connect to controller 2084.

  A program provided to the hard disk drive 2040 via the RAM 2020 is stored in a recording medium such as the flexible disk 2090, the CD-ROM 2095, or an IC card and provided by the user. The program is read from the recording medium, installed in the hard disk drive 2040 in the computer 1900 via the RAM 2020, and executed by the CPU 2000.

  A program that is installed in the computer 1900 and causes the computer 1900 to function as the determination device 20 includes an acquisition module, a specific module, a calculation module, and a determination module. These programs or modules work on the CPU 2000 or the like to cause the computer 1900 to function as the acquisition unit 52, the identification unit 54, the calculation unit 56, and the determination unit 58, respectively.

  The information processing described in these programs is read into the computer 1900, whereby the acquisition unit 52, the identification unit 54, and the calculation unit 56, which are specific means in which the software and the various hardware resources described above cooperate. And functions as the determination unit 58. And the specific determination apparatus 20 according to the intended use is constructed | assembled by implement | achieving the calculation or processing of the information according to the intended use of the computer 1900 in this embodiment by these specific means.

  As an example, when communication is performed between the computer 1900 and an external device or the like, the CPU 2000 executes a communication program loaded on the RAM 2020 and executes a communication interface based on the processing content described in the communication program. A communication process is instructed to 2030. Under the control of the CPU 2000, the communication interface 2030 reads transmission data stored in a transmission buffer area or the like provided on a storage device such as the RAM 2020, the hard disk drive 2040, the flexible disk 2090, or the CD-ROM 2095, and sends it to the network. The reception data transmitted or received from the network is written into a reception buffer area or the like provided on the storage device. As described above, the communication interface 2030 may transfer transmission / reception data to / from the storage device by a DMA (direct memory access) method. Instead, the CPU 2000 transfers the storage device or the communication interface 2030 as a transfer source. The transmission / reception data may be transferred by reading the data from the data and writing the data to the communication interface 2030 or the storage device of the transfer destination.

  The CPU 2000 is all or necessary from among files or databases stored in an external storage device such as a hard disk drive 2040, a CD-ROM drive 2060 (CD-ROM 2095), and a flexible disk drive 2050 (flexible disk 2090). This portion is read into the RAM 2020 by DMA transfer or the like, and various processes are performed on the data on the RAM 2020. Then, CPU 2000 writes the processed data back to the external storage device by DMA transfer or the like. In such processing, since the RAM 2020 can be regarded as temporarily holding the contents of the external storage device, in the present embodiment, the RAM 2020 and the external storage device are collectively referred to as a memory, a storage unit, or a storage device. Various types of information such as various programs, data, tables, and databases in the present embodiment are stored on such a storage device and are subjected to information processing. Note that the CPU 2000 can also hold a part of the RAM 2020 in the cache memory and perform reading and writing on the cache memory. Even in such a form, the cache memory bears a part of the function of the RAM 2020. Therefore, in the present embodiment, the cache memory is also included in the RAM 2020, the memory, and / or the storage device unless otherwise indicated. To do.

  In addition, the CPU 2000 performs various operations, such as various operations, information processing, condition determination, information search / replacement, etc., described in the present embodiment, specified for the data read from the RAM 2020 by the instruction sequence of the program. Is written back to the RAM 2020. For example, when performing the condition determination, the CPU 2000 determines whether the various variables shown in the present embodiment satisfy the conditions such as large, small, above, below, equal, etc., compared to other variables or constants. When the condition is satisfied (or not satisfied), the program branches to a different instruction sequence or calls a subroutine.

  Further, the CPU 2000 can search for information stored in a file or database in the storage device. For example, in the case where a plurality of entries in which the attribute value of the second attribute is associated with the attribute value of the first attribute are stored in the storage device, the CPU 2000 displays the plurality of entries stored in the storage device. The entry that matches the condition in which the attribute value of the first attribute is specified is retrieved, and the attribute value of the second attribute that is stored in the entry is read, thereby associating with the first attribute that satisfies the predetermined condition The attribute value of the specified second attribute can be obtained.

  The program or module shown above may be stored in an external recording medium. As the recording medium, in addition to the flexible disk 2090 and the CD-ROM 2095, an optical recording medium such as DVD or CD, a magneto-optical recording medium such as MO, a tape medium, a semiconductor memory such as an IC card, and the like can be used. Further, a storage device such as a hard disk or RAM provided in a server system connected to a dedicated communication network or the Internet may be used as a recording medium, and the program may be provided to the computer 1900 via the network.

  As mentioned above, although this invention was demonstrated using embodiment, the technical scope of this invention is not limited to the range as described in the said embodiment. It will be apparent to those skilled in the art that various modifications or improvements can be added to the above-described embodiment. It is apparent from the scope of the claims that the embodiments added with such changes or improvements can be included in the technical scope of the present invention.

FIG. 1 shows a configuration of a database apparatus 10 according to the present embodiment. FIG. 2 shows an example of a plurality of table storage areas 30 and a plurality of divided tables 40 stored in the plurality of table storage areas 30. FIG. 3 shows a functional configuration of the determination apparatus 20 according to the present embodiment, together with a plurality of table storage areas 30. FIG. 4 shows a processing flow of the determination apparatus 20 according to the present embodiment. FIG. 5 shows an example of the access frequency for each range of candidate key values for each of a plurality of tables. FIG. 6 shows a processing flow of step S15 shown in FIG. 7, the key _{K 11} of table _{T 1,} the key _{K 21} of table _{T 2,} in the case of a combination of keys _{K 31} of the table _{T 3,} one example of the access frequency and the reference access frequency for each range of values for each key Show. 8, the key _{K 11} of table _{T 1,} the key _{K 22} of table _{T 2,} in the case of a combination of keys _{K 31} of the table _{T 3,} one example of the access frequency and the reference access frequency for each range of values for each key Show. 9, the key _{K 11} of table _{T 1,} the key _{K 21} of table _{T 2,} in the case of a combination of keys _{K 32} of the table _{T 3,} one example of the access frequency and the reference access frequency for each range of values for each key Show. Figure 10 is a key _{K 11} of table _{T 1,} the key _{K 22} of table _{T 2,} in the case of a combination of keys _{K 32} of the table _{T 3,} one example of the access frequency and the reference access frequency for each range of values for each key Show. FIG. 11 is a diagram for explaining a processing example of the determination device 20 according to the modification of the present embodiment. FIG. 12A shows an example of an SQL statement issued from an external device as one of the database requests. FIG. 12B shows an example of a range specifying SQL statement for acquiring the access range of the table when the SQL statement of FIG. 12A is issued. FIG. 13 shows an example of a hardware configuration of a computer 1900 according to the present embodiment.

Explanation of symbols

DESCRIPTION OF SYMBOLS 10 Database apparatus 12 Database server 14 Storage apparatus 16 Network 20 Determination apparatus 30 Table storage area 40 Partition table 52 Acquisition part 54 Identification part 56 Calculation part 58 Determination part 62 Selection part 64 Adjustment part 66 Division part 68 Arrangement determination part 1900 Computer 2000 CPU
2010 ROM
2020 RAM
2030 Communication interface 2040 Hard disk drive 2050 Flexible disk drive 2060 CD-ROM drive 2070 Input / output chip 2075 Graphic controller 2080 Display device 2082 Host controller 2084 Input / output controller 2090 Flexible disk 2095 CD-ROM

Claims (12)

A determination device that determines in which of a plurality of table storage areas each of a plurality of divided tables obtained by dividing a table in a database,
An acquisition unit for acquiring an access request for the table;
A specifying unit for specifying an access range in the table from the acquired access request;
A calculation unit that calculates an access frequency for each record range of the table from the access range of each access request;
Based on the access frequency for each record range of the table, a determination unit that determines the table storage area in which each of the divided tables is to be placed;
A determination device comprising: The determination unit is
A dividing unit that divides the table into the plurality of divided tables based on an access frequency for each record range of the table;
Based on the access frequency for each record range of the table, an arrangement determining unit that determines the table storage area in which each of the divided tables is to be arranged;
The determination apparatus according to claim 1, comprising: The acquisition unit acquires an access request for each of the plurality of tables,
The specifying unit specifies an access range in each table from the access request acquired for each table,
The calculation unit calculates the access frequency for each record range of the table from the access range of each access request for the table,
The dividing unit divides the at least one table into a plurality of divided tables based on an access frequency for each record range of the at least one table,
The arrangement determination unit determines the table storage area in which each of the divided tables and each of the non-divided tables should be arranged based on an access frequency for each record range of each of the tables. 2. The determination device according to 2. The determining unit adjusts the reference access frequency so that a total data size of a record range having an access frequency equal to or higher than a reference access frequency in the plurality of tables is within a usable size of the table storage area specified in advance. And an adjustment unit
The dividing unit generates the divided table by dividing a record range having an access frequency equal to or higher than the reference access frequency from each of the plurality of tables.
The determination apparatus according to claim 3, wherein the arrangement determination unit determines that the division table obtained by dividing a record range having an access frequency equal to or higher than the reference access frequency should be arranged in the pre-specified table storage area. . The plurality of table storage areas are prioritized in descending order of access performance of storage devices to which the respective table storage areas are allocated,
The determination unit further includes a selection unit that selects the table storage area in descending order of priority,
The adjustment unit adjusts the reference access frequency so that the total data size of the record range having an access frequency equal to or higher than the reference access frequency in the plurality of tables is within the usable size of the selected table storage area. And
The dividing unit generates the divided table by dividing a record range having an access frequency equal to or higher than the reference access frequency from each of the plurality of tables.
The determination apparatus according to claim 4, wherein the arrangement determination unit determines that the division table obtained by dividing a record range having an access frequency equal to or higher than the reference access frequency should be arranged in the selected table storage area. The dividing unit divides each table according to a range of key values,
The adjustment unit, for at least one of the tables, on condition that the record range having an access frequency equal to or higher than the reference access frequency is not divided above the reference number when the records of the table are arranged in the order of key values, The determination device according to claim 5, wherein a key used for dividing the table is selected. The dividing unit divides each table according to a range of key values,
The determination device according to claim 5, wherein the adjustment unit selects a key combination used for dividing each of the plurality of tables so that the adjusted reference access frequency is lower.   The said adjustment part selects the combination of the key used for each division | segmentation of these tables so that the said reference | standard access frequency adjusted with respect to the said table storage area of the priority designated beforehand may become lower. Determining device according to.   The specifying unit obtains a maximum and minimum value of a key value from at least one record satisfying a search condition specified by the access request in the table as a target of the acquired access request. The determination device according to claim 1, wherein an access range in the table is specified by issuing a request to a database server. A determination device that determines in which of a plurality of table storage areas each of a plurality of divided tables obtained by dividing a table in a database,
An acquisition unit that acquires an access request for each of the plurality of tables;
A specifying unit for specifying an access range in each table from the access request acquired for each table;
From the access range of each access request for each table, a calculation unit that calculates an access frequency for each record range of the table;
Based on the access frequency for each record range of the table, a determination unit that determines the table storage area in which each of the divided tables is to be placed;
With
The plurality of table storage areas are prioritized in descending order of access performance of storage devices to which the respective table storage areas are allocated,
The determination unit is
A selection unit that selects the table storage areas in descending order of priority;
An adjustment unit that adjusts the reference access frequency so that the total data size of the record range having an access frequency equal to or higher than the reference access frequency in the plurality of tables is within the usable size of the selected table storage area;
A dividing unit that generates a divided table by dividing a record range having an access frequency equal to or higher than the reference access frequency from each of the plurality of tables;
An arrangement determining unit for determining that the division table obtained by dividing the record range having an access frequency equal to or higher than the reference access frequency should be arranged in the selected table storage area;
Determining device. A program that causes a computer to function as a determining device that determines in which of a plurality of table storage areas each of a plurality of divided tables obtained by dividing a table in a database,
The computer,
An acquisition unit for acquiring an access request for the table;
A specifying unit for specifying an access range in the table from the acquired access request;
A calculation unit that calculates an access frequency for each record range of the table from the access range of each access request;
A program that functions as a determining unit that determines the table storage area in which each of the divided tables is to be arranged based on an access frequency for each record range of the table. A determination method for deciding which of a plurality of divided tables obtained by dividing a table in a database is to be placed in a plurality of table storage areas,
Obtain an access request for the table;
From the acquired access request, specify the access range in the table,
Calculate the access frequency for each record range of the table from the access range of each access request,
A determination method for determining the table storage area in which each of the divided tables is to be arranged based on an access frequency for each record range of the table.

Images