JP7682117B2 - Data processing device and method - Google Patents

Description

The present invention relates generally to data processing, and more particularly to index design for database tables.

One example of this type of technology is the technology disclosed in Patent Document 1. The technology disclosed in Patent Document 1 extracts potential index candidates from a given query, evaluates the index candidates using optimization, and recommends indexes based on the evaluation results.

US10,762,085

As disclosed in Patent Document 1, there is a technique for recommending table indexes to be used for a given query.

However, for an environment or database other than the environment or database to which the table corresponding to the recommended index belongs, it is necessary to design a separate index for the table belonging to that other environment or database. This requires the use of comprehensive queries for that other environment or database, which results in a lot of work for index design.

In addition, when a new table is entered or a table is modified, it may be necessary to design an index for the entered or modified table. However, the index design must also use queries for the entered or modified table, which results in a lot of work for index design.

The data processing device acquires metadata of columns constituting a second table, and metadata similar to the acquired metadata is a first data catalog (data having column names and metadata for each column constituting a first table in a database). If the result of the determination is true, the data processing device identifies, from the first data catalog, a column name corresponding to the metadata similar to the acquired metadata, and generates a first index that includes the identified column name and is an index of the first table. The data processing device recommends the generated first index, or a second index that is generated based on the first index and is an index of the second table.

This invention can reduce the amount of work required to design a table index.

1 shows a configuration of a data processing device according to a first embodiment of the present invention. The structure of data catalog information is shown below. 1 shows an example of the structure of index definition information. 3 shows a flow of an index design support process according to the first embodiment. 1 shows a flow of a similarity determination process. 1 shows the flow of an index recommendation process. 13 shows a flow of an index design support process according to the second embodiment. 13 shows a flow of an index design support process according to the third embodiment. 13 shows a flow of an index design support process according to the fourth embodiment. 13 shows the flow of an index change determination process.

In the following description, an "interface unit" may refer to one or more interface devices. The one or more interface devices may be at least one of the following:
One or more I/O (Input/Output) interface devices. The I/O (Input/Output) interface devices are interface devices to at least one of the I/O devices and a remote display computer. The I/O interface device to the display computer may be a communications interface device. The at least one I/O device may be a user interface device, for example, either an input device such as a keyboard and a pointing device, or an output device such as a display device.
One or more communication interface devices. The one or more communication interface devices may be one or more homogeneous communication interface devices (e.g., one or more NICs (Network Interface Cards)) or two or more heterogeneous communication interface devices (e.g., a NIC and an HBA (Host Bus Adapter)).

In the following description, "memory" refers to one or more memory devices, which are an example of one or more storage devices, and may typically be a primary storage device. At least one memory device in the memory may be a volatile memory device or a non-volatile memory device.

In the following description, a "persistent storage device" may be one or more persistent storage devices, which are an example of one or more storage devices. A persistent storage device may typically be a non-volatile storage device (e.g., an auxiliary storage device), and more specifically, may be, for example, a hard disk drive (HDD), a solid state drive (SSD), or a non-volatile memory express (NVMe) drive.

In the following description, a "processor" may be one or more processor devices. The at least one processor device may typically be a microprocessor device such as a CPU (Central Processing Unit), but may also be other types of processor devices such as a GPU (Graphics Processing Unit). The at least one processor device may be a single-core or multi-core. The at least one processor device may be a processor core. The at least one processor device may also be a broader processor device such as a hardware circuit that performs part or all of the processing (e.g., an FPGA (Field-Programmable Gate Array), a CPLD (Complex Programmable Logic Device), or an ASIC (Application Specific Integrated Circuit)).

In the following description, functions are sometimes described using the expression "yyy part", but the functions may be realized by one or more computer programs being executed by a processor, or by one or more hardware circuits (e.g., FPGA or ASIC), or by a combination thereof. When a function is realized by a program being executed by a processor, the function may be at least a part of the processor, since the specified processing is performed using a storage device and/or an interface device, etc., as appropriate. Processing described with a function as the subject may be processing performed by a processor or a device having the processor. A program may be installed from a program source. The program source may be, for example, a program distribution computer or a storage medium (e.g., a non-transitory storage medium) that can be read by a computer. The description of each function is an example, and multiple functions may be combined into one function, or one function may be divided into multiple functions.

