An Introduction to Database Systems 8Ed - C J Date - Solutions Manual Episode 2 Part 4 ppsx

Copyright (c) 2003 C. J. Date page 17.8

SELECT STATS.OCCUPATION,

MAX ( STATS.SALARY ) AS MAXSAL,

MIN ( STATS.SALARY ) AS MINSAL

FROM STATS

GROUP BY STATS.OCCUPATION ;

GRANT SELECT ON JOBMAXMIN TO King ;

17.9

a. REVOKE SELECT ON STATS FROM Ford RESTRICT ;

b. REVOKE INSERT, DELETE ON STATS FROM Smith RESTRICT ;

c. REVOKE SELECT ON MINE FROM PUBLIC RESTRICT ;

d. REVOKE SELECT, UPDATE ( SALARY, TAX )

ON STATS FROM Nash RESTRICT ;

e. REVOKE SELECT ( NAME, SALARY, TAX ) ON STATS

FROM Todd RESTRICT ;

f. REVOKE SELECT ( NAME, SALARY, TAX ), UPDATE ( SALARY, TAX )

ON STATS FROM Ward RESTRICT ;

g. REVOKE ALL PRIVILEGES ON PREACHERS FROM Pope RESTRICT ;

h. REVOKE DELETE ON NONSPECIALIST FROM Jones RESTRICT ;

i. REVOKE SELECT ON JOBMAXMIN FROM King RESTRICT ;

*** End of Chapter 17 ***

Copyright (c) 2003 C. J. Date page 18.1

Chapter 18

O p t i m i z a t i o n

Principal Sections

• A motivating example

• An overview of query processing

• Expression transformation

• DB statistics

• A divide-and-conquer strategy

• Implementing the relational operators

General Remarks

No "SQL Facilities" section in this chapter. However, some SQL￾specific optimization issues are discussed in the annotation to

several of the references (especially references [18.37-18.43]).

Also, the summary section mentions the fact that 3VL, SQL's

(flawed) support for 3VL, and duplicate rows all serve as

optimization inhibitors (and the same is true for SQL's left-to￾right column ordering, though this last one isn't mentioned in the

chapter itself). The articles mentioned under reference [4.19]

are also relevant.

The material of this chapter is stuff that──in principle,

given a perfect system──the user really shouldn't need to know

about. It's part of the implementation, not part of the model.

However, just as a knowledge of what goes on under the hood can

help you be a better driver, a knowledge of what's involved in

executing queries might help you use the system better. In any

case, it's interesting stuff!──and it's a major part of relational

technology in general, though not part of the relational model per

se. The chapter really shouldn't be omitted, but it might be

downplayed a little (though it goes against the grain to say so).

There are two broad aspects to optimization: expression

transformation (aka "query rewrite") and access path selection

(using indexes and other storage structures appropriately to get

to the stored data). The relational model is directly relevant to

the first aspect, inasmuch as it's the formal properties of the

relational algebra that make expression transformation possible in

the first place. It's not so directly relevant to the second

aspect, except inasmuch as its clean logical vs. physical

separation is what permits so many different access paths to be

deployed (physical data independence). Commercial optimizers do a