International Business Machines Corporation

Randy Lynn Egan

Roger Alan Mittelstadt

Paul Reuben Day

Brian Robert Muras

Rafal Przemyslaw Konik

Jeffrey Wayne Tenner

Mark William Theuer

Kevin James Kathmann

Robert Douglas Driesch, Jr

Robert J Bestgen

Curtis Neal Boger

Thuan Quang Bui

Carol Ledermann Ramler

Gary Ross Ricard

Richard Pervin King

Kurt Walter Pinnow

Michael Steven Faunce

John David Dietel

Roy L Hoffman

Daniel Manual Dias

Thomas Paul Giordano

Christos Alkiviadis Polyzois

John Francis Edwards

Scott Joseph Heimer

Dale M Stahl

A method, apparatus and program product that determine a density of a key value referenced in a database query over at least one range of rows, and utilize the determined density to optimize the database query. By doing so, the density of various key values may be determined and compared, and a bit map may be generated over the range of rows of the key value that is denser, resulting in a reduction of resources to build and use the bit map. Moreover, from the bit map over the range of rows to be selected after using the determined density, dense regions may be identified that can be used to optimize block I/O's according to the number of rows to be selected based on the query.

Determining a density of a key value referenced in a database query over a range of rows

In an embodiment, a first plurality of rows of a first table are read from memory in an order of page addresses of pages in the memory that comprise the first plurality of rows. First selected rows from the first plurality of rows that meet a query predicate are stored into a result set. After the reading the first plurality of rows in the order of the page addresses, a second plurality of rows of the first table are read from secondary storage, in an order of row identifiers of the second plurality of the rows in the first table. Second selected rows from the second plurality of rows that meet the query predicate are stored into the result set.

Reading rows from memory prior to reading rows from secondary storage

A disappearing index allows faster processing of a database query without the startup time of a prior art sparse index. The disappearing index starts as a full index but is trimmed of index values that point to a row in the table that is not selected by the query. Thus the traditional index disappears as it becomes a sparse index tailored to the query. The query is able to execute faster using the now sparse index since the target database table is not accessed for duplicate values in the base table of the query. The query optimizer may determine to use a disappearing index based on estimates of the number of duplicate values in the base table. When the query is complete, the created disappearing table may be discarded or used for other queries that match the selection of the query that created the disappearing index.

Disappearing index for more efficient processing of a database query

A method, apparatus and program product use a first index associated with a field in a database table to identify a range of records in the database table that includes instances of a first key value in the field and use the identified range of records to subset a second index associated with another field in a database table. The database query identifies the first key value for the field in the database table and the second key value for the other field in the database table. By doing so, information from an index may be carried over and applied to another index to subset the other index, often reducing the quantity of entries that are searched in the other index and improving performance.

Database query optimization using index carryover to subset an index

Techniques are disclosed for identifying impact of a patch. One embodiment of the invention provides a DBMS that is configured to manage one or more databases. The DBMS may receive a request to evaluate a patch for the DBMS. The DBMS provides a first execution environment that is not based on the patch and a second execution environment that is based on the patch. For each query, the DBMS compares a first access plan with a second access plan, where the first access plan is generated from the first execution environment and the second access plan is generated from the second execution environment. The DBMS identifies one or more queries having differing access plans, based on the comparisons.

Identifying impact of installing a database patch

In an embodiment, a threshold value is calculated for a current entry in a first column of an index. If the current entry has been read a number of times by execution of an execution plan that is more than the threshold value and the current entry points to at least one row in a table and not all of the at least one row have been selected by the execution plan for inclusion in a result set as satisfying a query that the execution plan implements, then a new entry is added to the index.

Adding entries to an index based on use of the index

Techniques are disclosed for reorganizing rows of a database table. In one embodiment, each row is associated with a respective row identifier. A mapping is provided that assigns a logical identifier to each row identifier. Each database index on the database table is configured to use the logical identifier rather than the row identifier. Accordingly, the rows of the database table may be reorganized without having to modify any database index on the database table.

Reorganizing database tables

Systems, methods and computer program products are disclosed for managing a database cache. In one embodiment, access to a data object stored in a database is monitored. A memory residency priority for the data object is determined, based on the access patterns. Further, an access plan is determined for a query, based on the memory residency priority. Access to the data object is also monitored when the access plan is executed, thereby providing a feedback loop between determining memory residency priorities and determining query access plans.

Feedback loop between a query optimizer and a cache manager

In an embodiment, a FIRSTIO execution plan is selected that has a lowest estimated execution time for finding a number of records that satisfy the query and are simultaneously viewable. An ALLIO execution plan is selected that has a lowest estimated execution time for finding all records that satisfy the query. The FIRSTIO execution plan is executed for a first time period to create a FIRSTIO result set. The ALLIO execution plan is executed for a second time period to create an ALLIO result set. The FIRSTIO result set is displayed if the FIRSTIO result set comprises more records than the ALLIO result set. The ALLIO result set is displayed if the ALLIO result set comprises more records than the FIRSTIO result set. In an embodiment, the first and second time periods expire in response to the expiration of a maximum time specified by the query.

Executing a query plan with display of intermediate results

A method, apparatus, and program product is provided to, in response to receiving a first database query for which usage of shared weight indices is prohibited, determine whether a shared weight index associated with a column of a database table referenced by the first database query can be utilized as a non-shared weight index to optimize the database query. The method, apparatus, and program product determine whether the column includes at least one changed weight value and determines whether the database query references at least one changed weight value in the column. The shared weight index is utilized to optimize the first database query in response to determining that the shared weight index can be utilized as a non-shared weight index to optimize the first database query.

Database query optimization using weight mapping to qualify an index

A method, apparatus and program product that determine a density for each key value of a plurality of key values in a table over at least one range of rows in the database table, and store the determined densities in a data structure for use in optimizing a query that references a key value in the plurality of key values in the table. By doing so, the density of various key values may be determined and dynamically maintained, often resulting in optimization data for improved query optimization.

Growing community of inventors

Rochester, MN, United States of America

Randy Lynn Egan