Transcript 幻灯片 1

Chapter 8
Views and Indexes
第8章 视图与索引
8.1 Virtual Views
 Views:
 “virtual relations”. Another class of SQL
relations that do not exist physically. They are
defined by an expression much like a query.
They can be queried as if they existed
physically, and in some cases, we can even
modify views.
 We can define several views in a database.
 Why we need views?
 Simplify computation.
 Different user concern about different
attributes in a relation.
 Safety.
8.1.1 Declaring Views
 How to declare views?
 CREATE VIEW [ owner .]view-name [( columnname , ... )]
AS select-without-order-by [with check option]
8.1.1 Declaring Views
 Example:
CREATE View orderamount As
select salesorder. orderno, signdate, empid, custid,
Sum(quantity) as qty, Sum(unitprice*quantity) as
amount
from dba.salesitem,dba.salesorder
where salesorder.orderno = salesitem.orderno
group by
salesorder.orderno,signdate,empid,custid;
 The result is a virtual relation (view):
orderamount(orderno, signdate, empid, custid,
qty, amount)
8.1.2 Querying Views
 The grammars of querying views and base
tables are same.
 Example:
SELECT * FROM orderamount;
 Example: Find the IDs and signing dates of
order forms with maximum total.
Select orderno, signdate, amount
From orderamount
Where amount = (Select Max (amount)
From orderamount);
8.1.2 Querying Views
 Example: Find the IDs, signing dates, salesmen’s
IDs and names of order forms with maximum
sales amount in October 2007.
Select orderno, signdate, salesman.empid, name
From orderamount natural join salesman
Where signdate between '2007-10-1' and '200710-31' AND amount =
( Select Max(amount) From orderamount
Where signdate between '2007-10-1' and '200710-31');
 Queries of views will be converted to those of
base tables by SQL system.
8.1.3 Renaming attributes
 We can give a view’s attributes new names.
 Example:
CREATE View order(no, orderdate, salesman,
customer, amount) As
Select salesorder. orderno, signdate, empid,
custid,Sum(unitprice*quantity) as amount
From dba.salesitem,dba.salesorder
Where salesorder.orderno = salesitem.orderno
Group by salesorder.orderno, signdate, empid,
custid;
The result is a virtual table: order(no,
orderdate, salesman, customer, amount)
8.2 Modifying Views
 How to modify a view declaration?
 ALTER VIEW [ owner .]view-name [( columnname , ... )]
AS select-without-order-by [with check option];
 How to delete a view declaration?
 DROP VIEW [owner.]view-name;
8.2 Modifying Views
 Which views can be updated?
 From a single table.
 The attributes in the SELECT clause must
include all attributes with NOT NULL.
 No GROUP BY clause.
 No aggregate.
 No UNION.
 No DISTINCT.
 The WHERE clause must no involve R in a
subquery. (no correlated subquery)
 Modifications of views will be translated to
equivalent modifications on base tables by SQL
system.
8.2 Modifying Views
 When a trigger is defined on a view, we can use
INSTEAD OF in place of BEFORE or AFTER.
 When an event awakens the trigger, the action
of the trigger is done instead of the event itself.
8.4 Selection of Indexes
 Index
 data structure used to speed access to
tuples of a relation, given values of one
or more attributes.
 Could be a hash table, but in a DBMS it is
always a balanced search tree with giant
nodes (a full disk page) called a B-tree.
8.4 Selection of Indexes
 No standard!
 Typical syntax:
 CREATE INDEX SalesmanInd ON
Salesman(name);
 CREATE INDEX SalesorderInd ON
Salesorder(signdate, empid);
 Given a value v, the index takes us to only
those tuples that have v in the attribute(s) of
the index.
8.4 Selection of Indexes
 Example: Use SalesmanInd and SalesorderInd to
find the order No. of order forms sold by
‘zhangsan’ in Oct. 2009.
SELECT orderno FROM salesman, salesorder
WHERE name = ‘zhangsan’ AND salesorder.empid =
salesman.empid AND signdate between ‘2009-10-1’
and ‘2009-10-31’;
 Use SalesmanInd to get all the salesmen named
‘Zhangsan’.
 Then use SalesorderInd to get No's of those
salesmen, with signing date is in Oct. 2009.
8.4 Selection of Indexes
 A major problem in making a database run fast
is deciding which indexes to create.
 Pro: An index speeds up queries that can use it.
 Con: An index slows down all modifications on
its relation because the index must be modified
too.
8.4 Selection of Indexes
Suppose the only things we did with our sales
database was:
 Insert new facts into a relation (10%).
 Find the order form of a given salesman’s
ID in a given date (90%).
 Then SalesorderInd on Salesorder(signdate,
empid) would be wonderful, but SalesmanInd
on Salesman(name) would be harmful.

8.4 Selection of Indexes
A major research thrust.
 Because hand tuning is so hard.
 An advisor gets a query load, e.g.:
 Choose random queries from the history of
queries run on the database, or
 Designer provides a sample workload.

8.4 Selection of Indexes
 The advisor generates candidate indexes and
evaluates each on the workload.
 Feed each sample query to the query
optimizer, which assumes only this one index
is available.
 Measure the improvement/degradation in
the average running time of the queries.