Several embodiments will be described below.
[First embodiment]

Figure 1 shows the configuration of a data processing device according to the first embodiment of the present invention.

The data processing device 100 includes an interface device 101, a persistent storage device 102, a memory 103, and a processor 104.

The interface device 101 is connected to a communication network (e.g., the Internet) 150. The interface device 101 communicates with a user device 110 via the communication network 150. The user device 110 may be a physical computer such as a personal computer, or a virtual computer based on a physical computer. The interface device 101 may also be connected to an input/output device as a user interface device instead of or in addition to the user device 110. That is, the data processing device 100 can input and output information between the user device 110 and the input/output device.

The persistent storage device 102 stores a database 121, data catalog information 122, and index definition information 123. Of the databases 121, the data catalog information 122, and the index definition information 123, at least the database 121 may exist in a plurality of instances. For example, there may be a database 121 belonging to a test environment (an example of a first environment) and a database 121 belonging to a production environment (an example of a second environment different from the first environment). There may also be a first database 121 and a second database 121 different from the first database (the environment to which the first database 121 belongs may be the same as or different from the environment to which the second database 121 belongs). The database 121 includes a table and an index. The data catalog information 122 and the index definition information 123 will be described later.

The memory 103 stores one or more computer programs. These programs are executed by the processor 104 to realize functions such as an index candidate generation unit 131 and an index design support unit 132. The index candidate generation unit 131 generates an index for a table used by a given query, using the query. The index candidate generation unit 131 may be a function according to existing technology. The index design support unit 132 performs index design support processing, which will be described later. The index design support unit 132 includes functions such as a similarity determination unit 136 and an index recommendation unit 137. The similarity determination unit 136 and the index recommendation unit 137 will be described later.

Although not shown, the memory 103 may store a computer program for implementing a DBMS (DataBase Management System) by the processor 104. At least one of the index candidate generation unit 131 and the index design support unit 132 may be included in the DBMS or may be a function outside the DBMS. The DBMS receives a query from a query source, and according to the query, refers to an index of a table specified in the query, and performs input/output to the database 121. The query source may be a device external to the data processing device 100, such as the user device 110, or may be an internal element of the data processing device 100 (for example, an application implemented by the processor 104 executing a computer program in memory).

Figure 2 shows the structure of data catalog information 122.

The data catalog information 122 includes a data catalog for each table in one or more databases 121. The data catalog is data that has column names 202 and metadata 210 for each column that makes up the table.

The data catalog information 122 has an entry for each table. Taking an entry for one table as an example, the entry includes information such as table name 201, column name 202, type/statistics 203, first attribute 204, second attribute 205, and description 206. A table is made up of one or more columns. Taking an example of one column, the column metadata 210 includes type/statistics 203, first attribute 204, second attribute 205, and description 206. The information 201 to 206 will be explained using one table and one column as an example.

Table name 201 indicates the name of the table. Column name 202 indicates the name of the column. Type/statistics 203 indicates the type of data in the column (e.g., numeric or character) and statistics of the data in the column (e.g., maximum, minimum, and average). First attribute 204 indicates the characteristics of the data in the column (e.g., whether there is duplicate data in the column, whether it is sorted, etc.). Second attribute 205 indicates how it is used (conditions (e.g., join key, sort key, filter condition) related to database operations (e.g., join, sort, filter) that can be specified in a query (e.g., SQL statement) to database 121). Description 206 indicates a description of the column (e.g., text statement).

Figure 3 shows an example of the configuration of index definition information 123.

The index definition information 123 has an entry for each index that has been generated in one or more databases 121. An entry for one index corresponds to the index definition for that index. Taking an entry for one index as an example, the entry has information such as index name 301, table name 302, index type 303, and column name 304.

Index name 301 indicates the name of the index. Table name 302 indicates the name of the table to which the index corresponds. Index type 303 indicates the type of index (e.g., B-tree, range). Column name 304 indicates the names of the columns included in the index.

As shown in FIG. 3, one or more indexes may exist for a table. Also, at least one index definition may be included in the data catalog for the table to which the index it represents corresponds.

Figure 4 shows the flow of the index design support process according to the first embodiment.

