Oracle International Corporation

Andrew P Osborn

Paolo Fragapane

Stephen D Cave

Robert Lloyd Lavender

James Adam Steadman

Christopher Evans

Kathryn Nash

Suresh Mali

Natascha Kearsey

Jitendra Dhamija

A query generator for generating a query for retrieving data from a database comprising at least one table is disclosed. The query refers to one or more data items in a first view defined to expose a portion of the database and to one or more data items that are not exposed by the first view, at least one of which has an associated reach through flag set thereby defining it as a reach through item. The query generator is adapted to: receive user input indicating selected items to be retrieved from the database; ascertain whether any of the selected items that are not exposed by the first view are defined as reach through items; and, if so: create a second view that is a copy of the first view and add a copy of each reach through item to it; and create a query that refers only to the second view and the items exposed by it.

Query generator

A method of modifying a Structured Query Language (SQL) statement in order to reduce the number of joins executed by a database is disclosed. The method includes presenting the SQL statement to the database and identifying joins in the SQL statement for removal in accordance with one or more predetermined criteria. For each identified join, the SQL statement may be modified so as to remove the identified join and to change references to a first instance of a table which is referred to by the identified join to a second instance of the table.

Structured query language table merging

The present invention relates to a query generator for generating a query for obtaining selected data from a database. The query generator includes a processor which is adapted to receive an input indicating the selected data to be obtained. The processor then analyses the input to determine whether the input requires a joining of data in different tables, and an aggregation step. If so, the processor generates a query which causes the database to aggregate the data within each of the tables as required; and, join the aggregated data, the joined aggregated data representing the selected data.

A method of rewriting a Structured Query Language (SQL) statement in order to prevent processing of a join between a master table and a detail table in a database is disclosed. The method includes determining in the SQL statement a unique identifier to a row of the master table. The unique identifier is then equated to an identifier to related rows of the detail table using the join condition. A revised SQL statement may then be produced that only refers to the detail table using the identifier to the rows of the detail table. Subsequently, the revised SQL statement is processed.

SQL predicate migration

The present invention relates to a repository which stores properties of database objects. The repository includes a first store for storing a number of predetermined properties, and a second store for storing additional properties. A processor is provided for determining or modifying the properties stored in the first and second stores.

Property extensions

A self monitoring system that identifies optimum summary table(s) for use by a database management system, and that automatically creates or deletes summary tables based on selected summary table recommendations.

Summary table management in a computer system

A query performance prediction ('QPP') module is provided as part of an applications layer residing on respective user stations operating in conjunction with a centralized host computer system. The QPP module correlates estimated system cost information for a database query provided from a database management system associated with the computer system with statistics compiled from previous queries in order to estimate the system response time. In particular, an estimated CPU time for executing the present query is derived by extrapolating actual CPU times recorded for past queries having the closest estimated costs for accessing the same, or similar, tables and items in the database, using a form of a 'nearest neighbor' algorithm to match the present query to either identical or statistically closest past queries. The estimated CPU time for the present query is then multiplied by a current ratio of total elapsed times-to-CPU times for the system to produce an estimated total elapsed time for responding to the query.

Systems and methods for estimating query response times in a computer

Methods and apparatus for effective and efficient usage of summary tables in a computer system including a database. In a computer system it is common for a database to include one or more summary tables which have pre-generated aggregations of data from one or more original, detailed database tables. In order to achieve the benefits of summary tables, they must be accessed when appropriate. Thus, information regarding the available summary database tables, including summary bitmaps for all the summary tables, is maintained in the metadata of a computer system. This information is updated whenever a summary table is created. The summary bitmaps are a main key for determining if any summary table is responsive to a user query to the computer system. The summary bitmaps map to corresponding SQL statements, which are generated to access the respective summary tables in the database. Once an SQL statement for accessing a respective summary table is identified for a particular user query, that SQL statement is then incorporated in the executable SQL query that is thereafter executed.

Growing community of inventors

Bath, United Kingdom

Andrew P Osborn