Đang chuẩn bị liên kết để tải về tài liệu:
Joe Celko s SQL for Smarties - Advanced SQL Programming P42

Joe Celko s SQL for Smarties - Advanced SQL Programming P42. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 382 CHAPTER 18 VIEWS DERIVED TABLES MATERIALIZED TABLES AND TEMPORARY TABLES Then do a grouped select on it. This is correct SQL but it does not work in the old DB2. The compiler apparently tried to insert the view into the FROM clause as we have seen but when it expands it out the results are the same as those of the incorrect first query attempt with a function call in the GROUP BY clause. The trick is to force DB2 to materialize the view so that you can name the column constructed with the SUBSTRINGS function. Anything that causes a sort will do this the SELECT DISTINCT UNION GROUP BY and HAVING clauses for example. Since we know that the short identification number is a key we can use this view CREATE VIEW Shorty short_id amt1 amt2 . AS SELECT DISTINCT SUBSTRING long_id FROM 1 TO 6 amt1 amt2 . FROM TableA Then the report query is SELECT short_id SUM amt1 SUM amt2 . FROM Shorty GROUP BY short_id This works fine in DB2. I am indebted to Susan Vombrack of Loral Aerospace for this example. Incidentally this can be written in Standard SQL as SELECT FROM SELECT SUBSTRING long_id FROM 1 TO 6 AS short_id SUM amt1 SUM amt2 . FROM TableA GROUP BY long_id GROUP BY short_id The name on the substring result column in the subquery expression makes it recognizable to the parser. 18.4.4 Pointer Structures Finally the system can handle views with special data structures for the view. These structures are usually an array of pointers into a base table 18.5 WITH CHECK OPTION Clause 383 constructed from the view definition. This is a good way to handle updatable views in Standard SQL since the target row in the base table is at the end of a pointer chain in the view structure. Access will be as fast as possible. The pointer structure approach cannot easily use existing indexes on the base tables. But the pointer structure can be implemented as an index with restrictions. Furthermore multitable views can be constructed as pointer structures that allow direct access to the related .