Optimization of Queries with Conjunction of Predicates

Nicoleta Liviana Tudor

Abstract


A method to optimize the access at the objects of a relational database is through the optimization of the queries. This article presents an approach of the cost model used in optimization of Select-Project-Join (SPJ) queries with conjunction of predicates and proposes a join optimization algorithm named System RO-H (System Rank Ordering Heuristic). The System RO-H algorithm for optimizing SPJ queries with conjunction of predicates is a System R Dynamic Programming algorithm that extends optimal linear join subplans using a rank-ordering heuristic method as follows: choosing a predicate in ascending order according to the h-metric, where the h-metric depends on the selectivity and the cost per tuple of the predicate, using an expression with heuristic constants.
The System Rank-Ordering Heuristic algorithm finds an optimal plan in the space of linear left deep join trees. The System RO-H algorithm saves not a single plan, but multiple optimal plans for every subset, one for each distinct such order, termed interesting order. In order to build an optimal execution plan for a set S of i relations, the optimal plan for each subset of S, consisting of i-1 relations is extended, using the Lemma based on a h-metric for predicates. Optimal plans for subsets are stored and reused. The optimization algorithm chooses a plan of least cost from the execution space.


Keywords


optimal join subplans, cost function, query tree, join optimization algorithm, h-metric, heuristic method

Full Text:

PDF

References


S. Ganguly, W. Hasan, R. Krishnamurthy, Query optimization for parallel execution, In Proceedings of the ACM SIGMOD International Conference on Management of Data SIGMOD, Ed. ACM Press, New York, pp. 9-18, 1992.
http://dx.doi.org/10.1145/130283.130291

P. G. Selinger, M. M. Astrahan, R. A. Lorie, T. G. Price, Access path selection in a relational database management system, In Proceedings of ACM SIGMOD International Conference on Management of Data SIGMOD, Boston, MA, ACM Press, New York, pp. 23-34, 1979.
http://dx.doi.org/10.1145/582095.582099

D. Chimenti, R. Gamboa, R. Krishnamurthy, Towards an open architecture for LDL, In Proceedings of the 15th International Conference on Very Large Data Bases, VLDB, Netherlands, Ed. Morgan Kaufmann Publishers Inc., San Francisco, CA, pp. 195-203, 1989.

S. Chaudhuri, K. Shim, Query optimization in the presence of foreign functions, In Proceedings of the 19th International Conference on Very Large Data Bases, Ireland, Morgan Kaufmann Publishers Inc., San Francisco, CA, pp. 526-541, 1993.

J. M. Hellerstein, Practical predicate placement, In Proceedings of the ACM SIGMOD International Conference on Management of Data, Minneapolis, ACM Press, New York, pp. 325-335, 1994.
http://dx.doi.org/10.1145/191839.191904

J. M. Hellerstein, M. Stonebraker, Predicate migration: Optimizing queries with expensive predicates, In Proceedings of the ACM SIGMOD International Conference on Management of Data, Washington, ACM Press, New York, pp. 267-276, 1993.
http://dx.doi.org/10.1145/170035.170078

S. Chaudhuri, K. Shim, Optimization of Queries with User-Defined Predicates, ACM Transactions on Database Systems, Vol. 24, No. 2, pp. 177-228, 1999.
http://dx.doi.org/10.1145/320248.320249

J. M. Hellerstein, J. M., M. Stonebraker, Predicate migration: Optimizing queries with expensive predicates, In Proceedings of the ACM SIGMOD International Conference onManagement of Data, Washington, DC, Eds. ACM Press, New York, NY, pp. 267-276, 1993.

R. Krishnamurthy, H. Boral, C. Zaniolo, Optimization of nonrecursive queries, In Proceedings of the 12th International Conference on Very Large Data Bases, Kyoto, Japan, VLDB Endowment, Berkeley, CA, pp. 128-137, 1986.

C. L. Monma, J. Sidney, Sequencing with series-parallel precedence constraints, Math. Oper. Res. 4, pp. 215-224, 1979.
http://dx.doi.org/10.1287/moor.4.3.215

K. Y. Whang, R. Krishnamurthy, Query optimization in a memory-resident domain relational calculus database system, ACM Trans. Database Syst. 15, pp. 67-95, 1990.
http://dx.doi.org/10.1145/77643.77646

J. M. Hellerstein, M. Stonebraker, Predicate migration: Optimizing queries with expensive predicates, In Proceedings of the ACM SIGMOD Conference, 1993.
http://dx.doi.org/10.1145/170035.170078

