Systems and methods for maintaining aggregate tables in databases
First Claim
1. A computer-implemented method for maintaining aggregate tables in databases, at least a portion of the method being performed by a computing device comprising at least one processor, the method comprising:
- maintaining a database that comprises a primary table of data, an intermediate mapping table of metadata from the data in the primary table, and an aggregate table that aggregates the metadata from the intermediate mapping table, wherein at least the intermediate mapping table comprises a physical implementation of a denormalized contiguous linked list;
for each new item of data received during a time period;
updating the primary table with the new item of data;
identifying a row in the intermediate mapping table that specifies column values and determining that the new item of data matches the specified column values; and
updating the row in the intermediate mapping table with a pointer to the new item of data within the primary table of data immediately after updating the primary table with the new item of data, such that the row in the intermediate mapping table represents an aggregation of all rows within the primary table of data that match the specified column values and the row in the intermediate mapping table includes separate pointers to each row within the primary table of data that matches the specified column values;
at the end of the time period, updating the aggregate table with an aggregation of the metadata based on the metadata stored in the intermediate table rather than directly updating a count in the aggregate table to reflect each new item of data, to increase accuracy of the aggregate table;
retrieving at least one data mapping from the intermediate mapping table based on a search result obtained by retrieving the aggregation of the metadata from the aggregate table; and
retrieving at least one item of data from the primary table based on the at least one data mapping retrieved from the intermediate mapping table.
7 Assignments
0 Petitions
Accused Products
Abstract
The disclosed computer-implemented method for maintaining aggregate tables in databases may include (1) maintaining a database that comprises a primary table of data, an intermediate mapping table of metadata from the data in the primary table, and an aggregate table, (2) for each new item of data received during a time period, updating the primary table with the new item of data and updating at least one row in the intermediate mapping table with metadata from the new item of data, and (3) at the end of the time period, updating the aggregate table with an aggregation of the metadata based on the metadata stored in the intermediate table. Various other methods, systems, and computer-readable media are also disclosed.
124 Citations
20 Claims
-
1. A computer-implemented method for maintaining aggregate tables in databases, at least a portion of the method being performed by a computing device comprising at least one processor, the method comprising:
-
maintaining a database that comprises a primary table of data, an intermediate mapping table of metadata from the data in the primary table, and an aggregate table that aggregates the metadata from the intermediate mapping table, wherein at least the intermediate mapping table comprises a physical implementation of a denormalized contiguous linked list; for each new item of data received during a time period; updating the primary table with the new item of data; identifying a row in the intermediate mapping table that specifies column values and determining that the new item of data matches the specified column values; and updating the row in the intermediate mapping table with a pointer to the new item of data within the primary table of data immediately after updating the primary table with the new item of data, such that the row in the intermediate mapping table represents an aggregation of all rows within the primary table of data that match the specified column values and the row in the intermediate mapping table includes separate pointers to each row within the primary table of data that matches the specified column values; at the end of the time period, updating the aggregate table with an aggregation of the metadata based on the metadata stored in the intermediate table rather than directly updating a count in the aggregate table to reflect each new item of data, to increase accuracy of the aggregate table; retrieving at least one data mapping from the intermediate mapping table based on a search result obtained by retrieving the aggregation of the metadata from the aggregate table; and retrieving at least one item of data from the primary table based on the at least one data mapping retrieved from the intermediate mapping table. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 16, 17, 18)
-
-
9. A system for maintaining aggregate tables in databases, the system comprising:
-
a maintaining module, stored in memory, that maintains a database that by a primary table of data, an intermediate mapping table of metadata from the data in the primary table, and an aggregate table that aggregates the metadata from the intermediate mapping table, wherein at least the intermediate mapping table comprises a physical implementation of a denormalized contiguous linked list; a mapping module, stored in memory, that, for each new item of data received during a time period; updates the primary table with the new item of data; identifies a row in the intermediate mapping table that specifies column values and determines that the new item of data matches the specified column values; and updates the row in the intermediate mapping table with a pointer to the new item of data within the primary table of data immediately after the primary table is updated with the new item of data, such that the row in the intermediate mapping table represents an aggregation of all rows within the primary table of data that match the specified column values and the row in the intermediate mapping table includes separate pointers to each row within the primary table of data that matches the specified column values; an aggregation module, stored in memory, that at the end of the time period, updates the aggregate table with an aggregation of the metadata based on the metadata stored in the intermediate table rather than directly updating a count in the aggregate table to reflect each new item of data to increase accuracy of the aggregate table; a retrieving module, stored in memory, that retrieves at least one data mapping from the intermediate mapping table based on a search result obtained by retrieving the aggregation of the metadata from the aggregate table, and the retrieving module retrieves at least one item of data from the primary table based on the at least one data mapping retrieved from the intermediate mapping table; and at least one physical processor configured to execute the maintaining module, the mapping module, and the aggregation module. - View Dependent Claims (10, 11, 12, 13, 14, 19, 20)
-
-
15. A non-transitory computer-readable medium comprising one or more computer-readable instructions that, when executed by at least one processor of a computing device, cause the computing device to:
-
maintain a database that comprises a primary table of data, an intermediate mapping table of metadata from the data in the primary table, and an aggregate table that aggregates the metadata from the intermediate mapping table, wherein at least the intermediate mapping table comprises a physical implementation of a denormalized contiguous linked list; for each new item of data received during a time period; update the primary table with the new item of data; identify a row in the intermediate mapping table that specifies column values and determine that the new item of data matches the specified column values; and update the row in the intermediate mapping table with a pointer to the new item of data within the primary table of data immediately after the primary table is updated with the new item of data, such that the row in the intermediate mapping table represents an aggregation of all rows within the primary table of data that match the specified column values and the row in the intermediate mapping table includes separate pointers to each row within the primary table of data that matches the specified column values; at the end of the time period, update the aggregate table with an aggregation of the metadata based on the metadata stored in the intermediate table rather than directly updating a count in the aggregate table to reflect each new item of data, to increase accuracy of the aggregate table; retrieve at least one data mapping from the intermediate mapping table based on a search result obtained by retrieving the aggregation of the metadata from the aggregate table; and retrieve at least one item of data from the primary table based on the at least one data mapping retrieved from the intermediate mapping table.
-
Specification