Abstract: A method, system, and computer program product for managing database statistics are provided. The method, system, and computer program product provide for receiving a query for optimizing, collecting statistics specific to the query prior to generating any access plans for executing the query, and generating an access plan for executing the query based on the collected statistics.

Abstract: Disclosed is a method for directing a data processing system, the data processing system and an article of manufacture. The data processing system has memory for storing a database. The method directs the data processing system to configuring the database based on a database workload having a performance metric. The method includes selecting a plurality of database characteristics based upon the database workload, each of the database characteristics having an associated estimated performance metric for the database workload, selecting a set of database characteristics from the plurality of database characteristics for which the associated estimated performance metric is lower than the database workload performance metric, and configuring the database using the selected set of database characteristics.

Abstract: The retrieval of distinct tuples in a relational database management system. In response to a request from a consumer process for distinct tuples in a relational database table matching a defined criteria, a distinct operator component sequentially requests tuples from a source component. The source component access the database table and returns a tuple in the sequence to the distinct operator component. The distinct operator component passes each tuple in the sequence to an auxiliary logger. The auxiliary component receives a tuples from the distinct component and determines if it is distinct from other previously received tuples in the sequence to verify its uniqueness to the distinct operator. Tuples that are verified as unique by the auxiliary logger are returned to the consumer process by the distinct operator upon verification.

Abstract: Disclosed is a data processing system implemented method, a data processing system and an article of manufacture for executing a query having a union operator. The data processing system implemented method directs the data processing system to process a query against data objects. The data objects are operatively coupled to the data processing system. The query includes a parent operator. The parent operator references a union operator. The union operator references sub-queries. The sub-queries reference the data objects. The data processing system implemented method includes noting a set of partitionings for the union operator, the noted set of partitionings being based on the sub-queries and being based on the data objects reference by the sub-queries, and executing the query having the union operator, the execution of the query being based on the noted set of partitionings and the parent operator.

Abstract: An apparatus, system, and method are disclosed for performing fast approximate computation of statistics on query expressions in order to improve query optimization within a database management system by accurately and quickly estimating the sizes of intermediate query results. This is accomplished by analyzing a query for join instruction and identifying a fact table and a dimension table within those join instructions. Then, frequency statistics corresponding to distinct values of within the fact table are retrieved from a catalog table. Those frequency statistics are used in combination with a full scan of the dimension table accurately and quickly estimate frequency statistics for an expected join between the fact table and dimension table. The estimated frequency statistics corresponding to the expected join may then be used in such operations as query optimization.

Abstract: A query is matched to an outlier materialized query table that stores exception data. The query is searched for a source predicate. An outlier predicate in the outlier materialized query table that corresponds to the source predicate is searched for a target column that corresponds to a source column in the source predicate. A new range predicate is derived based on the target column and introduced into the query, wherein the query is executed to retrieve data from one or more data stores.

Abstract: Application of a scalar function across a varying number of records within an RDBMS using the RDBMS's SQL capabilities. This is accomplished by determining a last row in a group of rows, sorting the rows and passing the sorted rows to a scalar function, where the scalar function accepts each row and stores the accepted rows in a temporary file space. When the last row is encountered, the aggregate value is calculated using all of the rows in the group, and then a filter is applied on the rows so that only a row with the aggregate value is returned.

Abstract: The present invention provides a method of estimating cardinalities using statistical soft constraints in a relational database management system. The statistical soft constraints are generated from statistical information gathered with respect to virtual columns. The virtual columns are the result of operations involving one or more columns in the database. When a query is received, the database management system determines whether any of the virtual columns are of interest because they involve expressions or relations that are also reflected in the query. The database management system then develops statistical soft constraints from the statistical characteristics of the virtual column and uses those statistical soft constraints to assist in estimating the cardinality of a step in the query plan. The virtual columns may or may not be actually materialized into a column of values.

Abstract: A query rewrite optimization method eliminates condition predicates of conditional expressions defining the values of a column that is referenced in the predicate of an SQL query on a view. The method evaluates the query to identify a view and a predicate referencing a column of the view. The column has values selected by a conditional expression having condition predicates defining respective mutually exclusive ranges of values. The predicate defines a first range of values. A determination is made as to whether one of the condition predicates defines a mutually exclusive range of values that comprises the first range of values. In response, the query is rewritten to eliminate the condition predicates other than the one condition predicate. The predicate is thus applied directly to the base tables without evaluating the conditional expression.

