Abstract: One embodiment includes dimensional columns of a database table that are mapped to filter columns. The filter columns are used to build an index which provides multi-dimensional access to the database table.

Abstract: There is provided a computer-implemented method of optimizing a query. An exemplary method comprises determining a maximum cardinality for an operator. The query may comprise the operator. The exemplary method also comprises determining a plurality of potential implementations for the operator. The exemplary method additionally comprises generating a query plan for the query based on the maximum cardinality and the potential implementations.

Abstract: A system and method for join order optimization in a query optimizer is disclosed. The method includes receiving a query having a plurality of join operators, including at least one multi-way join between relational operators in the query tree. The join operators include at least one outer-join and/or semi-join. The multi-way-join is transformed to a multi-join operator with a plurality of join back bone children representing the relational operators. The dependencies that occur between the join back bone children are tracked. Join order validity is evaluated based on the tracked dependencies. One or more multi-join rules are applied to the multi-join operator sufficient to generate at least one join subtree when at least one join subtree is determined to have a valid join order.

Abstract: There is provided a computer-implemented method of optimizing a query. An exemplary method comprises receiving a first request from an optimizer that specifies a table, a first predicate and a first sample size, wherein the optimizer is optimizing a relational query language statement that specifies the table and the first predicate. The exemplary method also comprises generating a sample table, comprising a first subset of rows from the table, based on the request. The exemplary method also comprises selecting a second subset of rows from the sample table based on the predicate. The exemplary method additionally comprises sending a count of rows in the second subset to the optimizer.

Abstract: Embodiments of the present invention partition optimized query plans into equivalence groups, each comprising one or more equivalence classes. Each equivalence group corresponds to a particular compiled, normalized, and parameterized query plan prior to optimization. Each equivalence class within an equivalence group corresponds to a different query plan corresponding to the particular compiled, normalized, and parameterized query plan represented by the equivalence group that has been optimized with respect to the selectivity of one or more predicate clauses of the query that is compiled to produce the particular compiled, normalized, and parameterized query plan. Optimized query plans are cached according to their respective equivalence groups and equivalence classes.

Abstract: Embodiments of the present invention are directed to query-optimization methods and systems that employ risk premiums assigned to database operations in order to increase the robustness and reliability of the evaluation and selection of query-execution plans for executing high-level database queries. Using risk-premium estimates, selection of query-execution plans that include potentially inefficient database operations and inefficient database-operation-execution sequences, often selected by currently available query-optimization methods and systems, are avoided.

Abstract: In one embodiment, a computer system comprises one or more processors, and a memory module communicatively connected to the one or more processors. The memory module comprises logic instructions stored on a computer readable medium which, when executed on the one or more processors configure the one or more processors to receive, in a hybrid caching module, database query, determine whether at least a portion of an existing execution plan stored in a memory module coupled to the hybrid caching module can be reused to implement the database query, reuse at least a portion of an existing execution plan stored in the memory module coupled to the hybrid caching module when possible, and generate a new execution plan when the memory module lacks an existing execution plan adaptable for use with the database query.

Abstract: One embodiment is a method that determines skew to predict costs for query plans for a database query. The method selects a query plan that accounts for the skew to evenly distribute processing load among multiple processors and then executes the query with the selected query plan.

Abstract: An enterprise data warehouse or business intelligence system having a plurality of processors and memory resources. The system includes at least a database server, a workload management system, a query compiler, and a query executor. The database server configured to receive a database query from an application at a database client system. The query compiler configured to prepare the execution plan for the query and compute the number of executive server processes (ESPs) in each ESP layer of the query. The workload management system is configured to generate an affinity value, and the query executor is configured to execute the query. As disclosed herein, placement of the executive server process layers of the query onto processors of the computing system is determined using the affinity value. Other embodiments, aspects and features are also disclosed.

Abstract: Methods and systems receive a normalized query tree and analyze the tree to collect information about join operators and their children, and tables in an associated query. This information is then made available to a rule based optimizer that is configured to produce, from the normalized query tree, an execution plan. In addition, in at least some embodiments, an extensible framework is provided for join order optimization via the use of a multi-join operator and multi-join rules as part of the general framework of a query optimizer.

Abstract: A method for creating a joined data set from a join input data set is disclosed. The method starts by categorizing the join input data set into a high-skew data set and a low-skew data set. The low-skew data set is distributed to the plurality of CPUs using a first distribution method. The high-skew data set is distributed to the plurality of CPUs using a second distribution method. The plurality of CPUs process the high-skew data set and the low-skew data set to create the joined data set.

Abstract: In one embodiment, a computer system comprises one or more processors, and a memory module communicatively connected to the one or more processors. The memory module comprises logic instructions stored on a computer readable medium which, when executed on the one or more processors configure the one or more processors to receive, in a hybrid caching module, database query, determine whether at least a portion of an existing execution plan stored in a memory module coupled to the hybrid caching module can be reused to implement the database query, reuse at least a portion of an existing execution plan stored in the memory module coupled to the hybrid caching module when possible, and generate a new execution plan when the memory module lacks an existing execution plan adaptable for use with the database query.

Abstract: The present invention reduces the compile time in a top-down rule based system by identifying the complexity of a query prior to applying a rule to an expression. If the complexity of the query is above a threshold, the present invention determines whether the rule should be applied based upon several factors including the type of rule and the position of the node in the search space. Those rules that need not be applied are randomly pruned at a determined rate that prevents search space explosion and prevents the elimination of large contiguous portions of the search space. Pruned rules are not applied, while those rules that are not pruned are applied.

Abstract: The present invention applies one or more pruning heuristics to the expression, the binding, and/or the substitute during a database query optimization process. The heuristics identify certain rules that can be eliminated by either not applying the rules and/or not implementing the rules for a given expression and context (if any) based upon one or more flow rates of the expression. The pruning heuristics can eliminate the application of rules based upon the flow rates of the binding or substitute, for example.