Database system with highly denormalized database structure
AssigneeWisoncsin Alumni Research Foundation
InventorsYinan Li, Jignesh M. Patel
Life of this patent
Estimating the term…
Abstract
A database system converts a multi-table relational database into a wide table incorporating all of the information of the relational database tables and converts queries for the relational database system into a form applicable to the wide table. Dictionary compression and/or columnar store allow faster query processing despite a substantially larger size of the wide table.
Claims17
A database comprising:
- a data storage device for holding a database having a relational database structure of multiple tables having data elements organized in logical columns related to attributes and logical rows linking the data elements of different attributes, the tables related by key values;
- a high speed memory having faster access speed than the data storage device and
- a processor in communication with the data storage device and the high speed memory and executing a stored program held in a non-transitory medium to:
- (a) combine the data elements of the multiple tables using an outer join to provide a wide database structure sized to store in high speed memory and having a wide table being a single logical table of rows and columns;
- (b) store the wide database structure in high speed memory;
- (c) after steps (a)-(b), receive a query for the relational database structure and convert it to a wide query for application to the wide table;
- (d) apply the wide query to the wide table as stored in high speed memory to select rows of the wide table according to the wide query using only data contained in the wide table for the selection;
- (e) output results from of the wide query applied to the wide database structure based on the selected rows;
- and
- f) repeat steps (c)-(d) for the wide database structure of steps (a)-(b);
- wherein the processor executes the stored program to compress the data elements in the wide table using a dictionary structure linking a data element to a compressed representation and storing the compressed representation in the wide table.
A method of executing a database query using an electronic computer in communication with a data storage system and a high-speed memory having faster access speed than the data storage system, the data storage system holding a database having a relational database structure of multiple tables having data elements organized in logical columns related to attributes and logical rows linking the data elements of different attributes, the tables related by key values;
- the method executing a program stored in non-transitory medium on the electronic computer to:
- (a) combine the data elements of the multiple tables using an outer join to provide a wide database structure sized to store in high speed memory and having a wide table being a single logical table of rows and columns;
- (b) store the wide database structure in high speed memory;
- (c) after steps (a)-(b), receive a query for the relational database structure and convert it to a wide query for application to the wide table;
- (d) apply the wide query to the wide table as stored in high speed memory to select rows of the wide table according to the wide query using only data contained in the wide table for the selection;
- (e) output results from of the wide query applied to the wide database structure based on the selected rows;
- and
- (f) repeat steps (c)-(d) for the wide database structure of steps (a)-(b);
- wherein compressing a size of the data elements in the wide table using a dictionary structure linking a data element to a compressed representation and storing the compressed representation in the wide table.
Dependencies are inferred from the claim text.