Overlapping Table Partitions Resolve Full-Text Query Limits
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face limitations in table partitioning, particularly in full-text search queries, where overlapping of partitioned tables is not allowed, leading to performance issues and complexity in maintaining synchronization of data operations like INSERT, DELETE, and UPDATE.
Innovation Solution
The implementation of algorithms and SQL scripts for automatic horizontal table and index partitioning, allowing overlapping data records, which creates partitioned tables based on a column, modifies SELECT queries to work efficiently with these tables, and synchronizes insert/delete/update operations across the original and partitioned tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If table partitioning is implemented in database systems, then performance and manageability are improved, but overlapping of partitioned tables is not allowed which limits full-text search capabilities
Solution Approach 1:
The patent divides a large table into multiple smaller partitioned tables based on a partitioning column (e.g., date, category). Each partitioned table contains a subset of the original table's data, enabling improved query performance by limiting the search scope. The segmentation is achieved through automatic partitioning algorithms that split the original table into manageable chunks while maintaining data integrity and relationships.
Solution Approach 2:
The patent implements overlapping partitions where partitioned tables can contain duplicate data records that nest across multiple partitions. This nesting allows full-text search queries to access relevant data from multiple overlapping partitions simultaneously, resolving the contradiction between performance (through partitioning) and full-text search capability (through overlapping access).
2Speed
If partitioned tables are created for better performance, then query speed is improved, but synchronization of data operations becomes complex
Solution Approach 1:
The patent employs trigger-based automatic synchronization mechanisms where INSERT, UPDATE, and DELETE operations on the original table automatically propagate to the relevant partitioned tables through database triggers. This self-service approach eliminates manual synchronization efforts, reducing operational complexity while maintaining data consistency across partitions. The triggers automatically determine which partitions need updates based on the partitioning column values.
Solution Approach 2:
The system implements feedback mechanisms through triggers that monitor data changes in the original table and automatically respond by synchronizing changes to appropriate partitioned tables. This feedback loop ensures data consistency without requiring complex manual intervention, as the system automatically detects and propagates changes based on predefined synchronization rules.
3Ease of manufacture
If manual table partitioning is performed, then data can be divided into smaller elements, but automatic synchronization of operations is difficult to maintain
Solution Approach 1:
The patent replaces manual mechanical partitioning operations with automated algorithmic partitioning systems. The partitioning is performed automatically based on data characteristics and predefined rules, eliminating the need for manual table division. This automation extends to synchronization operations as well, where database triggers and stored procedures automatically handle data propagation without manual intervention.
Solution Approach 2:
The system dynamically adjusts partitioning parameters such as partition count, partition size, and partitioning column selection based on data characteristics and performance requirements. This parameter-based automation allows the partitioning strategy to adapt to changing data patterns while maintaining automatic synchronization through configurable trigger rules, reducing the need for manual reconfiguration.
Data Source
AI summary
Various embodiments provide a set of algorithms and scripts, e.g., SQL scripts, to perform automatic horizontal table and index partitioning that allows overlapping of data records, used in full-text search queries. In at least some embodiments, table creation and populating script is utilized to create a set of smaller tables partitioned by one column. In at least some embodiments, SELECT query modification script is provided to generate a query that works on the partitioned tables to achieve better performance. In at least some other embodiments, other query creation script is provided to modify the INSERT/UPDATE/DELETE queries for the original large table to automatically keep these queries synchronized with partitioned tables.