Karen Morton, Kerry Osborne, Robyn Sands Riyaj Shamsudeen, and Jared Still

601 Pages·2010·6.39 MB·English

CYAN YELLOW
MAGENTA BLACK
PANTONE 123 C
BOOKS FOR PROFESSIONALS BY PROFESSIONALS® THE EXPERT’S VOICE® IN ORACLE
Companion
eBook Available
Karen Morton, Author of
Pro Oracle SQL
Expert Oracle Practices
Beginning Oracle SQL Pro
Pro Oracle SQL unlocks the power of SQL in the Oracle Database—one of the
most potent SQL implementations on the market today. To master it requires
a three-pronged approach: learn the language features, learn the supporting O
features that Oracle provides to help use the language effectively, and learn to
Kerry Osborne
think and work in sets.
Karen Morton and her team help you master powerful aspects of Oracle
SQL not found in competing databases. You’ll learn analytic functions, the r Pro
Robyn Sands, Author of MODEL clause, and advanced grouping syntax—features that will help in cre-
Expert Oracle Practices ating good queries for reporting and business intelligence applications. Pro a
Oracle SQL also helps you minimize parsing overhead, read execution plans,
test for correct results, and exert control over SQL execution in your database.
You’ll learn when to create indexes, how to verify that they make a difference, c Oracle SQL
Riyaj Shamsudeen, how to use SQL Profiles to optimize SQL in packaged applications, and much
Author of more. You’ll also understand how SQL is optimized for working in sets, and
Expert Oracle Practices that the key to getting accurate results lies in making sure that queries ask l
clear and precise questions.
e
• Endorsed by the OakTable Network, a group of Oracle technologists
Jared Still
well-known for their rigorous and scientific approach to Oracle Database

performance.
• Comprehensive – goes beyond the language with a focus on what you need S
to know to write successful queries and data manipulation statements.
Q
THE APRESS ROADMAP
Exploit the full power of SQL and
Beginning Pro Troubleshooting
Oracle SQL Oracle SQL Oracle Performance L
supporting features in Oracle Database
Companion eBook
Expert
Beginning Oracle PL/SQL Oracle Database
Oracle PL/SQL Recipes Architecture

See last page for details
on $10 eBook version
S
h
a Karen Morton, Kerry Osborne, Robyn Sands
SOURCE CODE ONLINE m O
www.apress.com ISBN 978-1-43025-43929298-5 SsudeSansborMort Riyaj Shamsudeen, and Jared Still
tedno
US $49.99 illnaen
Shelve in:
Databases / Oracle
User level: 9 781430 232285
Intermediate–Advanced
this print for content only—size & color not accurate 7.5 x 9.25 spine = 1.125" 600 page count

Pro Oracle SQL

(cid:1) (cid:1) (cid:1)
Karen Morton
Kerry Osborne
Robyn Sands
Riyaj Shamsudeen
Jared Still
Pro Oracle SQL
Copyright © 2010 by Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen,
and Jared Still
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN-13 (pbk): 978-1-4302-3228-5
ISBN-13 (electronic): 978-1-4302-3229-2
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names, logos, and images may appear in this book. Rather than use a trademark
symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and
images only in an editorial fashion and to the benefit of the trademark owner, with no intention of
infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if
they are not identified as such, is not to be taken as an expression of opinion as to whether or not
they are subject to proprietary rights.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewers: Christopher Beck, Iggy Fernandez, and Bernard Lopuz
Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell,
Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes,
Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic
Shakeshaft, Matt Wade, Tom Welsh
Coordinating Editor: Anita Castro
Copy Editor: Mary Behr
Compositor: Lynn L'Heureux
Indexer: Julie Grady
Artist: April Milne
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media, LLC.,
233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone],
e-mail [email protected], or visit www.springeronline.com.
For information on translations, please e-mail [email protected], or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional
use. eBook versions and licenses are also available for most titles. For more information, reference
our Special Bulk Sales–eBook Licensing web page at www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to be
caused directly or indirectly by the information contained in this work.
(cid:1)

