Abstract: A histogram-augmented dynamic sampling approach is provided for determining cardinality of a two-table join. The approach has a pre-processing phase in which data structures are created that will be used during a compilation phase for cardinality estimation. These data structures include a row histogram and a key histogram, which are created for selected columns of a first table. A cardinality estimation phase uses the data structures to estimate the cardinality of various joins at the time of query compilation. In this phase, the system executes queries that join the histograms with a second table, to perform the cardinality estimation.

Abstract: A new type of table join operation, outer semi join (OSJ), is provided, which can be used by an optimizer layer and an execution layer of a database management system (DBMS). OSJ combines the semantics of both left outer-join and semi-join. The concept of an anti-join marker (AJM) is also introduced, which specifies whether a matching row was not found between joined tables for each result row in an OSJ operation. The OSJ operation supports unnesting of a class of disjunctive ANY, ALL, EXISTS, NOT EXISTS, IN, and NOT IN subqueries for execution plan optimization. The disjunction may contain filter predicates. For unnesting, OSJ avoids the need of using a distinct operator on the right table and also supports using inequality (e.g. >, >=, <, <=) in connecting or correlating conditions of subqueries, rather than being limited to equality only.

Abstract: Disclosed is an approach for applying fine-grained hints to obtain optimal control over error handling during query compilation. Using fine-grained hints causes less disruption to the query plans generated by the optimizer as they constrain specific features while retaining scope for the rest of the optimizer functionality. This approach provides a query plan of better quality that avoid errors, particularly in comparison to coarse-grained approaches that restrict processing to earlier database versions or which disables cost-based processing.

Abstract: A new type of table join operation, outer semi join (OSJ), is provided, which can be used by an optimizer layer and an execution layer of a database management system (DBMS). OSJ combines the semantics of both left outer-join and semi-join. The concept of an anti-join marker (AJM) is also introduced, which specifies whether a matching row was not found between joined tables for each result row in an OSJ operation. The OSJ operation supports unnesting of a class of disjunctive ANY, ALL, EXISTS, NOT EXISTS, IN, and NOT IN subqueries for execution plan optimization. The disjunction may contain filter predicates. For unnesting, OSJ avoids the need of using a distinct operator on the right table and also supports using inequality (e.g. >, >=, <, <=) in connecting or correlating conditions of subqueries, rather than being limited to equality only.

Abstract: Techniques for automatic error mitigation in database systems using alternate plans are provided. After receiving a database statement, an error is detected as a result of compiling the database statement. In response to detecting the error, one or more alternate plans that were used to process the database statement or another database statement that is similar to the database statement are identified. A particular alternate plan of the one or more alternate plans is selected. A result of the database statement is generated based on processing the particular alternate plan.

Abstract: Techniques for automatically preventing execution plan regressions are provided. In one technique, in a first user database session, in response to receiving a first database statement, a first execution plan is generated and, while executing the first execution plan, first performance data that indicates one or more first performance metrics of executing the first execution plan is recorded. In response to receiving a second database statement, where the first execution plan may be used to generate a result for the second database statement, a second execution plan is generated and second performance data that indicates one or more second performance metrics of executing the second execution plan is recorded. A comparison between the first performance data and the second performance data is performed. Based on the comparison, it is determined whether the second execution plan will be stored for future use to process a database statement.

Abstract: Techniques for automatically preventing execution plan regressions are provided. In one technique, in a first user database session, in response to receiving a first database statement, a first execution plan is generated and, while executing the first execution plan, first performance data that indicates one or more first performance metrics of executing the first execution plan is recorded. In response to receiving a second database statement, where the first execution plan may be used to generate a result for the second database statement, a second execution plan is generated and second performance data that indicates one or more second performance metrics of executing the second execution plan is recorded. A comparison between the first performance data and the second performance data is performed. Based on the comparison, it is determined whether the second execution plan will be stored for future use to process a database statement.

Abstract: Embodiments generate random walks through a directed graph that is represented in a relational database table. Each row of the graph table represents a directed edge in the graph and includes a source vertex and a destination vertex. Each row is further augmented to (a) indicate the number of outbound edges starting from the destination vertex in the row and (b) include an identifier that distinguishes the edge from other outbound edges starting from the same source vertex. An SQL query may be executed on the augmented graph table. Starting from a source vertex (starting vertex or the destination vertex of the previously selected hop) the query randomly selects a row of the graph table representing one of the outbound edges from the source vertex and adds the selected outbound edge as a row in a random walk table that represents the next hop in the random walk.

Abstract: Disclosed is an approach for applying fine-grained hints to obtain optimal control over error handling during query compilation. Using fine-grained hints causes less disruption to the query plans generated by the optimizer as they constrain specific features while retaining scope for the rest of the optimizer functionality. This approach provides a query plan of better quality that avoid errors, particularly in comparison to coarse-grained approaches that restrict processing to earlier database versions or which disables cost-based processing.

Abstract: Techniques for subsumption of inline views and subqueries in a query are described. An optimization technique of subsumption is enabled by inline views having identical tables and identical join conditions and having aggregation functions but no group-by clauses. When subsumption takes place, a single query block replaces the inline views (or subqueries) with a single inline view query block. Subsumption reduces multiple access to the same table and multiple evaluations of the same join conditions required to evaluate the query. The single query block includes factored out filter predicates and unified predicates that originate from the subsumed inline views (or subqueries). Based on similarities among the aggregation functions and filter predicates in the subsumed inline views, pre-computation of common aggregates may be performed in a new group-by view in the subsuming view.

Abstract: Techniques for the automatic creation and maintenance of zone maps are provided. In one technique, a set of data sets is identified. For each data set, a data set width is determined based on a maximum value in the data set and a minimum value in the data set. One or more zones within the data set are identified. For each zone, a zone width is determined based on a difference between a maximum value in that zone and a minimum value in that zone. An aggregate zone width is generated that is based on the zone width of each zone. Based on the data set width and the aggregate zone width, it is determined whether to automatically generate a zone map for the data set.

Abstract: Techniques are provided for bitmap-based computation of a COUNT(DISTINCT) function, where the bitmaps are generated based on ranks of target expression values. According to an embodiment, the ranks are computed using the DENSE_RANK function. The bitmaps may be maintained in a materialized view. Bitmap data that represents the ranks for target expression values occurring in data for a given group is divided across multiple bucket bitmaps, each corresponding to a distinct sub-range of the ranks. According to an embodiment, target expression value ranks are computed relative to partitions of the target expression values. When these partitions correspond to a subset (not necessarily strict) of the target query grouping keys for a query rewrite, the resulting bitmaps allow computation of multiple levels of aggregation from the single set of bitmaps.

Abstract: Embodiments generate random walks through a directed graph that is represented in a relational database table. Each row of the graph table represents a directed edge in the graph and includes a source vertex and a destination vertex. Each row is further augmented to (a) indicate the number of outbound edges starting from the destination vertex in the row and (b) include an identifier that distinguishes the edge from other outbound edges starting from the same source vertex. An SQL query may be executed on the augmented graph table. Starting from a source vertex (starting vertex or the destination vertex of the previously selected hop) the query randomly selects a row of the graph table representing one of the outbound edges from the source vertex and adds the selected outbound edge as a row in a random walk table that represents the next hop in the random walk.

Abstract: Techniques are described for storing and maintaining, in a materialized view, bitmap data that represents a bitmap of each possible distinct value of an expression and rewriting a query for a count of distinct values of the expression using the materialized view. The materialized view contains bitmap data that represents a bitmap of each possible distinct value of a first expression, and aggregate values of additional expressions, and is stored in memory or on disk by a database system. The database system receives a query that requests a number of distinct values, of the first expression, and an aggregate value for an additional expression. In response, the database system, rewrites the query to: compute the number of distinct values by counting the bits in the bitmap data of the materialized view that are set to the first value, and obtains the aggregate value for the additional expression in the materialized view.

