Pro Oracle SQL

Prelim......Page 1
Contents at a Glance......Page 5
Contents......Page 7
About the Authors......Page 17
About the Technical Reviewers......Page 19
Acknowledgments......Page 21
The SQL Language......Page 23
Interfacing to the Database......Page 24
Connect to a Database......Page 25
Configuring the SQL*Plus environment......Page 26
Executing Commands......Page 28
The Five Core SQL Statements......Page 30
The SELECT Statement......Page 31
The FROM Clause......Page 32
The WHERE Clause......Page 33
The GROUP BY Clause......Page 34
The SELECT List......Page 35
The ORDER BY Clause......Page 36
Single-table Inserts......Page 37
Multi-table Inserts......Page 38
The UPDATE Statement......Page 40
The DELETE Statement......Page 44
The MERGE Statement......Page 46
Summary......Page 49
Oracle Architecture Basics......Page 51
SGA – The Shared Pool......Page 53
The Library Cache......Page 54
Identical Statements......Page 55
SGA – The Buffer Cache......Page 59
Query Transformation......Page 61
View Merging......Page 62
Subquery Unnesting......Page 66
Predicate Pushing......Page 69
Query Rewrite with Materialized Views......Page 72
Determining the Execution Plan......Page 74
Executing the Plan and Fetching Rows......Page 78
SQL Execution – Putting It All Together......Page 81
Summary......Page 82
Full Scan Access Methods......Page 83
How Full Scan Operations are Chosen......Page 84
Full Scans and Throwaway......Page 87
Full Scans and Multiblock Reads......Page 88
Full Scans and the Highwater Mark......Page 89
Index Scan Access Methods......Page 95
Index Structure......Page 96
Index Scan Types......Page 97
Index Unique Scan......Page 100
Index range scan......Page 101
Index Full Scan......Page 103
Index Skip Scan......Page 107
Index Fast Full Scan......Page 109
Join Methods......Page 110
Nested Loops Joins......Page 111
Sort-Merge Joins......Page 114
Hash Joins......Page 116
Cartesian Joins......Page 118
Outer Joins......Page 120
Summary......Page 126
Thinking in Sets......Page 127
Moving from Procedural to Set-based Thinking......Page 128
Procedural vs Set-based Thinking: An Example......Page 133
Set Operations......Page 135
UNION and UNION ALL......Page 136
MINUS......Page 139
Sets and Nulls......Page 141
NULLs and Unintuitive Results......Page 142
NULL Behavior in Set Operations......Page 145
NULLs and GROUP BY and ORDER BY......Page 146
NULLs and Aggregate Functions......Page 148
Summary......Page 149
Asking Good Questions......Page 151
Unknown......Page 0
Categories of Questions......Page 152
Questions about the Question......Page 154
Questions about Data......Page 156
Building Logical Expressions......Page 162
Summary......Page 174
Using Explain Plan......Page 175
Understanding How EXPLAIN PLAN can Miss the Mark......Page 182
Reading the Plan......Page 185
Viewing the Associated Execution Plan......Page 188
Collecting the Plan Statistics......Page 190
Identifying SQL Statements for Later Plan Retrieval......Page 193
Understanding DBMS_XPLAN in Detail......Page 196
Using Plan Information for Solving Problems......Page 202
Summary......Page 211
Advanced Grouping......Page 213
Basic GROUP BY Usage......Page 214
HAVING Clause......Page 217
CUBE Extension to GROUP BY......Page 219
Putting CUBE To Work......Page 223
Eliminate NULLs with the GROUPING() Function......Page 229
Extending Reports with GROUPING()......Page 231
Extending Reports With GROUPING_ID()......Page 232
GROUPING SETS and ROLLUP()......Page 236
GROUP BY Restrictions......Page 239
Summary......Page 242
Example Data......Page 243
Anatomy of Analytic Functions......Page 244
List of Functions......Page 245
Aggregation Functions......Page 246
Aggregate Function Over An Entire Partition......Page 247
Granular Window Specifications......Page 248
Example 1: Returning a Value from Prior Row......Page 249
Understanding that Offset is in Rows......Page 250
Example 2: Returning a Value from an Upcoming Row......Page 251
First_value & Last_value......Page 252
Example: Last_value to Calculate Minimum......Page 253
Nth_value (11gR2)......Page 254
Rank......Page 256
Dense_rank......Page 257
Row_number......Page 258
Ratio_to_report......Page 259
Percentile_cont......Page 260
Percentile_disc......Page 262
NTILE......Page 263
Stddev......Page 264
Performance Tuning......Page 265
Predicates......Page 266
Advanced topics......Page 268
Dynamic SQL......Page 269
Nesting Analytic Functions......Page 270
Parallelism......Page 271
Summary......Page 272
Spreadsheets......Page 273
Example Data......Page 274
Anatomy of a Model Clause......Page 275
Rules......Page 276
Positional and Symbolic References......Page 277
Positional Notation......Page 278
Symbolic Notation......Page 279
Returning Updated Rows......Page 280
Row Evaluation Order......Page 282
Rule Evaluation Order......Page 284
Aggregation......Page 285
Iteration......Page 286
An Example......Page 287
PRESENTV and NULLs......Page 288
Lookup Tables......Page 289
NULLs......Page 291
ACYCLIC......Page 293
CYCLIC......Page 294
Sequential......Page 295
Predicate Pushing......Page 296
Materialized Views......Page 298
Parallelism......Page 299
Partitioning in Model Clause Execution......Page 300
Indexes......Page 302
Subquery Factoring......Page 303
Summary......Page 304
Standard Usage......Page 305
Testing Execution Plans......Page 308
Testing Over Multiple Executions......Page 312
Testing the Effects of Query Changes......Page 315
Seizing Other Optimization Opportunities......Page 318
Applying Subquery Factoring to PL/SQL......Page 323
A CONNECT BY Example......Page 326
The Example Using an RSF......Page 328
Restrictions on RSF......Page 329
Differences from CONNECT BY......Page 330
The LEVEL Pseudocolumn......Page 331
The SYS_CONNECT_BY_PATH Function......Page 333
The CONNECT_BY_ROOT Operator......Page 335
The CONNECT_BY_ISCYCLE Pseudocolumn and NOCYCLE Parameter......Page 338
The CONNECT_BY_ISLEAF Pseudocolumn......Page 341
Summary......Page 346
Semi-joins......Page 347
Semi-join Plans......Page 356
Controlling Semi-join Plans Using Hints......Page 361
Controlling Semi-join Plans at the Instance Level......Page 364
Semi-join Restrictions......Page 367
Anti-joins......Page 369
Anti-join Plans......Page 375
Controlling Anti-join Plans Using Hints......Page 385
Controlling Anti-join Plans at the Instance Level......Page 386
Anti-join Restrictions......Page 389
Summary......Page 393
Indexes......Page 395
When to use Indexes......Page 396
Choice of Columns......Page 398
The Null Issue......Page 400
B-tree indexes......Page 401
Bitmap Indexes......Page 402
Index Organized Tables......Page 403
Partitioned Indexes......Page 405
Local Indexes......Page 406
Global Indexes......Page 407
Hash Partitioning vs. Range Partitioning......Page 408
Compressed Indexes......Page 411
Function Based Indexes......Page 413
Reverse Key Indexes......Page 416
Descending Indexes......Page 417
Invisible Indexes......Page 418
Virtual Indexes......Page 420
Bitmap Join Indexes......Page 421
Summary......Page 423
Direct Path Inserts......Page 425
Multi-Table Inserts......Page 428
Conditional Insert......Page 429
DML Error Logging......Page 430
UPDATE......Page 437
DELETE......Page 443
Syntax and Usage......Page 447
Performance Comparison......Page 451
Summary......Page 453
What is a Transaction?......Page 455
ACID Properties of a Transaction......Page 456
Transaction Isolation Levels......Page 457
Multi-Version Read Consistency......Page 459
Set Transaction......Page 460
Grouping Operations into Transactions......Page 461
The Order Entry Schema......Page 462
The Active Transaction......Page 469
Using Savepoints......Page 471
Serializing Transactions......Page 474
Isolating Transactions......Page 477
Autonomous Transactions......Page 480
Summary......Page 485
Testing and Quality Assurance......Page 487
Test Cases......Page 488
Testing Methods......Page 489
Unit Tests......Page 490
Schema Changes......Page 494
Repeating the Unit Tests......Page 498
Execution Plan Comparison......Page 500
Adding Instrumentation to Code......Page 506
Testing for Performance......Page 510
Testing to Destruction......Page 512
Troubleshooting through Instrumentation......Page 513
Summary......Page 517
Plan Instability: Understanding The Problem......Page 519
Changes to Statistics......Page 520
Changes to the Environment......Page 522
Bind Variable Peeking......Page 524
Capturing Data on Currently-Running Queries......Page 527
Reviewing the History of a Statement’s Performance......Page 528
Aggregating Statistics by Plan......Page 530
Looking for Statistical Variance by Plan......Page 531
Checking for Variations Around a Point in Time......Page 533
Modifying Query Structure......Page 535
Giving the Optimizer some Hints......Page 536
Plan Control: Without Access to the Code......Page 544
Option 1: Change the Statistics......Page 545
Option 3: Add or Remove Access Paths......Page 547
Outlines......Page 548
SQL Profiles......Page 560
SQL Plan Baselines......Page 577
Conclusion......Page 584
A......Page 587
D......Page 588
F......Page 589
H......Page 590
M......Page 591
N......Page 592
P......Page 593
R......Page 594
S......Page 595
T......Page 596
Y, Z......Page 597