Abstract: Selecting a join plan for a query containing a join and a union block includes determining whether to propose a join plan with the join pushed across the union block. A selection is made between a join plan in which the join is not pushed across the union block and any proposed join plan in which the join is pushed across the union block.

Abstract: An execution plan for executing one or more iterations of recursive database queries can be generated in accordance with one aspect of the invention. For example, in accordance with one embodiment of the invention, one or more cardinalities (results or size of results, including intermediate results) associated with one or more iterations can be estimated. This estimation can, for example, by performed by using a sampling or sampling-based algorithm. In addition, a plan generated for execution of one or more iteration of a recursive query can be modified or replaced by comparing the actual cardinality with its estimation. As a result, the first one or more iterations may be executed using a first plan but the subsequent one or more iterations may use a modified or different execution plan.

Abstract: The number of iterations or self joins required to execute a recursive database query can be estimated. It will be appreciated that this estimation can be used to plan the execution of the recursive query and can be made in various ways and for various applications. By way of example, an estimated number of iterations or self joins required to execute a recursive database query (e.g., 12) can be used as a basis to determine or plan an optimal execution plan. For example, given an estimated twelve (12) iterations, an execution plan can be determined for executing at least the first three (3) iterations or for executing every three (3) iterations, whereas for an estimated twenty (21) iterations required to complete a recursive database query, an execution plan can be determined for the first five (5) or six (6) iterations, and so on.

Abstract: Based on one or more predicated results (e.g., estimations of the actual results) associated with one or more iterations and/or sub-queries of an iterative or recursive database query, it can be determined whether to use an execution plan (e.g., a current execution plan used to execute one or more iterations) to execute one or more other iterations and/or sub-queries of the iterative or recursive database query. Consequently, use of the execution plan to execute the one or more other iterations and/or sub-queries of the iterative or recursive database query can be disallowed. As a result, the iterative or recursive database query can be executed more efficiently. The determination of whether to use an execution plan can, for example, be performed by determining whether an actual result obtained by executing one or more iterations differs or diverges more than a determined value from an estimated result of the same iteration(s).

Abstract: A query to perform an ordered analytic function contains a clause specifying creation of dynamic partitions, where the clause contains a partitioning condition to specify the dynamic partitions. During processing of the query, dynamic partitions are identified based on evaluating the partitioning condition. To identify the dynamic partitions, a first table is created that contains a first column to track rows of an input table for indicating whether the partitioning condition evaluates to true. The first column of the first table is used to identify the dynamic partitions.

Abstract: Techniques for optimizing outer joins in database operations are provided. In an embodiment, a query having an outer join with a GROUP BY clause is rewritten and expanded to expand a first level partition of that GROUP BY clause to produce a modified query. In another situation, rows associated with processing a query are each row split and then hashed based at least in part on attributes of the outer join predicate. A left outer join is performed and a global aggregation processed to produce a spooled table to process the query.

Abstract: Inputs are received. The inputs are a star schema data model comprising a non-partitioned fact table (F) comprising fields (f1, f2, . . . fn); a set of queries (Q=q1, q2, . . . qm); and a set of weights for each query in Q. Each weight defines the priority of a respective query in a workload. The inputs are pre-processed to produce a most-granular-partitioning. The most-granular-partitioning is processed to produce an initial multi-level partitioned primary index, the processing including determining the scan cost of one of the queries in Q. The initial multi-level partitioned primary index is processed to produce a final multi-level partitioned primary index, the processing including determining the query cost of one of the queries in Q. F is partitioned using the final multi-level partitioned primary index. A query from Q is run against F to produce a result. The result is stored.

Abstract: The number of iterations or self joins required to execute a recursive database query can be estimated. It will be appreciated that this estimation can be used to plan the execution of the recursive query and can be made in various ways and for various applications. By way of example, an estimated number of iterations or self joins required to execute a recursive database query (e.g., 12) can be used as a basis to determine or plan an optimal execution plan. For example, given an estimated twelve (12) iterations, an execution plan can be determined for executing at least the first three (3) iterations or for executing every there (3) iterations, whereas for an estimated twenty (21) iterations required to complete a recursive database query, an execution plan can be determined for the first five (5) or six (6) iterations, and so on.

Abstract: Based on one or more predicated results (e.g., estimations of the actual results) associated with one or more iterations and/or sub-queries of an iterative or recursive database query, it can be determined whether to use an execution plan (e.g., a current execution plan used to execute one or more iterations) to execute one or more other iterations and/or sub-queries of the iterative or recursive database query. Consequently, use of the execution plan to execute the one or more other iterations and/or sub-queries of the iterative or recursive database query can be disallowed. As a result, the iterative or recursive database query can be executed more efficiently. The determination of whether to use an execution plan can, for example, be performed by determining whether an actual result obtained by executing one or more iterations differs or diverges more than a determined value from an estimated result of the same iteration(s).

