Abstract: A method for estimating the selectivity of a database base table predicate, the cardinality of a join, and the cardinality of an aggregation. The method includes receiving a database query, the query comprising one or more query predicates and referencing one or more database tables. One or more join indexes are identified, the join index(es) defined on respective database tables referenced by the database query. The join index(es) comprises one or more join index predicates, and includes one or more join columns in its select list. The row count selected by the query predicates is calculated at least partly using the row count or statistics of the one or more join indexes. The selectivity of the base table predicate is calculated at least partly from the calculated row count. The cardinality of the join is estimated at least partly from the row count and statistics of the identified join index(es).

Abstract: A database system includes database software to receive a query specifying a join of plural base tables, and to determine whether a foreign key and primary key relationship exists between join columns specified by the query and a materialized view definition. Based on the existence of the foreign key and the primary key relationship between join columns specified by the query and the materialized view definition, the database software determines that a result for the received query is to be computed from the materialized view.

Abstract: A system and method include defining a snapshot join index using a sparse condition in a join index definition. A new sparse condition of the snapshot join index is compared with an old sparse condition. Rows in a base table are identified as a function of the comparing, and the join index table is updated using the identified rows.

Abstract: A SQL query that includes an IN-List is optimized by utilizing an array insert to insert the IN-List into a spool and piggybacking IN-List rows into a join step.

Abstract: A system and method of rewriting a database query where the query contains an aggregate includes the following. If one or more aggregate materialized views are considered, rewriting the query using an aggregate materialized view. If one or more non-aggregate multi-table materialized views are considered, the query is rewritten using a multi-table materialized view. A join cost is determined for each such non-aggregate multi-table materialized view. If one or more non-aggregate single table materialized views are considered, the query is rewritten using the single table materialized view. A join cost is determined for each such non-aggregate single table materialized view. Finally, a current total cost is determined for use of various materialized views as a function of join cost, aggregation cost and spool cost to select a rewritten query.

Abstract: A system and method include obtaining a query and identifying an aggregate join index (AJI) at a high level of aggregation. The dimension table may be rolled-up with the grouping key being the union of the grouping key in the AJI and the grouping key of the query. The identified AJI is joined with the rolled-up dimension table to obtain columns in the query that are not in the identified AJI. The joined AJI and rolled-up dimension table are then rolled up to answer the query.

Abstract: A method for estimating the selectivity of a database base table predicate, the cardinality of a join, and the cardinality of an aggregation. The method includes receiving a database query, the query comprising one or more query predicates and referencing one or more database tables. One or more join indexes are identified, the join index(es) defined on respective database tables referenced by the database query. The join index(es) comprises one or more join index predicates, and includes one or more join columns in its select list. The row count selected by the query predicates is calculated at least partly using the row count or statistics of the one or more join indexes. The selectivity of the base table predicate is calculated at least partly from the calculated row count. The cardinality of the join is estimated at least partly from the row count and statistics of the identified join index(es).

Abstract: A SQL query that includes an IN-List is optimized by (1) performing an evaluation to determine whether access to a table can be performed as a join operation, (2) converting the IN-List to an IN-LIST relation, and (3) joining the IN-List relation with the table to access the data in the table.

Abstract: The invention relates to a method of determining the performance of an optimizer program arranged to generate a plan. The method generally comprises the steps of executing the optimizer utilising at least one test case to derive a pre-variation plan; executing the pre-variation plan to generate a first output; varying a strategy of the optimizer program; executing the varied optimizer program utilising the at least one test case to derive a post-variation plan; and executing the post-variation plan to generate a second output. The outputs are then grouped according to at least one element of the plans to assess the performance of the optimizer.

Abstract: A method for determining the cost of evaluating queries against a database. The method includes receiving a condition, or query; determining the cost of one or more fixed cost operators within the condition; and determining the cost of one or more variable coefficients within the condition. The method may also be extended to determine the cost of successive conditions within a set of conditions as a function of the cost of a current condition and the combined selectivity of previous conditions within the set of conditions; and to determine an optimal order of conditions within a set of conditions.

Abstract: A database query is optimized for a star database schema that includes at least one fact table and at least one dimension table, where the database query includes an IN-List condition. During optimization, the IN-List is transformed into a relation and the relation is joined with the dimension table to produce a join result. The join result is then joined to the fact table.

Abstract: A system and method include obtaining a query and identifying an aggregate join index (AJI) at a high level of aggregation. The dimension table may be rolled-up with the grouping key being the union of the grouping key in the AJI and the grouping key of the query. The identified AJI is joined with the rolled-up dimension table to obtain columns in the query that are not in the identified AJI. The joined AJI and rolled-up dimension table are then rolled up to answer the query.

Abstract: A system and method include defining a snapshot join index using a sparse condition in a join index definition. A new sparse condition of the snapshot join index is compared with an old sparse condition. Rows in a base table are identified as a function of the comparing, and the join index table is updated using the identified rows.

Abstract: A computer implemented system and method includes obtaining a query referring to rows in a relational database. A sparse index of the database that has a set of rows that is a subset of the rows referred to in the query is obtained. Rows referred to in the query that are not in the sparse index are then obtained and a union of such rows and the rows of the sparse index is performed to obtain a complete row set for processing the query.

Abstract: A system and method of rewriting a database query where the query contains an aggregate includes the following. If one or more aggregate materialized views are considered, rewriting the query using an aggregate materialized view. If one or more non-aggregate multi-table materialized views are considered, the query is rewritten using a multi-table materialized view. A join cost is determined for each such non-aggregate multi-table materialized view. If one or more non-aggregate single table materialized views are considered, the query is rewritten using the single table materialized view. A join cost is determined for each such non-aggregate single table materialized view. Finally, a current total cost is determined for use of various materialized views as a function of join cost, aggregation cost and spool cost to select a rewritten query.

Abstract: The invention relates to a method of determining the performance of an optimizer program arranged to generate a plan. The method generally comprises the steps of executing the optimizer utilising at least one test case to derive a pre-variation plan; executing the pre-variation plan to generate a first output; varying a strategy of the optimizer program; executing the varied optimizer program utilising the at least one test case to derive a post-variation plan; and executing the post-variation plan to generate a second output. The outputs are then grouped according to at least one element of the plans to assess the performance of the optimizer.

Abstract: An optimization technique involves optimizing a SQL query, in which the SQL query includes a large IN-List query to access data in a table. The technique includes determining whether an IN-List exceeds a predetermined size, and, if so, removing the IN-List query statement and inserting a new query statement including a join operation.

Abstract: A database query is optimized for a star database schema that includes at least one fact table and at least one dimension table, where the database query includes an IN-List condition. During optimization, the IN-List is transformed into a relation and the relation is joined with the dimension table to produce a join result. The join result is then joined to the fact table.

Abstract: A method, database system and computer program are disclosed for optimizing a SQL query, in which the SQL query includes an IN-List. An evaluation is performed to determine whether the IN-List query can be performed as a join operation. If so, a new access path that converting IN-List to a relation, and subsequently joined with the table to complete the IN-List query is cost and competes with other access paths.

Abstract: A method, database system and computer program are disclosed for optimizing a SQL query, in which the SQL query includes an IN-List. The method includes utilizing an array insert to insert the IN-List into a spool and piggybacking IN-List rows into a join step.