Abstract: Techniques are described herein for using a dynamic dictionary encoding with an associated hash table to support many-to-many join and aggregation operations. In an embodiment, within a first storage of a computing device, a first data structure that comprises a first dense grouping key column is created. The dense grouping key column includes a first plurality of dense grouping key values and one or more instances of a flag value. Within the first storage of the computing device, a second data structure is created that comprises a group-by column and a second dense grouping key column. The group-by column includes a plurality of group-by key values and the second dense grouping key column includes a second plurality of dense grouping key values. Within the first storage of the computing device, a third data structure, a hash table, is created that includes a hash bucket for each join key value that corresponds to an instance of the flag value.

Abstract: Methods, computer systems, and stored instructions are described herein for densely grouping dimensional data and/or aggregating data using a data structure, such as one that is constructed based on dimensional data. When smaller tables are joined with a larger table, a server may analyze the smaller tables first to determine actual value combinations that occur in the smaller tables, and these actual value combinations are used to more efficiently process the larger table. A dense data structure may be generated by processing dimensional data before processing data from fact table. The dense data structure may be generated by compressing ranges of values that are possible in dimensions into a range of values that actually occurs in the dimensions. The compressed range of values may be represented by dense set identifiers rather than the actual compressed range of values.

Abstract: Methods, computer systems, and stored instructions are described herein for inferring dimensional metadata from content of a query that references tables. A server analyzes the content of the query to determine which table(s) of the referenced tables could be fact tables and/or which table(s) of the referenced tables could be dimension tables. The server transforms the query to a transformed query that operates on a candidate fact table and candidate dimension table(s) of the referenced tables other than the candidate fact table. The transformed query, when executed, processes at least some data from dimension(s) using the candidate dimension table(s) before processing at least some data from the candidate fact table. Alternatively or additionally, the server generates an execution plan that operates on the candidate fact table and the candidate dimension table(s).

Abstract: Methods, computer systems, and stored instructions are described herein for transforming a query or execution plan to re-use stored data. A query processor stores a query or a representation thereof. Before executing the query, the query processor analyzes the query or representation to determine whether the query or representation could cause at least part of a data structure to be generated at least twice. Based at least in part on determining that the at least part of the data structure could be generated at least twice by the query or representation, the query processor transforms the query or representation. The transformed query or representation includes a first transformed sub-query or sub-operation that generates and stores the at least part of the data structure, and a second transformed sub-query or sub-operation uses the at least part of the data structure that was stored by the first transformed sub-query or sub-operation.