Optimization of queries using relational algebraic theta-semijoin operator

United States Patent [w]

Srivastava et al.

US006032144A [ii] Patent Number: 6,032,144 [45] Date of Patent: *Feb. 29,2000

[54] OPTIMIZATION OF QUERIES USING RELATIONAL ALGEBRAIC THETASEMLJOIN OPERATOR

[75] Inventors: Divesh Srivastava, New Providence, N.J.; Peter J. Stuckey, Elwood, Australia; Sundararajarao Sudarshan,

Powai, India

[73] Assignee: Lucent Technologies Inc., Murray Hill, N.J.

[ * ] Notice: This patent is subject to a terminal disclaimer.

[21] Appl. No.: 08/862,459 [22] Filed: May 23, 1997

Related U.S. Application Data

[60] Provisional application No. 60/018,391, May 29, 1996.

[51] Int. C I. G06F 17/30

[52] U.S. CI 707/3; 707/2; 707/4; 707/5

[58] Field of Search 707/1, 2, 3, 4,

707/5, 6, 100, 102, 103; 702/2, 4

[56] References Cited

U.S. PATENT DOCUMENTS

5,548,755 8/1996 Leung et al 707/2

5,687,362 11/1997 Bhargava et al 707/2

5,694,591 12/1997 Du et al 707/2

5,701,454 12/1997 Bhargava et al 707/2

5,713,015 1/1998 Goel et al 707/2

OTHER PUBLICATIONS

Implementation ol Magic-sets in a Relational Database
System, I.S. Mumick and H. Pirahesh, SIGMOD Record,
vol. 23, Issue 2, Jun. 1994, pp. 103-114.
Query Optimization by Predicate Move-Around, A.Y. Levy,
I.S. Mumick, Y. Sagiv, Proceedings ol the 20th VLDB
Conlerence, Santiago, Chile, 1994, pp. 96-107.
Magic is Relevant, I.S. Mumick, S.J. Finkelstein, H.Pira-
hesh, R. Ramakrishnan, SIGMOD Record, vol. 19, Issue 2,
Jun. 1990, pp. 247-258.

Complex Query Decorrelation, P. Seshadri, H. Pirahesh,
T.Y.C. Leung, Proceedings—12th Int'l Conlerence on Data
Engineering sponsored by IEEE Computer Society Techni-
cal Committeee on Data Engineering, pp. 450-458.
Translating SQL Into Relational Algebra: Optimization,
Semantics, and Equivalence ol SQL Queries, S. Ceri and G.
Gottlob, IEEE Transactions on Software Engineering, vol.
SE-11, No. 4, Apr. 1985, pp. 324-345.
Compiling Query Constraints, P.J. Stuckey and S. Sudars-
han, SIGMOD/PODS 94 - 5/94, Minneapolis, MN, USA,
1994 ACM 0-89791-639-5/94/0005, pp. 56-67.
Query Porcessing in a System for Distributed Databases
(SDD-1), PA. Bernstein, N. Goodman, E. Wong, C.L.
Reeve and J.B. Rothnie, Jr., ACM Transactions on Database
Systems, vol. 6, No. 4, Dec. 1981, pp. 602-625.
Space/Time Trade-offs in Hash Coding with Allowable
Errors, B.H. Bloom, Communications ol the ACM, vol. 13,
No. 7, Jul. 1970, pp. 422-426.

Optimization ol Nonrecursive Queries, R. Krishnamurthy,
H. Boral, C. Zaniolo, Proceedings ol the 12th IntT Conl. on
Very Large Data Bases, Kyoto, Aug. 1986, pp. 128-137.

(List continued on next page.)

Primary Examiner—-Wayne Amsbury
Assistant Examiner—Srirama Channavajjala
Attorney, Agent, or Firm—Geraldine D. Monteleone

A collection ol equivalence rules involving the multiset version ol the relational algebraic theta-semijoin operator is used to generate relational algebraic expressions equivalent to a computer programming language query. These expressions may be employed as a search space which is utilized by, for example, optimizing software or software that determines the equivalency ol queries. Cost formulas for the multiset version ol the theta-semijoin operator may be used in computing cost estimates for the generated expressions. Based on these computed cost estimates, the least costly implementation ol a complex query is determined. Thus, queries are cost-based optimized on both a local and global basis by use ol the relational algebraic theta-semijoin operator.

22 Claims, 2 Drawing Sheets

OTHER PUBLICATIONS

Stefano Ceri et al., Translating SQL Into Relational Algebra: Optimization, Semantics, and Equivalence of SQL Queries, IEEE 1985, 324-345, Apr. 4, 1985.

Umeshwar Dayal, Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates and Quantifiers, Proceedings of the 13th VLDB Conference, Brighton 1987, 197-208, Sep. 4, 1987.

C J Date, An Introduction of Database Systems, Text Book, Chap 6, p. 154 [sixth edition], 1995.

Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, and Quantifiers, U. Dayal, Proceedings of the 13th VLDB Conference, Brighton 1987, pp.197-208.