Abstract: Techniques for optimizing outer joins in database operations are provided. In an embodiment, a query having an outer join with a GROUP BY clause is rewritten and expanded to expand a first level partition of that GROUP BY clause to produce a modified query. In another situation, rows associated with processing a query are each row split and then hashed based at least in part on attributes of the outer join predicate. A left outer join is performed and a global aggregation processed to produce a spooled table to process the query.

Abstract: A method, computer program, and database system are disclosed for processing a database query that includes one or more expressions. The method includes resolving columns in one or more of the expressions. Expression optimization is performed on one or more of the expressions. Afterward, further query optimization is performed.

Abstract: A method, computer program, and database system are disclosed for executing database queries. The database includes a first table (T1) having a primary key (PK) column and a first correlated value column (CV1) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV2). One implementation of the method includes preparing a database query for execution based at least in part on application of a derived constraint rule (DCR) having the form, (PK=FK)?CV2+C1?CV1?CV2+C2, where C1 and C2 are constants and “?” means “implies,” to produce an execution plan. A frequency of errors due to changes in DCRs is taken into account when preparing the execution plan. The plan is then executed.

Abstract: Selecting a join plan for a query containing a join and a union block includes determining whether to propose a join plan with the join pushed across the union block. A selection is made between a join plan in which the join is not pushed across the union block and any proposed join plan in which the join is pushed across the union block.

Abstract: Methods, computer programs, and database systems for analyzing one or more queries are disclosed. Queries may include one or more conditions and one or more sub-queries, with each sub-queries introduced by connecting condition. The method determines the satisfiability of the query, including the satisfiability of the connecting conditions and conditions in the sub-queries. Queries may include one or more conditions of the form (X+Y OP C). The method determines the satisfiability of the query, including the satisfiability of the conditions of the form (X+Y OP C).

Abstract: A method, computer program, and database system are disclosed for executing database queries. The database includes a first table (T1) having a primary key (PK) column and a first correlated value column (CV1) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV2). One implementation of the method includes preparing a database query for execution based at least in part on application of a derived constraint rule (DCR) having the form, (PK=FK)?CV2+C1?CV1?CV2+C2, where C1 and C2 are constants and “?” means “implies,” to produce an execution plan. Abort steps that are conditioned on changes in the DCR are also included in the execution plan. The plan is then executed.

Abstract: A method, computer program, and computer network are disclosed for executing database queries. The method includes identifying a first set of conditions corresponding to a selected step for executing a query. A second set of conditions corresponding to one or more steps for executing the query that feed the selected step is identified. Each condition in the first set is checked for mathematical redundancy, including redundancy without equivalency, with regard to the other conditions in the union of the conditions corresponding to the selected step and the conditions in the second set. Each condition in the first set that is redundant is included in a third set. If there is only one condition in the third set, an identifier of the one condition is stored.

Abstract: A method, computer program and system for joining two tables, t1 and t2 are described. Each table contains rows and columns and is divided into one or more partitions. A correlation function is calculated between a first correlated value column of table t1 and a second correlated value column of table t2. Following a query request, a joining algorithm is used to join t1 and t2, using a reduced set of partitions based in part upon the correlation function.

Abstract: A method and computer system for optimizing queries to a database are disclosed. The database includes a first table (T1) having a primary key (PK) column and a first correlated value column (CV1) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV2). The method includes joining T1 to the T2 using PK=FK as the join condition to produce a join result having rows. Each row includes a value from CV1 and a value from CV2. The method further includes creating an initial running constraint (RC). The initial running constraint includes a null range.

Abstract: A method, database system and computer program are disclosed for optimizing a SQL query, in which the SQL query includes a WHERE clause and a FROM clause. An evaluation is done to determine whether a non-covering join index partially but not completely covers the query. If it does, the join index is added to the FROM clause of the query without removing the partially covered base tables and the WHERE clause of the query is modified by: (1) mapping a query condition to the join index for its partially covered base tables; and (2) adding a join back condition from the join index to a base table from which the join index was formed.

Abstract: The retrieval of data from a database in a computerized database management system is optimized through the use of a virtual join index and associated covering non-unique secondary indexes. The virtual join index is created by combining one or more commonly-used columns of one or more tables. The covering non-unique secondary indexes are generating by combining one or more commonly-used columns of the virtual join index. Data is not stored in the virtual join index after the covering non-unique secondary indexes are generated. Thereafter, some queries or portions thereof may be resolved solely by accessing one or more of the covering non-unique secondary indexes.