Database table partitioning allowing overlaps used in full text query
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.
1. A computer-implemented method comprising:
processing an original table to provide a plurality of smaller horizontally partitioned tables, the partitioned tables being configured to allow at least some overlapping data records, wherein processing an original table to provide a plurality of smaller horizontally partitioned tables comprises:
estimating an average number of words per partitioned table; and
calculating lower and upper word count boundaries for each partitioned table; and
using query modification script to generate a query configured to work with the partitioned tables.
2. The computer-implemented method of claim 1 , wherein the query modification script comprises SELECT query modification script.
3. The computer-implemented method of claim 1 , wherein the query modification script comprises INSERT query modification script.
4. The computer-implemented method of claim 1 , wherein the query modification script comprises DELETE query modification script.
5. The computer-implemented method of claim 1 , wherein the query modification script comprises UPDATE query modification script.
6. The computer-implemented method of claim 1 , wherein the partitioned tables are configured to enable full-text search queries.
7. The computer-implemented method of claim 1 , wherein the partitioned tables are configured to enable SQL full-text search queries.
8. The computer-implemented method of claim 1 , wherein estimating the average number of words per partitioned table comprises using the following formula: (total number of words in the original table)/(total number of partitions).
9. The computer-implemented method of claim 1 , wherein calculating lower and upper word count boundaries for each partitioned table comprises ascertaining whether a percentage of total number of words divided by an average word count exceeds a defined parameter and, if so, starting a new partitioned table.
10. The computer-implemented method of claim 1 , wherein using query modification script comprises creating and storing a linear-split SELECT procedure configured to enable full-text queries.
11. The computer-implemented method of claim 1 , wherein using query modification script comprises creating and storing a linear-split SELECT procedure configured to enable full-text queries based on word count of an input string.
12. The computer-implemented method of claim 1 , wherein using query modification script comprises creating and storing a binary-split SELECT procedure configured to enable full-text queries.
13. One or more computer readable storage memories comprising computer readable instructions which, when executed, implement a method comprising:
processing an original table to provide a plurality of smaller horizontally partitioned tables, the partitioned tables being configured to allow at least some overlapping data records, wherein processing an original table to provide a plurality of smaller horizontally partitioned tables comprises:
estimating an average number of words per partitioned table; and
calculating lower and upper word count boundaries for each partitioned table; and
using query modification script to generate a query configured to work with the partitioned tables.
14. The one or more computer readable storage memories of claim 13 , wherein estimating the average number of words per partitioned table comprises using the following formula: (total number of words in the original table)/(total number of partitions).
15. The one or more computer readable storage memories of claim 13 , wherein calculating lower and upper word count boundaries for each partitioned table comprises ascertaining whether a percentage of total number of words divided by an average word count exceeds a defined parameter and, if so, starting a new partitioned table.
16. The one or more computer readable storage memories of claim 13 , wherein using query modification script comprises creating and storing a linear-split SELECT procedure configured to enable full-text queries.
17. The one or more computer readable storage memories of claim 13 , wherein using query modification script comprises creating and storing a linear-split SELECT procedure configured to enable full-text queries based on word count of an input string.
18. The one or more computer readable storage memories of claim 13 , wherein using query modification script comprises creating and storing a binary-split SELECT procedure configured to enable full-text queries.