Oracle International Corporation

Ekrem Soylemez

Matthew Dombroski

Caleb Welton

Juan R Loaiza

Dieter Gawlick

Neerja Bhatt

James E Carey

Hasan Rizvi

Albert A Hopeman

Adam Kociubes

Albert A Hopeman, Iv

Benoit Dageville

Amit Ganesh

Andrew Witkowski

Robert J Jenkins, Jr

Martin Roth

Gregory Dorman

Bruce Golbus

Christopher Kearney

Lev Margulis

A A Hopeman

Zhiqi Qiu

Hyemin Chung

Rahim Yaseem

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

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. A result set for a query is determined using a combination of the first data structure, second data structure and/or the third data structure.

Use of dynamic dictionary encoding with an associated hash table to support many-to-many joins and aggregations

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

Predicate abduction involves identifying, from a database statement, predicates on attributes of dimensional data, and applying those predicates to corresponding measure data so that only measure values are fetched and returned for dimension values that satisfy the predicate. In contrast to processing joins of relational data, the subset of the measure data is determined without needing to join a fact table with a dimension table and, therefore, without processing every fact record to determine which measure values are associated with records that satisfy the condition. Buffer reuse involves storing in buffers the 'domains' of queries executing on multidimensional data. Responsive to detecting that execution of a database statement requires multiple iterations of extracting the same multidimensional data, the data is extracted during the first iteration and stored in a buffer so that, for each subsequent iteration, the buffer-stored data is accessed rather than re-extracting the data.

Efficient processing of relational joins of multidimensional data

In processing a query on multidimensional data in a multidimensional schema, a multidimensional database server performs various processes to limit the amount of data that is extracted from the database and presented to a relational database server in response to the request. A subset of data is identified to extract from the multidimensional data, based on the query. In various embodiments, cell-filtering, measure-filtering, and column-filtering criteria identified from the query are used to filter the subset of data, in order to return a minimized set of multidimensional data values from the subset of data. Therefore, computational resources are conserved with respect to further processing by the relational database server in response to the query.

Efficient SQL access to multidimensional data

Approaches are disclosed for using a multidimensional database as a surrogate for a run-time hash table in the context of processing a relational query. In an embodiment, an analytic workspace is used to store data, wherein the data is to be evaluated using a by a function in the processing of a MODEL clause. The approaches eliminate the need to use a hash table to store the data that is to be evaluated. The data may initially reside in the analytic workspace, or it may be loaded into the analytic workspace.

Using multidimensional access as surrogate for run-time hash table

In one approach disclosed herein, a cache is used to provide cursor isolation. The cache is used to cache data that is updated in a query. Specifically, when a query is initiated, one or more caches may be instantiated in respect of the cursor. When data is updated during the query, the updated data is stored in the cache. By storing the updated data in the cache, the updated data is isolated from other users, from other sessions and from other queries by the same user.

Using a cache to provide cursor isolation

Techniques for executing an atomic transaction include performing a first operation of a first set of operations. The atomic transaction involves the first set of operations for causing changes to a first set of data. A first error that prevents completion of the atomic transaction is detected. In response to detecting the first error, a second set of operations is performed for resolving the first error. It is then determined whether a resolution of the first error is obtained in response to performing the second set of operations. If the resolution is obtained, then execution of the first set of operations is resumed. These techniques allow atomic transactions to be resumed after resolving an error without undoing all the operations accomplished for the transaction at the time the error occurred.

Techniques for resuming a transaction after an error

An analytical workspace includes a set of data on which what-if analysis is performed, which is stored persistently and managed in a relational database system. The data may be stored as a binary large object in relational tables, and may comprise a portion of a multidimensional cube. A series of commands on the analytical workspace is received by a database server. In response to the commands, changes are made to the data in temporary storage. Redo information is not generated at this time in association with such changes. Hence, these changes are not made permanent at this time. At some point, the server may receive an update command to make the changes permanent. In response to the update command, redo information is generated for the changes reflected in the temporary storage and the changes are made permanent, in accordance with relational database properties.

Techniques for managing what-if analysis of data managed by a relational database system

A method and apparatus are provided for implementing a content-based publish-and-subscribe system in a relational database system. A subscriber may request for messages by using a query language supported by the database server. The queries are then used as subscription rules for determining whether an incoming message is to be sent to the subscriber. Evaluation of incoming messages against subscription rules involves a two-tier process. The first tier involves filtering out all the subscription rules that could not possibly apply to the incoming message. The second tier involves further evaluation of the incoming message, if needed, to determine which subscribers are to receive the incoming message. In another aspect of the embodiment, in cases where the publisher is the relational database system itself, queries that request for messages are not stored as subscription rules because publication of the messages is implicit. In this situation, a query is received from a client that is written in a query language supported by the database system. The query is a request for data stored in the database system that satisfies the query. A detection is made that the data stored in the database system has been changed and in response, the changed data is provided to the client.

Approach for publishing data in a relational database system

A method and system for recovering after failure of a first server to which a client was connected for accessing a resource is provided. When it is detected that a connection between the first server and the client has failed, the client is automatically connected with a second server that has access to the resource. After automatically connecting the client, the client accesses the resource through the second server. The client stores information about the state of the session with the first server so that processing can continue where it left off after the client connects with the second server. The client may be pre-connected to the second server prior to the failure of the first server to reduce the latency caused by switching in response to a failure. The second server may be configured to pre-parse the commands that the client issues to the first server to further reduce the latency associated with switching to the second server.

Automatic failover for clients accessing a resource through a server

A method and apparatus are provided for implementing a content based publish-and-subscribe system in a relational database system. A subscriber may request for messages by using a query language supported by the database server. The queries are then used as subscription rules for determining whether an incoming message is to be sent to the subscriber. Evaluation of incoming messages against subscription rules involves a two-tier process. The first tier involves filtering out all the subscription rules that could not possibly apply to the incoming message. The second tier involves further evaluation of the incoming message, if needed, to determine which subscribers are to receive the incoming message. In another aspect of the embodiment, in cases where the publisher is the relational database system itself, queries that request for messages are not stored as subscription rules because publication of the messages is implicit.

Content based publish-and-subscribe system integrated in a relational database system

A method and apparatus are provided for passing a client from a first server to which the client was connected for accessing a resource, to a second server for accessing the resource. While executing, the first server ceases to respond to the client. After the client detects that the first server has ceased to respond to the client, the client is automatically connected with the second server that has access to the resource. After automatically connecting the client, the client accesses the resource through the second server. The client stores information about the state of the session with the first server so that processing can continue where it left off after the client connects with the second server. The client may be pre-connected to the second server prior to the failure of the first server to reduce the latency caused by switching in response to a failure. The second server may be configured to pre-parse the commands that the client issues to the first server to further reduce the latency associated with switching to the second server.

Growing community of inventors

Arlington, MA, United States of America

Ekrem Soylemez