Oracle International Corporation

Srikanth Bondalapati

Sankar Subramanian

Andrew Witkowski

Rafi Ahmed

Angela Amor

Techniques are described herein for processing queries comprising joins specifying a plurality of tables. The techniques involve partitioning the tables by assigning rows to partitions. One or more partition maps may be generated to indicate the partitions. Subsequent tables may be partitioned based on the generated partition maps. The partitions may be used to determine results for the joins.

N-way hash join

Attributes and semantics of duplicate insignificance that are inherent or inferred in a database language statement are detected. Also, a join operation that is inherent or inferred in the database language statement is detected and examined for join semantics. The join semantics specifies or refers to a driving table to be subjected to a hash join operation that may populate one or more hash buckets. The optimizer and the execution layers may use cost estimation or heuristics to assign the left and right table roles to the tables involved in the join. The hash join operation removes left table duplicates during population of the hash buckets, resulting in full or partial duplicate elimination that occurs during the hash join operation.

Duplicate reduction or elimination with hash join operations

Techniques for query optimization for group-by extensions and distinct aggregate functions are provided. A query has an extended group-by clause with an extended group-by operator and a first set of group-by columns. The query has one or more distinct aggregate functions and one or more non-distinct aggregate functions. An initial subquery is constructed that generates a partially aggregated initial temporary (PAIT) table when executed. The initial subquery includes a GROUP BY clause with a second set of group-by columns that includes the first set of group-by columns of the extended group-by clause of the query and one or more columns specified by the one or more distinct aggregate functions. One or more subqueries are computed that compute groupings indicated by the extended group-by operator based on the PAIT table generated by the initial subquery.

Growing community of inventors

Belmont, CA, United States of America

Srikanth Bondalapati