System R's bottom-up query optimizer architecture forms the basis of most current commercial database managers. The paper compares the performance of top-down and bottom-up optimizers, using the measure of the number of plans generated during optimization. Top down optimizers are superior according to this measure because they can use upper and lower bounds to avoid generating groups of plans. Early during the optimization of a query, a top-down optimizer can derive upper bounds for the costs of the plans it generates. These bounds are not available to typical bottom-up optimizers since such optimizers generate and cost all subplans before considering larger containing plans. These upper bounds can be combined with lower bounds, based solely on logical properties of groups of logically equivalent subqueries, to eliminate entire groups of plans from consideration. We have implemented such a search strategy, in a top-down optimizer called Columbia. Our performance results show that the use of these bounds is quite effective, while preserving the optimality of the resulting plans. In many circumstances this new search strategy is even more effective than heuristics such as considering only left deep plans.

Resolving object references, or reference materialization, is a fundamental operation in object query evaluation. Existing reference materialization techniques fall into two categories: pointer-based and value-based. We identify several drawbacks of existing techniques and propose a hybrid technique that combines the advantages of each category. This technique relaxes the limitations of value-based techniques, while preserving much of their performance advantage over pointer-based techniques; it performs well in those cases where no existing algorithm is applicable or efficient. The hybrid technique shows even stronger performance advantages when moving from single-valued to collection-valued attributes. We present algebraic transformations to enable the hybrid technique in a rule-based query optimizer. Initial experimental results using a commercial object-oriented database show that the hybrid approach achieves significant speedup over current algorithms in many cases. The initial motivation for our work was the optimization and evaluation of object-oriented query languages, particularly OQL. However, the key features we have concentrated on, namely references and collection-valued attributes, are present in object-relational products and the SQL:1999 proposal.

Efficient algorithms for processing large volumes of data are very important both for relational and new object-oriented database systems. Many query-processing operations can be implemented using sort- or hash-based algorithms, e.g. intersections, joins, and duplicate elimination. In the early relational database systems, only sort-based algorithms were employed. In the last decade, hash-based algorithms have gained acceptance and popularity, and are often considered generally superior to sort-based algorithms such as merge-join. In this article, we compare the concepts behind sort- and hash-based query-processing algorithms and conclude that (1) many dualities exist between the two types of algorithms, (2) their costs differ mostly by percentages rather than by factors, (3) several special cases exist that favor one or the other choice, and (4) there is a strong reason why both hash- and sort-based algorithms should be available in a query-processing system. Our conclusions are supported by experiments performed using the Volcano query execution engine.>

Database query languages and their use for programming nontraditional applications, such as engineering and artificial intelligence applications, are discussed. In such environments, database programs are used to code applications that work over large data sets residing in databases. Optimizing such programs then becomes a necessity. An examination is made of various optimization techniques, and transformations are suggested for improving the performance of database programs. These transformations result in new equivalent database programs with better space and time performance. Several of these techniques apply to classical query languages, although extended query languages which include an iteration operator are specifically discussed.>

Extensions to database query languages for retrievals that involve inferencing on the nodes and edges of a graph are surveyed. Common types of inferencing are to find paths between two nodes, compute a value for a path such as a distance or an elapsed time, and to choose among alternative paths. The survey is based on the data model (relational or functional), method of extension (iteration, recursion, or special operators), interface style (string or tabular), and restrictions (data- and problem-oriented). The Quel, objected-oriented functional data, G-Whin, and Alpha languages are examined in detail with different values for these properties. The characteristics of other languages are summarized in several tables. The results of the survey indicate the diversity of language extensions and the need to provide data-model and query-language features to address such problems.>

The computational completeness is demonstrated of certain extended database query languages, namely, POSTGRES and GENESIS, and a language defined by A. Aho and J. Ullman (1979). The method used is to implement a Turing machine interpreter in each of the languages. These query languages were defined as extensions to traditional database languages in order to encompass certain specific new applications. Results show that these extensions have already encompassed all the computational power of any programming language.>

We study algorithms for computing the equijoin of two relations in a system with a standard architecture hut with large amounts of main memory. Our algorithms are especially efficient when the main memory available is a significant fraction of the size of one of the relations to he joined; but they can be applied whenever there is memory equal to approximately the square root of the size of one relation. We present a new algorithm which is a hybrid of two hash-based algorithms and which dominates the other algorithms we present, including sort-merge. Even in a virtual memory environment, the hybrid algorithm dominates all the others we study. Finally, we describe how three popular tools to increase the efficiency of joins, namely filters, Babb arrays, and semijoins, can he grafted onto any of our algorithms.

Article Free Access Share on Optimization of extended database query languages Authors: Timos K. Sellis Department of Electrical Engineering and Computer, Science, Unversity of California, Berkeley, CA Department of Electrical Engineering and Computer, Science, Unversity of California, Berkeley, CAView Profile , Leonard Shapiro Department of Computer Science, North Dakota, State University, Fargo, ND Department of Computer Science, North Dakota, State University, Fargo, NDView Profile Authors Info & Claims SIGMOD '85: Proceedings of the 1985 ACM SIGMOD international conference on Management of dataMay 1985 Pages 424–436https://doi.org/10.1145/318898.318993Published:01 May 1985Publication History 13citation279DownloadsMetricsTotal Citations13Total Downloads279Last 12 Months15Last 6 weeks5 Get Citation AlertsNew Citation Alert added!This alert has been successfully added and will be sent to:You will be notified whenever a record that you have chosen has been cited.To manage your alert preferences, click on the button below.Manage my Alerts New Citation Alert!Please log in to your account Save to BinderSave to BinderCreate a New BinderNameCancelCreateExport CitationPublisher SiteeReaderPDF

With the availability of very large, relatively inexpensive main memories, it is becoming possible keep large databases resident in main memory In this paper we consider the changes necessary to permit a relational database system to take advantage of large amounts of main memory We evaluate AVL vs B+-tree access methods for main memory databases, hash-based query processing strategies vs sort-merge, and study recovery issues when most or all of the database fits in main memory As expected, B+-trees are the preferred storage mechanism unless more than 80--90% of the database fits in main memory A somewhat surprising result is that hash based query processing strategies are advantageous for large memory situations