Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.