The index design support unit 132 acquires an index definition (for example, an index definition included in the data catalog of table A to which the index represented by the index definition corresponds) (S401). This index definition may be input from the user device 110 and stored in the persistent storage device 102, or may be acquired from the persistent storage device 102.

The index design support unit 132 identifies a column name 202 that matches the column name 304 of the index definition acquired in S401 from among the data catalogs having a table name that matches the table name (table name of table A) included in the index definition acquired in S401, and acquires metadata 210 corresponding to the identified column name 202 (S402). Note that the metadata acquired in S402 may be metadata acquired from table A of the table name included in the index definition acquired in S401 (e.g., metadata acquired from the table for each column represented by the index definition acquired in S401) instead of the metadata 210 acquired from the data catalog.

The similarity determination unit 136 of the index design support unit 132 performs a similarity determination process (S403).

In the similarity determination process, if there is metadata similar to the metadata 210 (metadata whose similarity S _i with the metadata 210 is equal to or greater than a predetermined threshold Th _i ) (S404: YES), the index recommendation unit 137 of the index design support unit 132 performs index recommendation processing.

Figure 5 shows the flow of the similarity determination process.

In the explanation of FIG. 5, the comparison source metadata is the metadata acquired before the similarity determination process (in this embodiment, the metadata acquired in S402). The comparison target metadata is the metadata to be compared with the comparison source metadata, and is the metadata of a column of table B (metadata in the data catalog of table B) that is different from table A having a column corresponding to the comparison source metadata. For one comparison source metadata, the metadata of all columns in table B may be set as the comparison target metadata. The similarity determination process will be explained using one comparison source metadata and one comparison target metadata as an example. Note that for one comparison source metadata, the comparison target metadata may be only the metadata of a column whose column name matches the column name of the column corresponding to the comparison source metadata.

The similarity determination unit 136 determines whether the column name 202 corresponding to the metadata of the comparison source matches the column name 202 corresponding to the metadata of the comparison target (S501). If the determination result of S501 is true (S501: YES), the similarity determination unit 136 adds a predetermined score (for example, "1") to the current score _Sc .

The similarity determination unit 136 also determines whether the data type represented by the type/statistics 203 in the metadata of the comparison source matches the data type represented by the type/statistics 203 in the metadata of the comparison target (S502). If the determination result of S502 is true (S502: YES), the similarity determination unit 136 adds a predetermined score (for example, "1") to the current score _Sc .

Thereafter, the similarity determination unit 136 adds a score according to the degree of agreement between the statistics represented by the type/statistics 203 in the metadata of the comparison source and the statistics represented by the type/statistics 203 in the metadata of the comparison target to the current score S _c (S505). The "degree of agreement" referred to in this paragraph depends on the number of elements (e.g., maximum values, minimum values, or average values) that match between the statistics represented by the type/statistics 203 in the metadata of the comparison source and the statistics represented by the type/statistics 203 in the metadata of the comparison target.

The similarity determination unit 136 determines whether the data characteristics (first attribute 204) in the comparison source metadata match the data characteristics (first attribute 204) in the comparison target metadata (S506). If the determination result in S506 is false (S506: NO), the similarity determination unit 136 determines that the comparison target metadata is not similar to the comparison source metadata (S511). In this case, the score S _c may be reset to an initial value.

If the result of the determination in S506 is true (S506: YES), the similarity determination unit 136 determines whether the usage (second attribute 205) in the comparison source metadata matches the usage (second attribute 205) in the comparison target metadata (S507). If the result of the determination in S507 is false (S507: NO), S511 is performed.

If the result of the determination in S507 is true (S507: YES), the similarity determination unit 136 adds a score according to the degree of agreement between the description 206 (text sentence) in the metadata of the comparison source and the description 206 in the metadata of the comparison destination to the current score S _c (S508). The "degree of agreement" referred to in this paragraph depends on the number of elements that match between the description 206 in the metadata of the comparison source and the description 206 in the metadata of the comparison destination. The "element" here may be a word or a combination of a word and its position. The element is identified from the description 206 (description) by the similarity determination unit 136.

The similarity determination unit 136 determines whether the current score _Sc is equal to or greater than a predetermined threshold _Thc (S509). If the determination result of S509 is false (S509: NO), S511 is performed.

