数据库系统 view(8)

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 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.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.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
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.
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.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.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.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
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’;
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)
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.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.
8.1 Virtual Views
Exercise: P344 8.1.1
8.2 Modifying Views