Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1999-12-14
2004-03-09
Pardo, Thuy N. (Department: 2175)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000, C707S793000
Reexamination Certificate
active
06704724
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular, to a parallel optimizer for a relational database management system that allows the input of hints with a direct manipulation interface.
2. Description of Related Art.
Relational DataBase Management Systems (RDBMS) using a Structured Query Language (SQL) interface are well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American Nationals Standard Institute (ANSI) and the International Standards Organization (ISO).
The SQL interface allows users to formulate relational operations on one or more tables in the relational database, which in turn generate a result set that is a subset of data from the tables. While there have been various techniques developed for optimizing the performance of SQL statements, query optimization remains a difficult task.
Generally, an optimizer function within the RDBMS tries to find a query execution plan that will consume the least resources and run the fastest. Doing so requires information about the underlying tables in the relational database that might not necessarily be available.
To deal around the problem of insufficient information, an RDBMS typically provides the capability for the user to supply “hints” along with a query specification. These hints are usually specified as extra keywords or commands interspersed within the SQL statements using a “command-syntax” approach. These hints provide the optimizer function of the RDBMS with help in the form of extra information that can be used to generate the most efficient query execution plan.
Optimizer hints influence the query execution plan. In the simplest terms, the optimizer of the RDBMS takes the query, plus any hints supplied, and generates a query execution plan that is executed by the RDBMS. Hints can be used to influence the optimizer to choose one plan over another when there is insufficient information for it to make the choice on its own.
The problem with the command-syntax approach is that the user must specify the hints using commands and keywords that indirectly affect the plan generated by the optimizer function. The optimizer interprets the hints along with the other information it knows about to generate the final query execution plan. There is no opportunity to directly affect the plan that is produced, and so several iterations of supplying additional or different hints may be necessary before the optimizer correctly “guesses” what the user has in mind. This results in a development cycle that is more difficult and time consuming than is necessary.
While there have been various techniques developed for optimizing the performance of RDBMS, there is a need in the art for techniques that provide improved user interfaces for adjusting query execution plans.
SUMMARY OF THE INVENTION
The present invention discloses a method, apparatus, and article of manufacture for directly manipulating a query for a relational database management system (RDBMS). The query is transformed into an operator tree that is displayed on a monitor, wherein the operator tree includes nodes for data sources and operators referenced in the query, and lines between the nodes. The RDBMS alters an execution plan for the query in response to one or more manipulations made to the displayed operator tree by the user. Generally, these manipulations comprise hints for an optimizer function of the RDBMS, so that an efficient execution plan can be generated for the query. Specifically, the hints influence the optimizer to choose one execution plan over another when there is insufficient information for the optimizer function to make a choice on its own.
An object of the present invention is to optimize the database access on parallel processing computer systems. Another object of the present invention is to improve the performance of database partitions managed by a parallel processing computer systems.
REFERENCES:
patent: 5548749 (1996-08-01), Kroenke et al.
patent: 5627979 (1997-05-01), Chang et al.
patent: 5694591 (1997-12-01), Du et al.
patent: 5745746 (1998-04-01), Jhingran et al.
patent: 5761653 (1998-06-01), Schiefer et al.
patent: 5857180 (1999-01-01), Hallmark et al.
patent: 5873075 (1999-02-01), Cochrane et al.
Srivastava et al., “Optimizing Multi-Join Queries in Parallel Relationa Databases”, IEEE, 1993, pp. 84-92.*
“Scattering and Gathering Data among Presentation Spaces”, IBM technical Disclosure Bulletin, Mar. 1990, US, vol. 32, pp. 24-27.*
Alfonso F. Cardenas et al., “The Knowledge-Based Object Oriented PICQUERY+Language”, IEEE Transactions on Knowledge and Data Engineering, vol. 5, No. 4, Aug. 1993, pp. 644-657.
Navin Kabra et al., “OPT++: An Object-Oriented Implementation for Extensible Database Query Optimization”, The VLDB Journal, vol. 8, 1999, pp 55-78.
William O'Connell et al., “The Teradata SQL3 Multimedia Database Server”, Book: Multimedia Technology for Applications, Editors: Sheu/Ismail, Publisher: IEEE Press 1998.
Ellmann Curtis John
Kostamaa Olli Pekka
Gates & Cooper
NCR Corporation
Pardo Thuy N.
LandOfFree
Parallel optimizer hints with a direct manipulation user... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Parallel optimizer hints with a direct manipulation user..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Parallel optimizer hints with a direct manipulation user... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3186865