The art of SQL

369 Pages·2006·9.36 MB·English

by  Stephane Faroult

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: [phone] 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