Abstract: A method, apparatus, and article of manufacture for incrementally estimating the cardinality of a derived relation when statistically correlated predicates are applied. A plurality of query execution plans (QEPs) are generated for the query. During the generation of the QEPs, a cardinality is computed for any of the QEPs in which two or more predicates are correlated to each other. The cardinality comprises a number of rows expected to be returned by the QEP and is computed in an incremental fashion for each operator of the QEP. The computations include calculations that may be done prior to the generation of the QEPs and calculations that are necessarily done as each operator of a QEP is added to that QEP. Thereafter, one of the QEPs is chosen to satisfy the query in a manner that minimizes an estimated cost metric, wherein the cost metric is computed using the cardinality.

Abstract: A system and process for evaluating the performance of an operational database system without interfering with the normal operation of the operational database system is described. The process involves the creation of a test database which contains the system catalogs of the operational database system as user tables. The test database is used to compile queries using these simulated system catalogs. The access plan generated by the compilation in the test database can then be used to evaluate the performance of the queries.

Abstract: The present invention provides a method of estimating cardinalities using statistical soft constraints in a relational database management system. The statistical soft constraints are generated from statistical information gathered with respect to virtual columns. The virtual columns are the result of operations involving one or more columns in the database. When a query is received, the database management system determines whether any of the virtual columns are of interest because they involve expressions or relations that are also reflected in the query. The database management system then develops statistical soft constraints from the statistical characteristics of the virtual column and uses those statistical soft constraints to assist in estimating the cardinality of a step in the query plan. The virtual columns may or may not be actually materialized into a column of values.

Abstract: The retrieval of distinct tuples in a relational database management system. In response to a request from a consumer process for distinct tuples in a relational database table matching a defined criteria, a distinct operator component sequentially requests tuples from a source component. The source component access the database table and returns a tuple in the sequence to the distinct operator component. The distinct operator component passes each tuple in the sequence to an auxiliary logger. The auxiliary component receives a tuples from the distinct component and determines if it is distinct from other previously received tuples in the sequence to verify its uniqueness to the distinct operator. Tuples that are verified as unique by the auxiliary logger are returned to the consumer process by the distinct operator upon verification.

Abstract: The invention provides method and apparatus for use in a database management system for managing a database containing data, where the database has storage for storing data in the database, and has an instruction processor for processing instructions for managing data stored in the database. The database has a constraint manager for managing constraints on said data stored in the database. The invention provides efficient method and means for deferred checking of data after bulk loading into said database for violation of constraints by determining an appropriate procedure for constraint checking by determining whether constraint checking is required; and if it is required, determining whether to implement full, or incremental checking for compliance with said constraints; and then checking the data in the database in accordance with the determined constraint checking procedure.

Abstract: A method, apparatus, and article of manufacture for optimizing database queries using a materialized view for a table referenced in the query, wherein the materialized view has different properties than the referenced table. The materialized view may be replicated across multiple processors of the computer system, so that some or all of the query can be executed in a local fashion wherein there is no data movement required to perform the operations. The materialized view also may be partitioned across multiple processors of the computer system using a different partitioning key than the referenced table. The materialized view may be a vertical and/or horizontal subset of the table, so that only selected columns and/or tuples from the table are present therein. Columns may be added to the materialized view to contain pre-computed results of complex expressions, and indices may be created on the columns of the materialized view.

Abstract: In a relational database management system (RDMS), a method and system for constraint checking of bulk data after storage in a base table. This invention generates a "dummy" INSERT inquiry to cause the compiler constraint rule engine to generate code for checking for constraint violations. After construction of a Query Graph Model by the compiler, a logic module replaces the INSERT inquiry with a SELECT inquiry to create code that when executed will select records from the bulk data table. Constraint violations are handled in several ways by this invention, including merely reporting that a constraint error exists or listing those records that violate constraint conditions. The logic also enforces referential integrity constraints by applying the SQL CASCADE command to the violating records to expand the violation list to include children records that would be orphaned when their parent records are later removed.