Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1999-05-18
2001-06-19
Alam, Hosain T. (Department: 2172)
Data processing: database and file management or data structures
Database design
Data structure types
Reexamination Certificate
active
06249791
ABSTRACT:
FIELD OF THE INVENTION
The present invention pertains to the field of computer systems including, more particularly, to methods and apparatus of summary table management in computer systems.
BACKGROUND OF THE RELATED ART
In database management systems (DBMS), including relational database systems, the retrievable data is typically stored in the form of tables. Typically each column of a given table-represents a particular data attribute while each row represents a specific record. The row/column combination, e.g., each field of a row, represents a particular data item. A user of a database accesses data from one or more tables by submitting a query to the respective DBMS. The DBMS typically responds to the query by constructing a particular view of the data stored in the tables and returning a responsive data set to the user.
In known large database systems, users often issue queries which require aggregation of data from large tables and/or which require aggregation of data from two or more tables. Responding to such queries can be time consuming. As a result, it is not uncommon for a database to include one or more summary tables, which contain selected subsets of data items, and/or aggregations of data items from one or more tables. A particular advantage of summary tables is that they contain pre-generated values, which allow the DBMS to more quickly access and retrieve queried data, a task that could otherwise be costly and time consuming.
One of the issues with database management systems which utilize summary tables is knowing which summary tables to create to most benefit system performance. Summary tables may be large and there may be many of them associated with a single database. As such, summary tables can take up considerable space in the database and large amounts of server CPU time may be required to generate and populate the summary tables on a regular basis. Thus, the convenience of using summary tables is usually balanced against the resource time expended in creating and refreshing the summary tables, as well as the additional storage space required to maintain them.
Consequently, database system administrators, those individuals tasked with administering a database management system, need to know precisely what summary table they should create and with what combinations of information. Ideally a system administrator would like to create only those summary tables that would make the best use of the system resources. Known current systems traditionally depend on someone to analyze user requirements and, based upon this analysis, decide upon a set of summary tables to satisfy the users' requirements. Thus, the system administrators spend considerable time analyzing user queries to determine which summary tables will provide the most benefit. Alternatively, system administrators may use ad hoc methods to guess which set of summary tables will best serve the system. While the latter approach results in less time to create the summary tables, it can result in summary tables being built and maintained that are not particularly useful or optimum. In any event, once those queries for which a summary table is to be built are identified, the DBA must then execute the requisite system instructions to build the summary tables. Finally, the use of tables in a database may vary over time, either because of new data or because of different query patterns. It is difficult for a system administrator to know when these changes have occurred, rendering a particular summary table unnecessary.
Thus, it would be advantageous to provide a system that will automatically tune itself according to system demands to achieve faster searching and retrieval of data while keeping system resource usage at a minimum. It would be advantageous to have an improved methodology for identifying data sets for which summary tables should be created and maintained for a given database and for determining when a summary table is no longer beneficial due to changed data or query patterns. It would be further advantageous to provide a mechanism to automatically create and remove summary tables from the database.
The present inventions solve these problems and more.
SUMMARY OF THE INVENTION
The present invention includes a self monitoring system that automatically tunes itself according to system demands, such as for example, system loads and patterns of use, resulting in more efficient execution of user queries while minimizing machine resources required to accomplish this. The self monitoring system includes the collection of statistics about system usage to assist in the selection of summary tables to create and the capability to create, populate and refresh summary tables.
In a preferred embodiment, query statistics are generated from submitted queries and stored in a local stat table. The query statistics from local stat tables are used to create a system stat table. Based on a combination of the frequency of a submitted query, and the length of time required for the computer system to execute the query, recommendations for creating or deleting summary tables are formed.
Upon selection of one or more recommendations for summary table creations, the system can automatically create the table. In the preferred embodiment the self-monitoring system automatically generates an appropriate SQL query, allocates memory for the summary table to be created, executes the generated SQL query, and populates the summary table with the appropriate data set. Upon selection of a recommendation to delete a summary table, the system automatically deletes the selected summary table from the database.
In accordance with one aspect of a preferred embodiment of the present invention, a database applications layer of a user station of a computer system may be configured to automatically monitor a database and automatically create or delete summary tables from a database. By example, in a preferred embodiment, a database applications layer includes a program to generate summary table creation and/or deletion recommendations at specified time intervals, and, thereafter, submit one or more queries to a DBMS to create a predetermined number of summary tables corresponding to the highest ranked summary table creation recommendations and/or delete a predetermined number of summary tables corresponding to the highest ranked summary table deletion recommendations.
An object of an aspect of the present inventions is to provide a methodology for generating statistical data on user-submitted queries, and thereafter use the generated query statistics to form a ranking of recommended summary tables to be created and/or deleted. Another object of an aspect of the present inventions is to provide a methodology for automatically creating or deleting summary tables based on selected summary table recommendations.
Other and further objects, features, aspects, and advantages of aspects of the present inventions are found in the following detailed description of the preferred embodiments illustrated in the accompanying drawings.
REFERENCES:
patent: 5404510 (1995-04-01), Smith et al.
patent: 5519859 (1996-05-01), Grace
patent: 5664173 (1997-09-01), Fast
patent: 5675785 (1997-10-01), Hall et al.
patent: 5864846 (1999-01-01), Voorhees et al.
Designer's Guide Manual For Business Objects For Windows 4.0 (section entitled “Aggregate Navigation”), pp. 133-135, Sep. 1996.
Cave Stephen D.
Lavender Robert L.
Osborn Andrew P.
Alam Hosain T.
Lyon & Lyon LLP
Oracle Corporation
Shah Sanjiv
LandOfFree
Summary table management in a computer system does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Summary table management in a computer system, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Summary table management in a computer system will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-2450160