Oracle International Corporation

Albert A Hopeman

James E Carey

Caleb Welton

Ekrem Soylemez

Bruce Golbus

Martin Roth

Adam Kociubes

Grant E Hicks

Charles P Venezia, Jr

Igor Lyubashevskiy

Andrew H Goldberg

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.

Techniques for dictionary based join and aggregation

A method and data structure is described that builds summary information using processor time that is usually proportional to the size of input data and a depth of hierarchies for a plurality of attributes. The output of computation is stored in a smaller area by eliminating redundant storage and computation. An index is generated which includes tuples or rows that include lower bound values for each of the attributes, values of children of the lower bound values based on the hierarchies of the attributes, and coverage class indicators of the coverage classes of the children.

Method and apparatus for storage and retrieval of information in compressed cubes

In a multidimensional database, an aggregation operation is performed in an optimal manner by storing the values included in the/aggregation operation on the same disk page. A sparsity manager determines aggregate values that are computed from other data values during the aggregation operation. Each aggregate value is associated with one or more data values that are used during the aggregation operation to compute the aggregate value. The sparsity manager stores the associated data values in proximity to each other, such as on the same disk page, so that multiple disk page fetches may not be required for the same set of data values during the aggregation operation. The data values used in the aggregation operation can therefore be fetched once from a disk page, and thereafter are found in memory, such as on a cache page corresponding to the disk page. In this manner, multiple fetches for the same disk page during the aggregation operation are avoided.

Multidimensional database storage and retrieval system

In a multi-threaded computing environment, a shared cache system reduces the amount of redundant information stored in memory. A cache memory area provides both global readable data and private writable data to processing threads. A particular processing thread accesses data by first checking its private views of modified data and then its global views of read-only data. Uncached data is read into a cache buffer for global access. If write access is required by the processing thread, the data is copied into a new cache buffer, which is assigned to the processing thread's private view. The particular shared cache system supports generational views of data. The system is particularly useful in on-line analytical processing of multi-dimensional databases. In one embodiment, a dedicated collector reclaims cache memory blocks for the processing threads. By utilizing a dedicated collector thread, any processing penalty encountered during the reclamation process is absorbed by the dedicated collector. Thus the user session threads continue to operate normally, making the reclaiming of cache memory blocks by the dedicated collector task thread transparent to the user session threads. In an alternative embodiment, the process for reclaiming page buffers is distributed amongst user processes sharing the shared memory. Each of the user processes includes a user thread collector for reclaiming a page buffer as needed and multiple user processes can concurrently reclaim page buffers.

System for maintaining a buffer pool

A method for aggregating sparse data in a multidimensional array by using a composite join hierarchy created by segmenting the data so that each segment of the hierarchy processed is smaller and more likely to fit in memory. The method employs a recursive sub-cubing mechanism wherein an n-dimensional cube is broken into a number of (n&minus;1)-dimensional cubes and each of those cubes are solved as (n&minus;2)-dimensional cubes etc. Within each division, the processing is segmented by hierarchy level so a dimension with three hierarchy levels (for example, month-quarter-year) would form three separate subcubes with one less dimension. This algorithm produces one 'worklist' for every combination of hierarchy levels in the cube. Each of these worklists is represented as a bitmap of the cells contained within it and may be used as a basis of generating more aggregate worklists. To minimize the need for input-output data transfers, all the derived worklists of a single worklist are generated at the same time. This is accomplished without keeping more than n-worklists active at any given time to reduce the number of input-output data transfers needed without requiring substantially larger memory space.

Methods and apparatus for aggregating sparse data

Growth of databases in read-repeatable environments is controlled by an erase list maintained for each generation of the database. The erase list identifies pages which are retained in the database to facilitate read repeatability. The system uses the erase list to identify obsolete pages which can be removed from the database. The removed pages can then be reclaimed for re-use by a subsequent generation.

System for controlling database growth in a read-repeatable environment

A method for translating, in a software paging system, an input key describing a virtual page to the address of the page in main memory, comprises creating, in main memory, a translation buffer which has a plurality of records. Each record has a plurality of cells, each cell having a key field for storing a key or a portion of a key which identifies a page in memory, and each cell having an address field for storing the address of the identified page. If the input key matches a stored key, the address associated with the identified page is retrieved. Otherwise, a paging manager is invoked to establish an address for the input key, and the input key and established address are saved. The least recently used order of memory pages addressed in the dereferenced record is indicated by updating a least recently used cell indicator associated with the dereferenced record. Alternatively, a table having a plurality of entries is created, wherein each entry references a respective hash chain of translation records in a main memory translation buffer. Each translation record has a key field for storing a key identifying a page, and an associated address field for storing the address of the identified page in memory. The records of the hash chain are searched until a translation record is found which has a key value matching the input key.

Growing community of inventors

Arlington, MA, United States of America

Albert A Hopeman