Contents at a Glance
(cid:1)About the Authors................................................................................................................xv
(cid:1)About the Technical Reviewer...........................................................................................xvii
(cid:1)Introduction.........................................................................................................................xix
(cid:1)Chapter 1: Core SQL...............................................................................................................1
(cid:1)Chapter 2: SQL Execution....................................................................................................29
(cid:1)Chapter 3: Access and Join Methods..................................................................................61
(cid:1)Chapter 4: SQL is About Sets.............................................................................................105
(cid:1)Chapter 5: It’s About the Question....................................................................................129
(cid:1)Chapter 6: SQL Execution Plans........................................................................................153
(cid:1)Chapter 7: Advanced Grouping..........................................................................................191
(cid:1)Chapter 8: Analytic Functions...........................................................................................221
(cid:1)Chapter 9: The Model Clause.............................................................................................251
(cid:1)Chapter 10: Subquery Factoring........................................................................................283
(cid:1)Chapter 11: Semi-joins and Anti-joins..............................................................................325
(cid:1)Chapter 12: Indexes...........................................................................................................373
(cid:1)Chapter 13: Beyond the SELECT........................................................................................403
(cid:1)Chapter 14: Transaction Processing.................................................................................433
(cid:1)Chapter 15: Testing and Quality Assurance......................................................................465
(cid:1)Chapter 16: Plan Stability and Control..............................................................................497
(cid:1)Index...................................................................................................................................565

iii

(cid:1)

Contents
(cid:1)About the Authors................................................................................................................xv
(cid:1)About the Technical Reviewer...........................................................................................xvii
(cid:1)Introduction.........................................................................................................................xix
(cid:1)Chapter 1: Core SQL...............................................................................................................1
The SQL Language......................................................................................................................1
Interfacing to the Database.........................................................................................................2
Review of SQL*Plus.....................................................................................................................3
Connect to a Database............................................................................................................3
Configuring the SQL*Plus environment....................................................................................4
Executing Commands.............................................................................................................6
The Five Core SQL Statements....................................................................................................8
The SELECT Statement................................................................................................................9
The FROM Clause.................................................................................................................10
The WHERE Clause...............................................................................................................11
The GROUP BY Clause...........................................................................................................12
The HAVING Clause...............................................................................................................13
The SELECT List....................................................................................................................13
The ORDER BY Clause...........................................................................................................14
The INSERT Statement..............................................................................................................15
Single-table Inserts...............................................................................................................15
Multi-table Inserts.................................................................................................................16
The UPDATE Statement.............................................................................................................18
The DELETE Statement..............................................................................................................22
The MERGE Statement..............................................................................................................24
Summary..................................................................................................................................27
v
(cid:1)CONTENTS

(cid:1)Chapter 2: SQL Execution....................................................................................................29
Oracle Architecture Basics........................................................................................................29
SGA – The Shared Pool.............................................................................................................31
The Library Cache.....................................................................................................................32
Identical Statements..................................................................................................................33
SGA – The Buffer Cache............................................................................................................37
Query Transformation................................................................................................................39
View Merging............................................................................................................................40
Subquery Unnesting..................................................................................................................44
Predicate Pushing.....................................................................................................................47
Query Rewrite with Materialized Views......................................................................................50
Determining the Execution Plan.................................................................................................52
Executing the Plan and Fetching Rows......................................................................................56
SQL Execution – Putting It All Together......................................................................................59
Summary..................................................................................................................................60
(cid:1)Chapter 3: Access and Join Methods..................................................................................61
Full Scan Access Methods.........................................................................................................61
How Full Scan Operations are Chosen...................................................................................62
Full Scans and Throwaway....................................................................................................65
Full Scans and Multiblock Reads...........................................................................................66
Full Scans and the Highwater Mark.......................................................................................67
Index Scan Access Methods......................................................................................................73
Index Structure.....................................................................................................................74
Index Scan Types..................................................................................................................75
Index Unique Scan................................................................................................................78
Index range scan...................................................................................................................79
Index Full Scan.....................................................................................................................81
Index Skip Scan....................................................................................................................85
Index Fast Full Scan..............................................................................................................87
Join Methods............................................................................................................................88
Nested Loops Joins...............................................................................................................89
Sort-Merge Joins..................................................................................................................92
vi
(cid:1)CONTENTS