If the determination result of S509 is true (S509: YES), the similarity determination unit 136 determines that the comparison target metadata is similar to the comparison source metadata (S510). In this case, the score S _c may be the similarity S _i described above, and the threshold Th _c of the score S _c may be the threshold Th _i described above. In other words, if the determination result of S509 is true for at least one comparison source metadata, the determination result of S404 in FIG. 4 may be true.

Figure 6 shows the flow of the index recommendation process.

The index recommendation unit 137 lists the column names 304 of columns corresponding to the comparison target metadata that are determined to be similar in the similarity determination process for the same table B (S601).

The index recommendation unit 137 generates an index (index candidate) having the listed column names 304 (S602) and recommends the index (index candidate) as an index for table B (S603).

The index generated in S602 may be of the same type as the index type represented by the index definition obtained in S401.

The "recommendation" in S603 may mean that the generated index (index candidate) is presented to the user (index designer) (for example, displayed on the user device 110), or that the generated index (index candidate) is stored in the database 121 that includes Table B as one of the indexes for Table B. The "recommendation" in S603 may mean that a definition of the generated index (index candidate) is output to the memory 103 or the persistent storage device 102.

According to the first embodiment, it is possible to automatically generate and recommend an index (index candidate) for table B using a data catalog identified based on the index definition of the index for table A. As a result, the number of steps required to design the index for table B is reduced. Note that the index for table A may be generated by the index candidate generation unit 131.
Second Embodiment

The second embodiment will now be described. In doing so, the differences from the first embodiment will be mainly described, and the description of the points in common with the first embodiment will be omitted or simplified (this also applies to the third and fourth embodiments).

Figure 7 shows the flow of the index design support process according to the second embodiment.

Instead of S401 and S402, S701 and S702 are performed.

In S701, the index design support unit 132 acquires metadata for a column of table A that is not registered in the data catalog, and an index for table A. The column corresponding to the acquired metadata may be the column represented by the acquired index.

In S702, the index design support unit 132 obtains the column name 304 included in the index definition of the index obtained in S701.

S703 to S705 are substantially the same as S403 to S405. In S703, the metadata to be compared may be the metadata obtained in S701 (metadata for columns of Table A).

According to the second embodiment, it is possible to automatically generate and recommend an index (index candidate) for table B based on the index definition of table A. As a result, the number of steps required to design an index for table B is reduced.
[Third embodiment]

Figure 8 shows the flow of the index design support process according to the third embodiment.

S801 is performed instead of S401 and S402.

In S801, the index design support unit 132 obtains metadata for columns of table A.

S802 to S804 are substantially the same as S403 to S405. In S802, the metadata to be compared may be the metadata obtained in S801 (metadata for columns of Table A).

According to the third embodiment, it is possible to automatically generate and recommend an index (index candidate) for table B based on the metadata of the columns of table A. As a result, the number of steps required to design an index for table B is reduced.
[Fourth embodiment]

Figure 9 shows the flow of the index design support process according to the fourth embodiment.

Instead of S401 and S402, S901 to S903 are performed.

In S901, the index design support module 132 acquires changes in metadata of columns of new or updated table A. Specifically, for example, any of the following may be used.
The new table A may be a table newly stored in the database 121. A data catalog of the new table A may be added to the data catalog information 122. Metadata for each column of table A may be obtained from the added data catalog (or from the new table A).
The updated table A may be a table in which at least one column has been changed (e.g., added or updated). The update of table A may update at least one metadata of the data catalog of table A. Metadata of the changed column may be obtained from the updated data catalog (or from the changed column of the updated table A).

In S902, the index design support unit 132 performs index change determination processing.

In S903, the index design support unit 132 determines whether the result of the index change determination process indicates that a change is required.

If the determination result of S903 is true (S903: YES), S904 to S906 are performed. S904 to S906 may be substantially the same as S403 to S405. For example, the following may be adopted.

That is, in S904, the metadata to be compared may be the metadata acquired in S901 (the metadata for each column of the new Table A, or the changed metadata in the updated Table A).

In addition, in S906 (specifically, in S603 in FIG. 6), the recommended index may be one or both of index B (index of table B) generated in S602 and index A (index (index candidate) of new table A or updated table A) generated by the index recommendation unit 137 based on index B. Index A may be, for example, an index of the same index type as index B.

