Database Column Indexing with Mismatched Data Types
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In relational database management systems, columns with inappropriate data types lead to inefficiencies, such as increased storage space requirements and inefficient query evaluation, due to erroneous data modeling or changes in the data model.
Innovation Solution
A method that detects mismatching data types in database columns using usage statistics, converts values to a more appropriate type, and builds an additional index as a physical access path, allowing for more efficient query processing without altering the existing data model.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If columns are declared with inappropriate data types (e.g., numeric values as character strings), then data modeling flexibility is maintained, but query evaluation efficiency deteriorates and storage space increases
Solution Approach 1:
The patent segments the data access path into two parts: the original column with inappropriate data type and an additional index with correct data type. This allows the system to maintain the original column for modeling flexibility while creating a separate access path for efficient query evaluation, resolving the contradiction between adaptability and productivity.
Solution Approach 2:
The patent introduces an intermediary additional index that mediates between the inappropriate column definition and the query requirements. The index serves as a bridge, allowing efficient query evaluation on correctly typed data without altering the original column's inappropriate data type, thus preserving both modeling flexibility and query efficiency.
2Adaptability or versatility
If columns are declared with inappropriate data types, then data modeling flexibility is maintained, but storage space requirements increase
Solution Approach 1:
The patent segments storage into the original column and an additional index structure. The index stores data in the appropriate format with compact representation, reducing overall storage requirements while maintaining the original flexible column definition for data modeling purposes.
Solution Approach 2:
The patent changes the storage parameters by creating an index with appropriate data type encoding. This allows the same logical data to be stored more efficiently in the index structure, reducing storage space while maintaining modeling flexibility in the original column.
3Productivity
If additional indexes are built over converted values, then query performance is improved, but device complexity increases
Solution Approach 1:
The patent performs preliminary conversion of values to appropriate data types during index creation. This preliminary action ensures that the index is built with correct data types from the start, enabling efficient query performance without requiring complex runtime conversions or additional processing complexity.
Data Source
AI summary
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.