Abstract: Techniques are provided for using of zone maps to improve the performance of a much wider range of queries than those for which zone maps are currently used. Specifically, techniques are provided for using zone maps to improve performance of queries by providing aggregate values for a wide range of aggregate operations, including SUM, AVG, etc., providing aggregate values for aggregate queries that specify filter conditions, distinguishing between situations in which the aggregate values for a zone are invalid for pruning purposes and when the aggregate values are invalid for query-answering purposes, determining when aggregate values may be used in multi-table zone maps where the type of join specified by a query differs from the type of join used to generate the aggregate values in the zone map, and selecting among different aggregate values for the same zone based on the type of join specified in a query.

Abstract: Embodiments generate random walks through a directed graph that is represented in a relational database table. Each row of the graph table represents a directed edge in the graph and includes a source vertex and a destination vertex. Each row is further augmented to (a) indicate the number of outbound edges starting from the destination vertex in the row and (b) include an identifier that distinguishes the edge from other outbound edges starting from the same source vertex. An SQL query may be executed on the augmented graph table. Starting from a source vertex (starting vertex or the destination vertex of the previously selected hop) the query randomly selects a row of the graph table representing one of the outbound edges from the source vertex and adds the selected outbound edge as a row in a random walk table that represents the next hop in the random walk.

Abstract: Techniques for the automatic creation and maintenance of zone maps are provided. In one technique, a set of data sets is identified. For each data set, a data set width is determined based on a maximum value in the data set and a minimum value in the data set. One or more zones within the data set are identified. For each zone, a zone width is determined based on a difference between a maximum value in that zone and a minimum value in that zone. An aggregate zone width is generated that is based on the zone width of each zone. Based on the data set width and the aggregate zone width, it is determined whether to automatically generate a zone map for the data set.

Abstract: Techniques are provided for bitmap-based computation of a COUNT(DISTINCT) function, where the bitmaps are generated based on ranks of target expression values. According to an embodiment, the ranks are computed using the DENSE_RANK function. The bitmaps may be maintained in a materialized view. Bitmap data that represents the ranks for target expression values occurring in data for a given group is divided across multiple bucket bitmaps, each corresponding to a distinct sub-range of the ranks. According to an embodiment, target expression value ranks are computed relative to partitions of the target expression values. When these partitions correspond to a subset (not necessarily strict) of the target query grouping keys for a query rewrite, the resulting bitmaps allow computation of multiple levels of aggregation from the single set of bitmaps.

Abstract: Techniques are described for storing and maintaining, in a materialized view, bitmap data that represents a bitmap of each possible distinct value of an expression and rewriting a query for a count of distinct values of the expression using the materialized view. The materialized view contains bitmap data that represents a bitmap of each possible distinct value of a first expression, and aggregate values of additional expressions, and is stored in memory or on disk by a database system. The database system receives a query that requests a number of distinct values, of the first expression, and an aggregate value for an additional expression. In response, the database system, rewrites the query to: compute the number of distinct values by counting the bits in the bitmap data of the materialized view that are set to the first value, and obtains the aggregate value for the additional expression in the materialized view.

Abstract: Embodiments generate random walks through a directed graph that is represented in a relational database table. Each row of the graph table represents a directed edge in the graph and includes a source vertex and a destination vertex. Each row is further augmented to (a) indicate the number of outbound edges starting from the destination vertex in the row and (b) include an identifier that distinguishes the edge from other outbound edges starting from the same source vertex. An SQL query may be executed on the augmented graph table. Starting from a source vertex (starting vertex or the destination vertex of the previously selected hop) the query randomly selects a row of the graph table representing one of the outbound edges from the source vertex and adds the selected outbound edge as a row in a random walk table that represents the next hop in the random walk.