International Business Machines Corporation

Abdo Esmail Abdo

Kurt Walter Pinnow

Michael Steven Faunce

Travis Michael Drucker

Robert J Bestgen

Kevin James Kathmann

Troy David Armstrong

Larry Wayne Loen

Robert Douglas Driesch, Jr

Lance Christopher Amundsen

Daniel Virgil Toft

Statistics for a pattern matching predicate are generated using stored character statistics. A first structure stores, for each of a plurality of character positions, frequently occurring characters in that character position, and a count of the number of occurrences of that character. A second structure stores frequently occurring characters that are subsequent to the frequently occurring characters stored in the first structure, and a probability of occurrence of each frequently occurring subsequent character. To form an estimate of the number of tuples matching a pattern matching predicate, statistics are retrieved for the matching characters in each matching position in the predicate, and then combined to produce the estimate. In the event a statistic is not stored for a desired character, the available statistics are used to make an estimate by accumulating statistics for other characters, and then calculating average frequency of occurrence of characters that do not have stored statistics.

Generating statistics on text pattern matching predicates for access planning

In processing a query including a selection criterion on one or more attributes of a relation, a prior statistic generated for a prior different selection criterion on the same one or more attributes of the relation, may be revalidated for use in processing the query, based upon a measure of the entropy of the one or more attributes of the relation. In this way, the re-validation of statistics may be performed more efficiently. Furthermore, attribute groups of a relation for which multi-dimensional indexes are to be formed, are identified by evaluating the correlation of attribute values within tuples of the relation and determining that the correlation of attribute values within tuples of the relation exceeds a threshold.

Estimation and use of access plan statistics

A join operation is costed by estimating the input/output operations (disk swaps) that will be required for retrieval of tuples from the outer and inner tables. A specific method is presented for estimating the number of input/output operations needed for given inner and outer data tables, which method models the retrieval of randomly arranged data from that table.

Estimation of input/output requirements for table probe in join processing

A cardinality estimator computes an estimated cardinality for a database column using a formula that accounts for skew in the data in the column. The result is an estimate of cardinality that more closely resembles the actual cardinality in the column. The improved cardinality estimate may then be used to make better query optimizations.

Apparatus and method for estimating cardinality when data skew is present

A method for computing clustering factor that is particularly suitable for use with existing indexes. The clustering factor is generated, by first determining clustered storage locations of records in a relation, i.e., locations where the records would be found if they were clustered relative to the attribute (e.g., locations for the records if they were ordered in storage in accordance with the attribute). Then, the actual storage locations of records are correlated to the clustered storage locations, and a clustering statistic is generated based upon the correlation.

Estimation of clustering for access planning

A method for dispatching input/output access requests to a direct access storage device (DASD) is disclosed. Each of a group of I/O access requests to DASD is assigned into one of at least two I/O access request categories, namely, high priority I/O access requests and low priority I/O access requests. Each of a group of I/O slots within DASD is assigned into one of at least two I/O slot categories, namely, high priority I/O slots and low priority I/O slots. An I/O access request from a first one of the two I/O access request categories is sent to any one of the I/O slots. An I/O access request from a second one of the two I/O access request categories is sent to only a slot belonging to a subset of the two I/O slot categories.

Allowing slots belonging to a second slot category to receive I/O access requests belonging to a first and a second access request categories in a round robin fashion

A storage format and methods for improving the performance of the symbol table of an encoded vector index. The symbol table comprises a hash table, entries of the hash table storing associated key values and codes for the encoded vector index. Hash table entries store an accumulated count of occurrences of prior and the current key values, which improves the efficiency of responding to a request for a key range count. A binary radix tree is used to locate entries, which comprises a plurality of nodes, corresponding to binary digits of a binary representation of a key value. Codes are assigned to key values for the encoded vector index in a distributed fashion, so there are available code values between existing code values in the code ordering, that can be assigned to new key values, alleviating the need to reorganize the code values upon an insertion.

Storage format for encoded vector indexes

Query results and statistics regarding them are generated using a novel representation of an n-attribute relation as an order n relational tensor. Orders of the relational tensor respectively correspond to each of the attributes, and each coordinate along an order relates to a key value of the corresponding attribute. Numeric values are stored in the relational tensor, each numeric value representing a count of tuples having the attribute key values that correspond to the coordinate of the numeric value along the orders of the relational tensor. This storage representation is useful in a variety of contexts for enhancing the performance of a RDBMS system. Specifically, a data-representing relational tensor can be used to produce results for join operations such as the SQL operations JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.

Growing community of inventors

Rochester, MN, United States of America

Abdo Esmail Abdo