T. H. Cormen, C. E. Leiserson, R. L. Rivest, Introduction to Algorithms, MIT Press, Cambridge, MA, 1990.

P. G. Selinger, M. M. Astrahan, R. A. Lorie, T. G. Price, Access path selection in a relational database management system, In Proceedings of ACM SIGMOD International Conference on Management of Data, Boston, MA, ACM Press, New York, pp. 23-34, 1979.
http://dx.doi.org/10.1145/582095.582099

Y. E. Ioannidis, Y. C. Kang, Randomized algorithms for optimizing large join queries, In Proceedings of the 1990 ACM SIGMOD International Conference on Management of Data, Ed. ACM Press, New York, NY, pp. 312-321, 1990.
http://dx.doi.org/10.1145/93597.98740

S. Chaudhuri, K. Shim, Including group-by in query optimization, In Proceedings of the 20th International Conference on Very Large Data Bases, VLDB Endowment, Berkeley, CA, 1994.

L. D. Shapiro, Join processing in database systems with large main memories, ACM Trans. Database Syst. 11, pp. 239-264, 1986.
http://dx.doi.org/10.1145/6314.6315




DOI: https://doi.org/10.15837/ijccc.2007.3.2361



Copyright (c) 2017 Nicoleta Liviana Tudor

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.

CC-BY-NC  License for Website User

Articles published in IJCCC user license are protected by copyright.

Users can access, download, copy, translate the IJCCC articles for non-commercial purposes provided that users, but cannot redistribute, display or adapt:

  • Cite the article using an appropriate bibliographic citation: author(s), article title, journal, volume, issue, page numbers, year of publication, DOI, and the link to the definitive published version on IJCCC website;
  • Maintain the integrity of the IJCCC article;
  • Retain the copyright notices and links to these terms and conditions so it is clear to other users what can and what cannot be done with the  article;
  • Ensure that, for any content in the IJCCC article that is identified as belonging to a third party, any re-use complies with the copyright policies of that third party;
  • Any translations must prominently display the statement: "This is an unofficial translation of an article that appeared in IJCCC. Agora University  has not endorsed this translation."

This is a non commercial license where the use of published articles for commercial purposes is forbiden. 

Commercial purposes include: 

  • Copying or downloading IJCCC articles, or linking to such postings, for further redistribution, sale or licensing, for a fee;
  • Copying, downloading or posting by a site or service that incorporates advertising with such content;
  • The inclusion or incorporation of article content in other works or services (other than normal quotations with an appropriate citation) that is then available for sale or licensing, for a fee;
  • Use of IJCCC articles or article content (other than normal quotations with appropriate citation) by for-profit organizations for promotional purposes, whether for a fee or otherwise;
  • Use for the purposes of monetary reward by means of sale, resale, license, loan, transfer or other form of commercial exploitation;

    The licensor cannot revoke these freedoms as long as you follow the license terms.

[End of CC-BY-NC  License for Website User]


INTERNATIONAL JOURNAL OF COMPUTERS COMMUNICATIONS & CONTROL (IJCCC), With Emphasis on the Integration of Three Technologies (C & C & C),  ISSN 1841-9836.

IJCCC was founded in 2006,  at Agora University, by  Ioan DZITAC (Editor-in-Chief),  Florin Gheorghe FILIP (Editor-in-Chief), and  Misu-Jan MANOLESCU (Managing Editor).

Ethics: This journal is a member of, and subscribes to the principles of, the Committee on Publication Ethics (COPE).

Ioan  DZITAC (Editor-in-Chief) at COPE European Seminar, Bruxelles, 2015:

IJCCC is covered/indexed/abstracted in Science Citation Index Expanded (since vol.1(S),  2006); JCR2018: IF=1.585..

IJCCC is indexed in Scopus from 2008 (CiteScore2018 = 1.56):

Nomination by Elsevier for Journal Excellence Award Romania 2015 (SNIP2014 = 1.029): Elsevier/ Scopus

IJCCC was nominated by Elsevier for Journal Excellence Award - "Scopus Awards Romania 2015" (SNIP2014 = 1.029).

IJCCC is in Top 3 of 157 Romanian journals indexed by Scopus (in all fields) and No.1 in Computer Science field by Elsevier/ Scopus.

 

 Impact Factor in JCR2018 (Clarivate Analytics/SCI Expanded/ISI Web of Science): IF=1.585 (Q3). Scopus: CiteScore2018=1.56 (Q2); Editors-in-Chief: Ioan DZITAC & Florin Gheorghe FILIP.