Database Reference
In-Depth Information
increase the salary of all employees based in Seattle by 10%, we can use the
following UPDATE query:
UPDATE Emp
SET Salary = 1.1 * Salary
WHERE Emp.DId IN ( SELECT Dept.DId FROM Dept
WHERE Dept.City = 'Seattle' )
UPDATE queries are defined by the updated table, the set of assignment
operations, and a predicate that constrains which rows in the table to actually
modify. Similarly, DELETE queries are specified by a table and a predicate that
identifies which rows to delete, and INSERT queries are specified by a table and
either an explicit set of tuples or another SQL query that returns the tuples
to insert.
1.3 Processing SQL Queries
We now briefly explain how SQL queries are actually evaluated in a database
system. Our exposition is necessarily high level and omits many details and
special cases. The objective of this section is to introduce the reader to con-
cepts that are needed in later chapters—mostly by example—but it is by no
means comprehensive. References at the end of this chapter provide many
additional details.
In a database system, tables are stored on disk as a sequence of pages, which
typically range from 4 KB to 64 KB in size. Tuples in a table are laid out in
pages sequentially, using a variety of encodings that balance tuple size and
processing eciency. Each tuple in a table is given a record id (or RID for
short), which identifies both the page that contains the tuple and the offset
inside the page. Pages are the unit of input/output (I/O) transfer and can be
cached in memory (in what is called a buffer pool).
The execution environment in a relational database system can be seen as
a virtual machine. Modern query engines provide a layer of abstraction on
top of several operating system services, such as memory management, I/O
subsystems, and schedulers, and expose a runtime environment to eciently
evaluate SQL queries. A query optimizer (which is the subject of Chapter 2)
takes an input SQL query and transforms it into a program to be executed (also
called an execution plan ) on this virtual machine. An execution plan is typi-
cally a tree of physical operators that precisely determine how a query is to be
evaluated. Physical operators take sequences of tuples as inputs and produce
an output sequence of tuples (the output of the root operator in the tree is the
result of the query). Physical operators are usually designed using the iterator
model, which allows operators to consume their inputs incrementally, without
necessarily materializing intermediate results produced by other operators.
Search WWH ::




Custom Search