Automatic and dynamic design of cache groups

A method for automatic and dynamic adaptation of cache groups in a database system having one or more processors, the method comprising:

• creating an anchored table set configured to hold one or more cache tables;
• collecting value-based predicates in a database query;
• creating a cache table for each value-based predicate and marking a filling column in each respective cache table;
• adding each cache table to the anchored table set;
• determining addable referential cache constraints between each cache table in the anchored table set;
• determining costs for each addable referential cache constraint;
• adding the referential cache constraint based on the determined cost of the respective referential cache constraint;
• checking the cache tables in the anchored table set for heterogeneous cycles between tables;
• and
• modifying referential cache constraints between cache tables when a heterogeneous cycle exists between cache tables, wherein
• the determining of addable referential cache constraints, the determining of costs for each addable referential cache constraint, the adding of the referential cache constraint, the checking of the cache tables in the anchored table set, and the modifying of the referential cache constraints is repeated until all possible addable referential cache constraints have been exhausted.

A non-transitory computer-readable storage medium having computer readable code embodied therein and capable of being stored in a memory as computer program instructions that, when executed by a computer having one or more processors, causes the computer to at least perform:

• creating an anchored table set configured to hold one or more cache tables;
• collecting value-based predicates in a database query;
• creating a cache table for each value-based predicate and marking a filling column in each respective cache table;
• adding each cache table to the anchored table set;
• determining addable referential cache constraints between each cache table in the anchored table set;
• determining costs for each addable referential cache constraint;
• adding the referential cache constraint based on the determined cost of the respective referential cache constraint;
• checking the cache tables in the anchored table set for heterogeneous cycles between tables;
• and
• modifying referential cache constraints between cache tables when a heterogeneous cycle exists between cache tables, wherein
• the determining of addable referential cache constraints, the determining of costs for each addable referential cache constraint, the adding of the referential cache constraint, the checking of the cache tables in the anchored table set, and the modifying of the referential cache constraints is repeated until all possible addable referential cache constraints have been exhausted.

A database system, comprising:

• a backend database having a memory and one or more processors and storing backend data;
• and
• one or more cache instances having a memory and one or more processors and operatively communicating with the backend database, the one or more processors being configured to:
• create an anchored table set configured to hold one or more cache tables;
• collect value-based predicates in a database query;
• create a cache table for each value-based predicate and marking a filling column in each respective cache table;
• add each cache table to the anchored table set;
• determine addable referential cache constraints between each cache table in the anchored table set;
• determine costs for each addable referential cache constraint;
• add the referential cache constraint based on the determined cost of the respective referential cache constraint;
• check the cache tables in the anchored table set for heterogeneous cycles between tables;
• and
• modify referential cache constraints between cache tables when a heterogeneous cycle exists between cache tables, wherein
• the determining of addable referential cache constraints, the determining of costs for each addable referential cache constraint, the adding of the referential cache constraint, the checking of the cache tables in the anchored table set, and the modifying of the referential cache constraints is repeated until all possible addable referential cache constraints have been exhausted.