Index A may be generated using index A' of table A before the update, in addition to index B. The column names of the columns in index A that remain unchanged may be the same as the column names in index A'. Index A may be, for example, an index of the same index type as index A'.

Figure 10 shows the flow of the index change determination process.

The index design support unit 132 determines whether the sorting status of the first attribute 204 of the metadata acquired in S901 differs from the sorting status of the first attribute 204 of the metadata of the column before the change (S1001).

If the result of the determination in S1001 is true (S1001: YES), the index design support unit 132 determines that the range index whose index type is "range" among one or more indexes of table A before the change is an index that needs to be changed (S1002).

If the judgment result of S1001 is false (S1001: NO), the index design support unit 132 judges whether the second attribute 205 (the one used) of the metadata acquired in S901 is different from the second attribute 205 of the metadata of the column before the change (S1003).

If the result of the determination in S1003 is true (S1003: YES), the index design support unit 132 determines that the B-tree index whose index type is "B-tree" among one or more indexes of table A before the change is an index that needs to be changed (S1004).

If the judgment result of S1004 is false (S1004: NO), the index design support unit 132 judges whether the metadata acquired in S901 is metadata for a new table A (S1005).

If the result of the determination in S1005 is true (S1005: YES), the index design support unit 132 determines that a change is required (generation of index A) (S1006).

If the judgment result of any of S1001, S1003, and S1005 is true, the judgment result of S903 is true. On the other hand, if the judgment result of any of S1001, S1003, and S1005 is false, the judgment result of S903 is false.

According to the fourth embodiment, it is possible to automatically generate index B (an index for table B) based on the metadata of columns in table A, and to recommend one or both of index B and index A (an index for table A that is generated based on index B). As a result, the amount of work required to design index B and index A is reduced.

The first to fourth embodiments described above can be summarized, for example, as follows. Note that the summary below may include explanations of modified examples and supplementary explanations.

The data processing device 100 includes a storage device (e.g., including a persistent storage device 102 and a memory 103) and a processor 104. The storage device stores a data catalog B (an example of a first data catalog) having column names 202 and metadata 210 for each column constituting table B (an example of a first table) in a database 121.

The processor 104 acquires metadata of columns constituting table A (an example of a second table). The processor 104 determines whether metadata 210 similar to the acquired metadata is present in the data catalog B. If the result of the determination is true, the processor 104 identifies, from the data catalog B, a column name 202 corresponding to the metadata 210 similar to the acquired metadata. The processor 104 generates an index B (an example of a first index) that includes the identified column name 202 and is an index of table B. The processor 104 recommends at least one of the generated index B and index A that is generated based on index B and is an index of table A.

In this way, as an index for table B, which has a column with metadata similar to that of a column in table A, index B containing the column name of the column with the similar metadata is automatically generated and recommended. This reduces the amount of work required to design index B.

Data catalog B for table B may be prepared using existing technology. In general, data catalog information 122 including data catalog B is prepared to obtain an overview of the contents of database 121 (e.g., what tables exist). Using data catalog B in such data catalog information 122, automatic generation of index B (index candidate) is realized. For this reason, there is no need to create a query related to table B in order to generate index B.

The environment to which table B belongs (e.g., a production environment) may be different from the environment to which table A belongs (e.g., a test environment). Furthermore, the database 121 in which table B is included may be different from the database 121 in which table A is included. For example, one of the environments or databases 121 may be a local environment or database 121, and the other may be a remote environment or database 121 (e.g., a database in the cloud or cloud storage).

The storage device may store index definition information 123 including an index definition representing one or more indexes of table A. Taking one index A' as an example, the index definition may include column names of table A that are included in index A'. Processor 104 may identify column names for index A' from the index definition. The obtained metadata may be metadata of columns corresponding to the identified column names, and the recommended index may be index B. In this way, index A' of table A can be used to generate and recommend index B for table B without a query on table B.

Table A may be a newly entered table or a table that has had a column change (e.g., a column added or updated). The recommended index may be at least index A out of index B and index A. As a result, index A (index candidate) is automatically generated and recommended for newly entered table A or table A that has had a column change, without a query on table A.

