Query Execution

Abstract

Previous chapters gave us data structures that help support basic database operations such as rinding tuples given a search key. We are now ready to use these structures to support efficient algorithms for answering queries. The broad topic of query processing will be covered in this chapter and Chapter 7. The query processor is the group of components of a DBMS that turns user queries and data-modification commands into a sequence of operations on the database and executes those operations. Since SQL lets us express queries at a very high level, the query processor must supply a lot of detail regarding how the query is to be executed. Moreover, a naive execution strategy for a query may lead to an algorithm for executing the query that takes far more time than necessary. Figure 6.1 suggests the division of topics between Chapters 6 and 7. In this chapter, we concentrate on query execution, that is, the algorithms that manipulate the data of the database. We shall begin with a review of rela-tional algebra. This notation, or something similar, is used by most relational database systems to represent internally the queries that the user expresses in SQL. This algebra involves operations on relations, such as join and union, with which you may already be familiar. However, SQL uses a bag (multiset) model of data, rather than a set model. Also, there are operations in SQL, such as aggregation, grouping, and ordering (sorting), that are not part of the classical relational algebra. Thus, we need to reconsider this algebra in the light of its role as a representation for SQL queries. One advantage of using relational algebra is that it makes alternative forms of a query easy to explore. The different algebraic expressions for a query are called logical query plans. Often, these plans are represented as expression trees, as we shall do in this book. This chapter catalogs the principal methods for execution of the operations of relational algebra. These methods differ in their basic strategy; scanning, hashing, sorting, and indexing are the major approaches. The methods also differ on their assumption as to the amount of available main memory. Some 237 238 CHAPTER 6. QUERY EXECUTION Figure 6.1: The major parts of the query processor algorithms assume that enough main memory is available to hold at least one of the relations involved in an operation. Others assume that the arguments of the operation are too big to fit in memory, and these algorithms have significantly different costs and structures. Preview of Query Compilation Query compilation can be divided into three major steps, as sketched in Fig. 6.2. a) Parsing, in which a parse tree, representing the query arid its structure, is constructed. b) Query rewrite, in which the parse tree is converted to an initial query plan, which is usually an algebraic representation of the query. This initial plan is then transformed into an equivalent plan that is expected to require less time to execute. c) Physical plan generation, where the abstract query plan from (b), often called a logical query plan, is turned into a physical query plan by selecting algorithms to implement each of the operators of the logical plan, and by selecting an order of execution for these operators. The physical plan, like the result of parsing and the logical plan, is represented by an expression tree. The physical plan also includes details such as how the queried relations are accessed, and when and if a relation should be sorted.