Table Of Content

The Art of SQL While heeding the profit of my counsel, avail yourself also of any helpful circumstances over and beyond the ordinary rules. —Sun Tzu,The Art of War Other resources from O’Reilly Related titles SQL in a Nutshell SQL Pocket Guide SQL Tuning SQL Cookbook™ oreilly.com oreilly.com is more than a complete catalog of O’Reilly books. You’ll also find links to news, events, articles, weblogs, sample chapters, and code examples. oreillynet.comistheessentialportalfordevelopersinterested in open and emerging technologies, including new plat- forms, programming languages, and operating systems. Conferences O’Reilly brings diverse innovators together to nurture the ideas that spark revolutionary industries. We specialize in documenting the latest tools and systems, translating the innovator’s knowledge into useful skills for those in the trenches.Visitconferences.oreilly.comforourupcomingevents. Safari Bookshelf (safari.oreilly.com) is the premier online reference library for programmers and IT professionals. Conduct searches across more than 1,000 books. Sub- scribers can zero in on answers to time-critical questions inamatterofseconds.ReadthebooksonyourBookshelf from cover to cover or simply flip to the page you need. Try it today for free. The Art of SQL Stéphane Faroult with Peter Robson Beijing • Cambridge • Farnham • Köln • Paris • Sebastopol • Taipei • Tokyo The Art of SQL by Stéphane Faroult with Peter Robson Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc. 1005 Gravenstein Highway North, Sebastopol, CA 95472 O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (safari.oreilly.com). For more information, contact our corporate/institutional sales department: (800)998-9938 or [email protected]. Editor: Jonathan Gennick Cover Designer: Mike Kohnke Production Editors: Jamie Peppard and Interior Designer: Marcia Friedman Marlowe Shaeffer Illustrators: Robert Romano, Jessamyn Read, Copyeditor: Nancy Reinhardt and Lesley Borash Indexer: Ellen Troutman Zaig Printing History: March 2006: First Edition. The O’Reilly logo is a registered trademark of O’ReillyMedia, Inc.The Art of SQL and related trade dress are trademarks of O’Reilly Media, Inc. Many of the designations used by manufacturers and sellerstodistinguishtheirproductsareclaimedastrademarks.Wherethosedesignationsappearin this book, and O’Reilly Media, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps. While every precaution has been taken in the preparation of this book, the publisher and authors assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein. This book uses RepKover™, a durable and flexible lay-flat binding. ISBN-10: 0-596-00894-5 ISBN-13: 978-0-596-00894-9 [M] [10/06] The French humorist Alphonse Allais (1854–1905), once dedicated one of his short stories as follows: To the only woman I love and who knows it well. . . . with the following footnote: This is a very convenient dedication that I cannot recommend too warmly to my fellow writers. It costs nothing, and can, all at once, please five or six persons. I can take a piece of wise advice when I meet one. STÉPHANEFAROULT C O N T E N T S Preface ix 1 Laying Plans 1 Designing Databases for Performance 2 Waging War 27 Accessing Databases Efficiently 3 Tactical Dispositions 55 Indexing 4 Maneuvering 75 Thinking SQL Statements 5 Terrain 105 Understanding Physical Implementation 6 The Nine Situations 127 Recognizing Classic SQL Patterns 7 Variations in Tactics 167 Dealing with Hierarchical Data 8 Weaknesses and Strengths 199 Recognizing and Handling Difficult Cases 9 Multiple Fronts 225 Tackling Concurrency 10 Assembly of Forces 247 Coping with Large Volumes of Data 11 Stratagems 279 Trying to Salvage Response Times 12 Employment of Spies 307 Monitoring Performance Photo Credits 333 Index 335