A method, database system, and computer program are disclosed for optimized costing. The method includes identifying a join that identifies a first table and a second table. The method further includes determining an optimized cost of reading the first table. If the number of unique first table values is greater than the number of unique second table values, the number of instances where a unique first table value matches a unique second table value is returned. Otherwise, the number of unique first table values is returned. The method further includes determining an optimized cost of reading the second table. The optimized cost of reading the second table includes the number of unique second table values. The method also includes summing the optimized cost of reading the first table and the optimized cost of reading the second table.
A method and computer program are disclosed for measuring the parallelism of an execution step. The method includes determining whether the step operates on rows whose primary index is specified. If the primary index is specified, the parallelism is measured as single. If the primary index is not specified, the method determines whether the step uses an output from a previous step. If an output is used, the number of rows in the output is compared to a specified number. If the number of rows is greater than the specified number, the parallelism is measured as total. In one implementation, the geography of the output is determined and the previous step is compared to specified types such that if the primary index is not specified, the number is rows is not greater than a specified number, the geography does not write output rows to all parallel units, and the previous step is of a specified type, then the parallelism is measured as partial.
A method and computer system for optimizing queries to a database are disclosed. The query specifies one or more data-to-be-aggregated columns (DAC), one or more explicit aggregation keys (EAK) and one or more join conditions. The query involves table T1. T1includes the one or more data-to-be-aggregated columns and an aggregation key. All join conditions that involve T1 join the aggregation key of T1 to the unique primary index of another table that is involved in the query. T1 is joined to a table T2 in accordance with a join condition of the aggregate join query. The joining process includes sorting T1 on a sort key that is the aggregation key. Aggregation processing is combined into the step of sorting if T1 is a significant table. Aggregation processing is also combined into the step of sorting if T2 is a significant table and the first join condition is between the aggregation key of T1 and the unique primary index of T2.
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.