Database Fragmentation Reduction via Value Extraction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational databases face inefficiencies in storage due to fragmentation, particularly in sparsely populated tables where the same most common value is repeatedly stored, leading to excessive space usage and unused space between records.
Innovation Solution
Transforming the database table by storing the most common value as a 'null' value and removing the affected column, with non-null values stored in a separate table, and segmenting data into chunks for efficient storage in a distributed file system.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If data is stored in a relational database table with repeated most common values, then data can be stored in a structured manner, but storage space is excessive and fragmentation occurs
Solution Approach 1:
The patent extracts the most common value from the database table and stores it separately. Instead of storing the most common value repeatedly in each row, the system creates a separate table to store this value once, and references it from the main table. This extraction eliminates the repetition of the most common value, significantly reducing storage space while maintaining the ability to query and access the data effectively.
Solution Approach 2:
The patent segments the database table into two separate tables: a main table for storing non-most-common values and a separate table for storing the most common value. This segmentation allows the system to store data in a more efficient manner by separating the frequent value storage function from the data storage function, thereby reducing fragmentation and optimizing space utilization.
2Productivity
If data is overwritten in place in the same location, then storage operations become more efficient, but unused space between records increases
Solution Approach 1:
By extracting the most common value to a separate table, the patent eliminates the need for in-place overwriting operations that create fragmentation. The separate table stores the most common value once, and the main table stores references to this value, avoiding the space waste associated with repeated storage and subsequent overwriting operations.
3Quantity of substance
If null values are stored to represent missing data, then storage space is reduced, but database fragmentation increases
Solution Approach 1:
The patent extracts the most common value (which would otherwise be stored repeatedly including as null placeholders) and stores it separately in a dedicated table. This approach replaces the need to store null values repeatedly with a single storage location, reducing both storage space requirements and fragmentation while maintaining the ability to represent missing or default values efficiently.
Data Source
AI summary
Techniques to reduce database fragmentation are disclosed. In various embodiments, an indication is received to store an attribute value for an entity that has a row or other entry in a first database table, wherein the first database table does not have a column for the attribute. It is determined that the value corresponds to a mapped value that is associated with not having an entry in a separate, second database table configured to store the attribute. Entries are made in the second database table only for values of the attribute other than the mapped value. Application level software code is configured to associate absence of a row in the second database table with the mapped value for the attribute.