Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-02-02
2003-06-24
Shah, Sanjiv (Department: 2172)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000
Reexamination Certificate
active
06584474
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates to a method of analysis and of increasing the speed at which the condition of a database is determined. The invention is more particularly related to the determination of appropriate data needed for a comprehensive analysis of specific tables in the database, and to increase the speed at which the analysis for determining the condition of a database table is performed.
2. Discussion of the Background
With the increasing use of computer databases in each of the home, office, and scientific environments, computer users and Database Administrators (DBA's) are increasingly confronted with the tasks of determining the condition or health of databases and performing various operations to maintain the database, including reorganization or rebuilding of tables stored in the database. Before rebuilding a database table(s), the DBA must first determine whether the condition of the database table(s) requires rebuilding.
Typically, commercial database products include tools to perform analysis to determine the condition of a database. For example, Oracle database management systems include an analyze function that performs a predetermined function including an SQL call UPDATE_STATISTICS_FOR_TABLE. The analyze function reads a selected database table and generates a set of statistics that reveal the condition of the table.
FIG. 1
is a flowchart illustrating the basic steps performed by a conventional Database Management System (DBMS) for determining the condition of a database table. At step
10
, a Database Administrator (DBA) provides the name of a table to be analyzed. The table name is provided to a DBMS function that returns data about the named table (step
20
). The returned data is analyzed resulting in statistical information (step
30
) which is then placed (stored) in a location for retrieval for by DBA (step
40
).
The DBMS function that returns data (DBMS analysis function) is shown in FIG.
2
and includes the steps of building an SQL call (step
50
), invoking the SQL call (step
60
), performing SQL overhead and administrative functions (step
70
), and returning the SQL results (step
80
). The SQL results are provided as the return values (statistical information) in step
30
of FIG.
1
.
Once the SQL results are analyzed, the displayed (or stored) information is utilized by the DBA to determine appropriate action, if needed, to properly maintain the database (rebuild tables, for example). In the example function, UPDATE_STATISTICS_FOR_TABLE, the Oracle DBMS provides table information including No. of Rows, Chained Rows, Blocks allocated, Blocks allocated but free, Blocks on freelist, Average available space in Freelist, Average free space in a Block, and Min, Max, Average Row Length.
However, the provided table information does not provide enough information to give the DBA the most accurate picture of the DBMS table condition. In addition, current methods for retrieving the DBMS table information are slow, being encumbered by overhead associated with standard DBMS procedures and function calls (file reading techniques, and SQL, for example).
Modern database tables contain enormous amounts of data. If a DBA needs to determine the condition of a large database table, analysis techniques based on current methods can take unacceptably large amounts of time during which a database table may not be available to a user. Furthermore, current methods do not provide the DBA with the exact information needed for a proper analysis. Therefore, the DBA may rebuild tables when it is not necessary or where other action may have been more appropriate. All of the above leads to increased down time and maintenance efforts.
SUMMARY OF THE INVENTION
The present inventor has realized the need for increasing the amount of data gathered to determine the condition of tables within a database and to increase the speed at which the condition is determined. Accordingly, it is an object of the present invention to provide a comprehensive analysis of at least one database table to determine an information set relating to the health of the tables analyzed.
It is another object of this invention to provide method to increase the speed at which database tables are analyzed.
These and other objects are accomplished by a method for analyzing database tables including the steps of identifying a database table, determining elements corresponding to the condition of the database table based on data contained in the database tables and providing said elements to one of a display for viewing by a user, a storage location, and a mechanism for using said elements. The elements determined include at least one of number of simple rows, number of migrated rows, number of deleted rows, number of blocks initialized but empty, average free space in a block unused, average free released free space, average row length of migrated rows, average row length of chained rows, number of blocks to be read for normal, chained, and migrated rows, measure of density of blocks used, number of blocks exceeding a percentage used threshold, number of blocks less a percentage free threshold, number of chained rows spanning data files, and min max average of chained rows spanning blocks.
In addition, a method to increase the speed of analysis is provided including the steps of identifying a table in said database, reading each of blocks associated with said table using direct asynchronous I/O, and performing said analysis based on information contained in said blocks.
Additional speed of analysis is provided by parallel processing and bypassing SQL overhead associated with regular RDBMS architectures. The parallel processing includes the steps of dividing the blocks read into plural sets, and providing each set to separate processes to determine information needed for the analysis.
REFERENCES:
patent: 4506326 (1985-03-01), Shaw et al.
patent: 5261093 (1993-11-01), Asmuth
patent: 5806060 (1998-09-01), Borgida et al.
patent: 5864856 (1999-01-01), Young
patent: 5873091 (1999-02-01), Garth et al.
patent: 5974407 (1999-10-01), Sacks
patent: 6081809 (2000-06-01), Kumagai
patent: 6094650 (2000-07-01), Stoffel et al.
patent: 6112238 (2000-08-01), Boyd et al.
patent: 6122640 (2000-09-01), Pereira
patent: 6185555 (2001-02-01), Sprenger et al.
Oracle 7tm ServerSQL Language Reference Manual, Part No. 778-70-1292, Dec. 1992, pp. 4-89 thru 4-96.
Oracle 7tm ServerConcepts Manual, Part No. 6693-70-1292, Dec. 1992, pp. 15-1 thru 15-14.
Calfee Halter & Griswold LLP
Computer Associates Think Inc.
Shah Sanjiv
LandOfFree
Method and apparatus for fast and comprehensive DBMS analysis does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Method and apparatus for fast and comprehensive DBMS analysis, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method and apparatus for fast and comprehensive DBMS analysis will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3141543