Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2001-06-05
2003-11-04
Metjahic, Safet (Department: 2171)
Data processing: database and file management or data structures
Database design
Data structure types
Reexamination Certificate
active
06643636
ABSTRACT:
BACKGROUND
Relational database systems store data in tables organized by columns and rows. The tables typically are linked together by “relationships” that simplify the storage of data and make complex queries against the database more efficient. Structured Query Language (or SQL) is a standardized language for creating and operating on relational databases.
A relational database system typically includes an “optimizer” that plans the execution of SQL queries. For example, if a query requires accessing or “joining” more than two tables, the optimizer will select the order that the tables are joined to produce the requested result in the shortest period of time or to satisfy some other criteria.
In some cases, a database administrator may define a “join index” that contains one or more frequently accessed columns from one or more tables. The optimizer has a choice of accessing columns from the join index or from the base table or tables from which the data in the join index was selected. Consequently, the join index gives the optimizer additional “join paths” that it can follow in planning query execution.
Typically, an optimizer considers a join index to execute a query if it “covers” the query, that is, if the join index includes all of the columns selected by the query that are in the base tables of the join index. This means that in order for a join index to be applicable for different queries against a common set of frequently used tables, a database administrator may define a join index that has all the columns needed by these queries, perhaps resulting in a very large join index. Otherwise, the optimizer will not use the join index to execute some queries.
SUMMARY
An optimization technique is provided that allows the use of non-covering join indexes to satisfy queries. This is accomplished, for example, by recognizing if a primary key from one or more of the base tables is included in a join index and, if so, using it to join back to the base table for the missing columns.
In general, in one aspect, the invention features a method for optimizing a SQL query, in which the SQL query includes a WHERE clause and a FROM clause. The method includes evaluating whether a non-covering join index partially but not completely covers the query. If it does, the method includes adding the join index to the FROM clause of the query without removing the partially covered base tables and modifying the WHERE clause of the query by: (1) mapping a query condition to the join index for its partially covered base tables; and (2) adding a join back condition from the join index to a base table from which the join index was formed.
Implementations of the invention may include one or more of the following. The method may include evaluating the cost of a plurality of different join paths to implement the query, the difference in at least some of the join paths being at least partially attributed to an order of the join back condition in the respective join paths. The method may include selecting the least costly of the plurality of different join paths. The non-covering join index may be recognized to have a unique column that can be used to join back to a base table of the join index efficiently. Adding the join back condition may include adding a predicate to the WHERE clause which tests for equality between the unique column of the base table and the corresponding unique column included in the join index. The unique column may be a rowid column, a primary index column, or a secondary index column.
The FROM clause of the query may specify one or more tables, including one or more base tables. The entire query may reference a entire-query set of columns from the one or more base tables of the join index. The WHERE clause of the query may reference a where-clause set of columns from the one or more base tables of the join index. The join index may be defined by selecting columns from the one or more base tables. The method may further include recognizing that the non-covering join index provides partial but not complete coverage by determining that the join index does not contain all of the entire-query set of columns and determining that the join index contains a subset of the where-clause set of column. Recognizing that the non-covering join index provides partial but not complete coverage may include determining that the join index includes a primary key for each of the one or more base tables from which the join index was formed that are not completely covered. The primary key included in the join index for at least one of the base tables may be a rowid, a unique primary index, or a unique secondary index.
In general, in another aspect, the invention features a database system for accessing a database. The database system includes a massively parallel processing system, which includes one or more nodes, a plurality of CPUs, each of the one or more nodes providing access to one or more CPUs, a plurality of virtual processes each of the one or more CPUs providing access to one or more processes, each process configured to manage data stored in one of a plurality of data-storage facilities; and an optimizer for optimizing a plan for executing a query. The query includes a FROM clause and a WHERE clause. The optimizer includes a process for evaluating whether a non-covering join index partially but not completely covers the query, and if it does, adding the join index to the FROM clause of the query without removing the partially covered base tables. The optimizer further includes a process for modifying the WHERE clause of the query by (1) mapping a query condition to the join index for its partially covered base tables; and (2) adding a join back condition from the join index to a base table from which the join index was formed.
In general, in another aspect, the invention features a computer program, stored on a tangible storage medium, for use in optimizing a query including a FROM clause and a WHERE clause. The program including executable instructions that cause a computer to evaluate whether a non-covering join index partially but not completely covers the query. If it does, the program includes executable instructions that cause a computer to add the join index to the FROM clause of the query without removing the partially covered base tables, and to modify the WHERE clause of the query by (1) mapping a query condition to the join index for its partially covered base tables; and (2) adding a join back condition from the join index to a base table from which the join index was formed.
Other features and advantages will become apparent from the description and claims that follow.
REFERENCES:
patent: 5241648 (1993-08-01), Cheng et al.
patent: 5440730 (1995-08-01), Elmasri et al.
patent: 5675819 (1997-10-01), Schuetze
patent: 5848408 (1998-12-01), Jakobsson et al.
patent: 5870747 (1999-02-01), Sundaresan
patent: 5991754 (1999-11-01), Raitto et al.
patent: 6505188 (2003-01-01), Ghazal et al.
patent: 6567802 (2003-05-01), Popa et al.
Au Grace Kwan-On
Ghazal Ahmad Said
Sinclair Paul Laurence
Al-hashemi Sana
Baker & Botts L.L.P.
Metjahic Safet
NCR Corporation
LandOfFree
Optimizing a query using a non-covering join index does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Optimizing a query using a non-covering join index, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Optimizing a query using a non-covering join index will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3114932