Joe Celko s SQL for Smarties - Advanced SQL Programming P43. 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. | 392 CHAPTER 18 VIEWS DERIVED TABLES MATERIALIZED TABLES AND TEMPORARY TABLES user has to use what he is given. However you should know how to use each structure and which one is best for which situation. Using VIEWs Do not nest views too deeply the overhead of building several levels eats up execution time and the extra storage for materialized views can be expensive. Complex nesting is also hard to maintain. One way to figure out what views you should have is to inspect the existing queries for repeated subqueries or expressions. These are good candidates for views. One of the major uses of views is security. The DBA can choose to hide certain columns from certain classes of users through a combination of security authorizations and views. Standard SQL has provisions for restricting access to tables at the column level but most implementations do not have that feature yet. Another security trick is to add a column with a special user or security-level identifier to a table. The view hides this column and displays to the user only what he is supposed to see. One possible problem is that a user could try to change something in the VIEW that violates other table constraints when his attempt returns an error message he gets some information about the security system that we would rather have hidden from him. The best way to approach views is to think of how a user wants to see the database and then give him a set of views that make it look as if the database had been designed just for his applications. Using TEMPORARY TABLEs The global temporary table can be used to pass data among users which is something that a VIEW cannot do. The LOCAL TEMPORARY table has two major advantages. The user can load it with the results of a complex time-consuming query once and use that result set over and over in his session greatly improving performance. This also prevents the system from locking out other users from the base tables from which the complex query was .