Hash Joins............................................................................................................................94
Cartesian Joins.....................................................................................................................96
Outer Joins...........................................................................................................................98
Summary................................................................................................................................104
(cid:1)Chapter 4: SQL is About Sets.............................................................................................105
Thinking in Sets......................................................................................................................105
Moving from Procedural to Set-based Thinking...................................................................106
Procedural vs Set-based Thinking: An Example...................................................................111
Set Operations........................................................................................................................113
UNION and UNION ALL........................................................................................................114
MINUS................................................................................................................................117
INTERSECT..........................................................................................................................119
Sets and Nulls.........................................................................................................................119
NULLs and Unintuitive Results.............................................................................................120
NULL Behavior in Set Operations.........................................................................................123
NULLs and GROUP BY and ORDER BY..................................................................................124
NULLs and Aggregate Functions..........................................................................................126
Summary................................................................................................................................127
(cid:1)Chapter 5: It’s About the Question....................................................................................129
Asking Good Questions............................................................................................................129
The Purpose of Questions........................................................................................................130
Categories of Questions...........................................................................................................130
Questions about the Question..................................................................................................133
Questions about Data..............................................................................................................135
Building Logical Expressions...................................................................................................141
Summary................................................................................................................................154
(cid:1)Chapter 6: SQL Execution Plans........................................................................................153
Explain Plans...........................................................................................................................153
Using Explain Plan...............................................................................................................153
Understanding How EXPLAIN PLAN can Miss the Mark........................................................160
Reading the Plan.................................................................................................................163
vii
(cid:1)CONTENTS

Execution Plans.......................................................................................................................166
Viewing Recently Generated SQL.........................................................................................166
Viewing the Associated Execution Plan................................................................................166
Collecting the Plan Statistics...............................................................................................168
Identifying SQL Statements for Later Plan Retrieval.............................................................171
Understanding DBMS_XPLAN in Detail................................................................................174
Using Plan Information for Solving Problems.......................................................................180
Summary................................................................................................................................189
(cid:1)Chapter 7: Advanced Grouping..........................................................................................191
Basic GROUP BY Usage...........................................................................................................192
HAVING Clause........................................................................................................................195
“New” GROUP BY Functionality...............................................................................................197
CUBE Extension to GROUP BY..................................................................................................197
Putting CUBE To Work.............................................................................................................201
Eliminate NULLs with the GROUPING() Function.......................................................................207
Extending Reports with GROUPING()........................................................................................209
Extending Reports With GROUPING_ID()...................................................................................210
GROUPING SETS and ROLLUP()................................................................................................214
GROUP BY Restrictions............................................................................................................217
Summary................................................................................................................................220
(cid:1)Chapter 8: Analytic Functions...........................................................................................221
Example Data..........................................................................................................................221
Anatomy of Analytic Functions.................................................................................................222
List of Functions......................................................................................................................223
Aggregation Functions.............................................................................................................224
Aggregate Function Over An Entire Partition........................................................................225
Granular Window Specifications..........................................................................................226
Default Window Specification..............................................................................................227
Lead and Lag..........................................................................................................................227
Syntax and Ordering...........................................................................................................227
Example 1: Returning a Value from Prior Row.....................................................................227
Understanding that Offset is in Rows...................................................................................228
Example 2: Returning a Value from an Upcoming Row........................................................229
viii