Efficient indexing of columns with inappropriate data types in relational databases
A computer-implemented method, a computer program product, and a computer system for detecting an inappropriate data type of a column in a database and building a physical access path over a correct data type. The computer system detects in a table a candidate column with a first data type that has a mismatching type definition, using database usage statistics. The computer system determines whether it is possible to build an additional index as an access path over values with a second data type. The computer system, in response to determining that it is possible to build the additional index, converts values in the candidate column to the values with the second data type. The computer system builds the additional index over the values with the second data type in the table. The computer system generates a query plan operator for the additional index.
1. A computer-implemented method for detecting an inappropriate data type of a column in a database and building a physical access path over a correct data type, the method comprising:
analyzing database usage statistics of a table in the database to detect high-cost conversion operations;
returning a list of candidate columns that have mismatching type definitions and have execution costs exceeding a predetermined threshold;
scanning data in a candidate column with a first data type of values to determine whether it is possible to build an additional index as an access path over a second data type of values;
in response to determining that it is possible to build the additional index, converting the first data type of values in the candidate column to the second data type of values in a temporary column in the table;
inserting converted attributes into an index data structure, to build the additional index over the values with the second data type in the table;
removing the temporary column after the additional index is built; and
generating, in a query plan for the database, a query plan operator for the additional index, wherein the query plan operator for the additional index is a more cost-efficient operator.
2. The computer-implemented method of claim 1 , further comprising:
gathering and evaluating the database usage statistics for the table.
3. The computer-implemented method of claim 1 , further comprising:
creating the temporary column with the second data type in the table; and
storing converted column values in the temporary column in the table.
4. The computer-implemented method of claim 1 , further comprising:
registering the additional index in a metadata catalog.
5. The computer-implemented method of claim 1 , wherein the additional index is one of a hash-based and tree-based index.
6. The computer-implemented method of claim 1 , further comprising:
removing the candidate column from a list of candidate columns, in response to determining that conversion of the candidate column fails by a certain percentage of column values in the candidate column.
7. A computer program product for detecting an inappropriate data type of a column in a database and building a physical access path over a correct data type, the computer program product comprising a computer readable storage medium having program instructions embodied therewith, the program instructions executable by one or more processors, the program instructions executable to:
using analyze database usage statistics of a table in the database to detect high-cost conversion operations;
return a list of candidate columns that have mismatching type definitions and have execution costs exceeding a predetermined threshold;
scan data in a candidate column with a first data type of values to determine whether it is possible to build an additional index as an access path over a second data type of values;
in response to determining that it is possible to build the additional index, convert the first data type of values in the candidate column to the second data type of values in a temporary column in the table;
insert converted attributes into an index data structure, to build the additional index over the values with the second data type in the table;
remove the temporary column from the table after the additional index is built; and
generate, in a query plan for the database, a query plan operator for the additional index, wherein the query plan operator for the additional index is a more cost-efficient operator.
8. The computer program product of claim 7 , further comprising the program instructions executable to:
gather and evaluate the database usage statistics for the table.
9. The computer program product of claim 7 , further comprising the program instructions executable to:
create the temporary column with the second data type in the table; and
store converted column values in the temporary column in the table.
10. The computer program product of claim 7 , further comprising the program instructions executable to:
register the additional index in a metadata catalog.
11. The computer program product of claim 7 , wherein the additional index is one of a hash-based and tree-based index.
12. The computer program product of claim 7 , further comprising program instructions executable to:
remove the candidate column from a list of candidate columns, in response to determining that conversion of the candidate column fails by a certain percentage of column values in the candidate column.
13. A computer system for detecting an inappropriate data type of a column in a database and building a physical access path over a correct data type, the computer system comprising one or more processors, one or more computer readable tangible storage devices, and program instructions stored on at least one of the one or more computer readable tangible storage devices for execution by at least one of the one or more processors, the program instructions executable to:
analyze database usage statistics of a table in the database to detect high-cost conversion operations;
return a list of candidate columns that have mismatching type definitions and have execution costs exceeding a predetermined threshold;
scan data in a candidate column with a first data type of values to determine whether it is possible to build an additional index as an access path over a second data type of values;
in response to determining that it is possible to build the additional index, convert the first data type of values in the candidate column to the second data type of values in a temporary column in the table;
insert converted attributes into an index data structure, to build the additional index over the values with the second data type in the table;
remove the temporary column from the table after the additional index is built; and
generate, in a query plan for the database, a query plan operator for the additional index, wherein the query plan operator for the additional index is a more cost-efficient operator.
14. The computer system of claim 13 , further comprising the program instructions executable to:
gather and evaluate the database usage statistics for the table.
15. The computer system of claim 13 , further comprising the program instructions executable to:
create the temporary column with the second data type in the table; and
store converted column values in the temporary column in the table.
16. The computer system of claim 13 , further comprising the program instructions executable to:
register the additional index in a metadata catalog.
17. The computer system of claim 13 , further comprising program instructions executable to:
remove the candidate column from a list of candidate columns, in response to determining that conversion of the candidate column fails by a certain percentage of column values in the candidate column.