The metadata acquired for table A may be metadata of the changed column. This metadata may be acquired from the data catalog of table A. The processor 104 may identify the attribute type (e.g., data characteristics or usage) of the changed data among the acquired metadata, and may determine whether or not the index needs to be changed based on the identified attribute type. If the determination result is true, the processor 104 may identify an index type (e.g., "range" or "B-tree") corresponding to the identified attribute type, and may generate index B using an index of the identified index type among one or more indexes already existing for table B. Index A is generated based on this index B. Therefore, the index type of index B and the index type of the generated index A are the same. Specifically, if the identified attribute type is whether or not sorted, or how it is used, it may be determined that the index needs to be changed. If the identified attribute type is whether or not sorted, the identified index type may be range. If the specified attribute type is the way it is used, the specified index type may be a B-tree. In this way, an index of an appropriate index type for the attribute type of the changed data is selected from one or more indexes already existing for table B, and index B and index A are generated based on the selected index. In other words, index A of the appropriate index type can be generated efficiently.

The metadata for each column may include the characteristics of the data in that column and how it is used. Metadata that is similar to the acquired metadata may have the same data characteristics and how it is used, and the degree of similarity of the acquired metadata other than the data characteristics and how it is used may be equal to or greater than a certain value. This makes it possible to identify appropriate metadata as similar metadata.

Although several embodiments have been described above, these are merely examples for the purpose of explaining the present invention, and the scope of the present invention is not intended to be limited to these embodiments. The present invention can also be implemented in various other forms.

100...Data processing device

Claims (7)

A data processing device including a storage device and a processor,
the storage device stores a first data catalog having column names and metadata for each column constituting a first table in a database;
The processor,
Obtaining metadata for columns that make up a second table;
determining whether the first data catalog contains metadata similar to the retrieved metadata;
If the result of the determination is true, identifying column names from the first data catalog that correspond to metadata similar to the retrieved metadata;
generating a first index that includes the identified column names and that is an index on the first table;
recommending at least one of the generated first index and a second index generated based on the first index and being an index of the second table;
Data processing device. the storage device stores an index definition for the second table;
the index definition includes column names of the second table that are included in an index of the second table;
The processor identifies column names from the index definition;
the obtained metadata is metadata for a column corresponding to the identified column name;
The recommended index is the first index.
2. A data processing apparatus according to claim 1. The second table is a table that has been newly entered or has had a column change;
the recommended index is at least the second index of the first index and the second index;
2. A data processing apparatus according to claim 1. the retrieved metadata is metadata for columns that have changed;
The processor,
Identifying different attribute types between the acquired metadata and the metadata of the column before the change;
Based on the identified attribute type, determining whether or not the index needs to be changed;
If the result of the determination is true,
Identifying an index type corresponding to the identified attribute type;
generating the first index using an index of the specified index type from among one or more indexes already existing for the first table;
4. A data processing apparatus according to claim 3. If the identified attribute type is whether or not to sort, or a usage that is a condition related to a database operation that can be specified in a query, the determination result is true;
When the specified attribute type is sorted or not, the specified index type is range;
If the specified attribute type is usage, the specified index type is B-tree.
5. A data processing apparatus according to claim 4. The metadata for each column includes data representing:
A data characteristic that is a characteristic of the data in the column; and
Usage, which is a condition that can be specified in a query about the database operations that are performed using the data in that column;
The metadata similar to the acquired metadata is data that satisfies the following conditions:
Both the data characteristics and the usage are consistent,
The similarity of the acquired metadata with respect to data other than the data characteristics and the usage is equal to or greater than a certain value.
2. A data processing apparatus according to claim 1. A data processing method carried out by a computer, comprising the steps of:
(A) obtaining metadata for columns constituting a second table;
(B) determining whether metadata similar to the retrieved metadata is present in a first data catalog;
(C) if the result of the determination is true, identifying, from the first data catalog, column names corresponding to metadata similar to the retrieved metadata;
(D) generating a first index that includes the identified column name, the first index being an index on a first table;
(E) recommending at least one of the generated first index and a second index generated based on the first index and being an index of the second table;
the first table is a table in a database;
The first data catalog is data having column names and metadata for each column constituting the first table.
Data processing methods.

Images