Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-01-13
2004-07-27
Corrielus, Jean M. (Department: 2172)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000, C707S793000
Reexamination Certificate
active
06768987
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Identification of Copyright
A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
2. The Field of the Invention
The present invention relates generally to computer-implemented database systems. More specifically, the present invention relates to a system and method for filtering explain tables according to user-defined filters.
3. The Relevant Technology
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database system which uses relational techniques for storing and retrieving data. Relational databases are organized into tables consisting of rows (tuples) and columns of data. A database typically includes many tables, and each table includes multiple rows and columns. The tables are conventionally stored in direct access storage devices (DASD), such as magnetic or optical disk drives, for semi-permanent storage.
Generally, users communicate with an RDBMS using a Structured Query Language (SQL) interface. The SQL interface allows users to create, manipulate, and query a database by formulating relational operations on the tables, either interactively, in batch files, or embedded in host languages such as C and COBOL. SQL has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
The SQL standard provides that each RDBMS should respond to a particular query in the same way, regardless of the underlying database. However, the method that the RDBMS actually uses to find the requested information in the database is left to the RDBMS. Typically, there is more than one method that can be used by the RDBMS to access the requested data. The RDBMS, therefore, attempts to select the method that minimizes the computer time and resources (i.e. cost) for executing the query.
The RDBMS determines how to execute the SQL statements. The set of steps created by the RDBMS for executing the SQL statements is commonly referred to as the “access path.” In other words, the access path is a sequence of operations used by the RDBMS to obtain the data requested by the SQL query. Depending on the access path, an SQL statement might search an entire table space, or, alternatively, it might use an index. The access path is the key to determining how well an SQL statement performs. The description of the access path is stored in a plan table, which typically stores the access path data for a plurality of SQL statements.
In addition to determining the access path, many databases estimate the cost (in time or service units) for executing each SQL statement. Typically, the estimated costs are stored in a statement table (in the case of DB2® for OS/390®) or another similar table in the database. Like the plan table, the statement table generally stores the estimated statement costs for a plurality of SQL statements.
Moreover, some databases store information relating to user-defined functions in a function table. User-defined functions can be very useful in developing database applications. Accordingly, it is advantageous to have information relating to the user-defined functions in a single, convenient location.
Collectively, the above-described access path data statement cost data, and function data are referred to as “explain” data. The plan table, statement table, and function table are often called “explain tables.” As noted above, the explain data is typically generated at bind time. However, the explain data can also be generated dynamically in response to a user-supplied query statement.
Unfortunately, because of the volume of data in the explain tables, users interested in improving SQL query performance often find the process of analyzing the explain tables difficult and time consuming. For example, it is typically difficult for a user to find the rows in the explain tables that are related to specific statements, functions, applications, and the like. To locate and view the desired rows, the user is typically required to formulate complex SQL queries, which requires a detailed knowledge of the underlying database.
Accordingly, what is needed is a system, method, and article of manufacture for filtering explain tables according to user-defined filters. What is also needed is a system, method, and article of manufacture for generating filters based on user-specified filtering criteria directed to one or more columns of the explain tables. Moreover, what is needed is a system, method, and article of manufacture for specifying filtering criteria in a simple and intuitive manner without the need for formulating complex SQL queries. Additionally, what is needed is a system, method, and article of manufacture for selectively storing and retrieving the user-defined filters.
SUMMARY OF THE INVENTION
The present invention solves the foregoing problems by providing a system, method, and article of manufacture for filtering explain tables according to user-defined filters. In one aspect of the invention, an apparatus for filtering an explain table includes a processor for executing instructions and a memory device having thereon modules of operational data and executable code for execution by the processor. In one embodiment, the modules include a filter generation module configured to receive user-specified filtering criteria directed to data within a selected column of the explain table and generate in response a user-defined filter. In one embodiment, the modules also include a table filtering module configured to apply the user-defined filter to the explain table to selectively exclude rows of the explain table not satisfying the filtering criteria of the user-defined filter.
In another aspect of the invention, a method for filtering an explain table comprising rows and columns includes the step of receiving user-specified filtering criteria directed to at least one column of the explain table and generating in response a user-defined filter. In one embodiment, the method also includes the step of applying the user-defined filter to the explain table to selectively exclude rows of the explain table not satisfying the filtering criteria of the user-defined filter.
In yet another aspect of the invention, an article of manufacture comprises a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform the above-described method for for filtering an explain table comprising rows and columns according to least one user-defined filter.
These and other objects, features, and advantages of the present invention will become more fully apparent from the following description and appended claims, or may be learned by the practice of the invention as set forth in the following specification.
REFERENCES:
patent: 4956774 (1990-09-01), Shibamiya et al.
patent: 5091852 (1992-02-01), Tsuchida et al.
patent: 5257185 (1993-10-01), Farley et al.
patent: 5455945 (1995-10-01), VanderDrift
patent: 5544355 (1996-08-01), Chaudhuri et al.
patent: 5548755 (1996-08-01), Leung et al.
patent: 5560007 (1996-09-01), Thai
patent: 5617567 (1997-04-01), Doktor
patent: 5734884 (1998-03-01), Eberhard et al.
patent: 5761654 (1998-06-01), Tow
patent: 5787416 (1998-07-01), Tabb et al.
patent: 5829983 (1998-11-01), Koyama et al.
patent: 5832501 (1998-11-01), Kain, III et al.
patent: 5924088 (1999-07-01), Jakobsson et al.
patent: 5974410 (1999-10-01), Copeland et al.
patent: 6003022 (1999-12-01), Eberhard et al.
patent: 6052681 (2000-04-01), Harvey
patent: 6061676 (2000-05-01), Srivastava et al.
patent: 6192358 (2001-02-01), Fuh et al.
patent: 6285996 (2001-09-01), Jou et al.
patent: 6446062 (2002-09-01), Levine et al.
patent: 6516310 (20
Couch Tanya
Drummond Catherine Elizabeth Wuebker
Hughes, Jr. Virginia Walbridge
Lai Theresa Hsing
Lea, III Joseph Davis
Corrielus Jean M.
Kunzler & Associates
Ly Anh
LandOfFree
System and method for filtering explain tables does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with System and method for filtering explain tables, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and System and method for filtering explain tables will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3248600