Techniques for dictionary based join and aggregation

Abstract

Techniques are described herein for performing join and aggregation operations for a received query using column dictionaries. In an embodiment, a query is received that requests to aggregate a measure column of a fact table based on an aggregate function and join the fact table with a dimension table on a join key column. Data of the fact table may be stored in one or more storage data units, for example a particular data portion of the fact table may be stored on a particular data storage unit. The respective data portion may include one or more column vectors corresponding to one or more columns of the fact table in which a cell element corresponds to a value at a particular row and a particular column of the one or more columns of the fact table. The cell element of the one or more column vectors includes a dictionary encoded value of the value at the particular column and row. This dictionary encoded value is mapped to the value of by a dictionary data structure of the particular column. In an embodiment, in response to receiving the query, using the query, for a particular storage unit, the DBMS identifies a fact join key vector of the one or more column vectors of the particular data storage unit and a fact join key dictionary data structure corresponding to the fact join key vector. The DBMS generates a dictionary-grouping key mapping based on the fact join key dictionary data structure and a dense grouping key data structure. Such a dense grouping key data structure represents a mapping of unique dimension join key values of the join key column of the dimension table to grouping key values of a grouping key of the dimension table. Based on the generated dictionary-grouping key mapping and the fact join key vector, the DBMS may generate a transformed vector, which represents the grouping key values of the dictionary-grouping key mapping arranged according to the fact join key vector. Alternatively, the dictionary-grouping key mapping may be directly accessed during the aggregation. Based on the generated transformed vector and/or by directly accessing the dictionary-grouping key mapping, the DBMS aggregates a column vector in that particular data storage unit that corresponds to the measure column in the received query.