Advanced SQL Database Programming - 2003.pdf

(675 KB) Pobierz
Advanced SQL Database Programmer
Handbook
Donald K. Burleson
Joe Celko
John Paul Cook
Peter Gulutzan
DBAzine.com &
BMC Software Inc.
Brought to you by
Advanced SQL Database Programmers
Handbook
By Donald K. Burleson, Joe Celko, John Paul Cook, and
Peter Gulutzan
Copyright © 2003 by BMC Software and DBAzine. Used with permission.
Printed in the United States of America.
Series Editor:
Donald K. Burleson
Production Manager:
John Lavender
Production Editor:
Teri Wade
Cover Design:
Bryan Hoff
Printing History:
August, 2003 for First Edition
Oracle, Oracle7, Oracle8, Oracle8i and Oracle9i are trademarks of Oracle Corporation.
Oracle In-Focus
is a registered Trademark of Rampant TechPress.
Many of the designations used by computer vendors to distinguish their products are
claimed as Trademarks. All names known to Rampant TechPress to be trademark names
appear in this text as initial caps.
The information provided by the authors of this work is believed to be accurate and
reliable, but because of the possibility of human error by our authors and staff, BMC
Software, DBAZine and Rampant TechPress cannot guarantee the accuracy or
completeness of any information included in this work and is not responsible for any
errors, omissions or inaccurate results obtained from the use of information or scripts in
this work.
Links to external sites are subject to change; DBAZine.com, BMC Software and
Rampant TechPress do not control or endorse the content of these external web sites,
and are not responsible for their content.
ISBN 0-9744355-2-X
DBAzine.com
BMC.com/oracle
iii
Table of Contents
Conventions Used in this Book ....................................................vii
About the Authors ...........................................................................ix
Foreword.............................................................................................x
Chapter 1 - SQL as a Second Language................................. 1
Thinking in SQL by Joe Celko ........................................................1
Chapter 2 - SQL View Internals.............................................7
SQL Views Transformed by Peter Gulutzan ................................7
Syntax ..................................................................................................7
Cheerful Little Fact #1:................................................................8
Cheerful Little Fact #2:................................................................8
View Merge.........................................................................................9
Table1 .......................................................................................... 10
The Small Problem with View Merge ......................................... 13
Temporary Tables........................................................................... 14
Permanent Materialized Views ..................................................... 15
UNION ALL Views ...................................................................... 18
Alternatives to Views ..................................................................... 19
Tips ................................................................................................... 21
References........................................................................................ 21
Chapter 3 - SQL JOIN ......................................................... 25
Relational Division by Joe Celko ................................................. 25
Chapter 4 - SQL UNION..................................................... 31
Set Operations by Joe Celko......................................................... 31
Introduction..................................................................................... 31
Set Operations: Union ................................................................... 32
Chapter 5 - SQL NULL ....................................................... 37
Selection by Joe Celko ................................................................... 37
Introduction..................................................................................... 37
iv
DBAzine.com
BMC.com/oracle
The Null of It All............................................................................ 37
Defining a Three-valued Logic..................................................... 39
Wonder Shorthands ....................................................................... 40
Chapter 6 - Specifying Time ................................................ 41
Killing Time by Joe Celko ............................................................. 41
Timing is Everything...................................................................... 41
Specifying "Lawful Time" ............................................................. 43
Avoid Headaches with Preventive Maintenance ....................... 44
Chapter 7 - SQL TIMESTAMP datatype ............................ 45
Keeping Time by Joe Celko .......................................................... 45
Chapter 8 - Internals of the IDENTITY datatype Column. 49
The Ghost of Sequential Processing by Joe Celko.................... 49
Early SQL and Contiguous Storage............................................. 49
IDENTITY Crisis .......................................................................... 50
Chapter 9 - Keyword Search Queries ................................... 53
Keyword Searches by Joe Celko................................................... 53
Chapter 10 - The Cost of Calculated Columns..................... 57
Calculated Columns by Joe Celko................................................ 57
Introduction..................................................................................... 57
Triggers ........................................................................................ 58
INSERT INTO Statement ....................................................... 60
UPDATE the Table................................................................... 61
Use a VIEW................................................................................ 61
Chapter 11 - Graphs in SQL ................................................. 63
Path Finder by Joe Celko .............................................................. 63
Chapter 12 - Finding the Gap in a Range ............................ 69
Filling in the Gaps by Joe Celko .................................................. 69
Chapter 13 - SQL and the Web ............................................ 75
Web Databases by Joe Celko........................................................ 75
DBAzine.com
BMC.com/oracle
v

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin