Oracle SQL Recipes (eBook)

Grant Allen has worked in the IT field for over 20 years, as a CTO, enterprise architect, and database administrator. Grant's roles have covered private enterprise, academia and the government sector around the world, specialising in global-scale systems design, development, and performance. He is a frequent speaker at industry and academic conferences, on topics ranging from data mining to compliance, and technologies such as databases (DB2, Oracle, SQL Server, MySQL), content management, collaboration, disruptive innovation, and mobile ecosystems like Android. His first Android application was a task list to remind him to finish all his other unfinished Android projects. Grant works for Google, and in his spare time is completing a Ph.D on building innovative high-technology environments. Grant is the author of Beginning DB2, and lead author of Oracle SQL Recipes and The Definitive Guide to SQLite.

Contents at a Glance 5
Contents 7
About the Authors 18
Introduction 22
Foundations of Data Manipulation 25
The Basics 26
1-1. Retrieving Data from a Table 26
1-2. Selecting All Columns from a Table 28
1-3. Sorting Your Results 29
1-4. Adding Rows to a Table 30
1-5. Copying Rows from One Table to Another 32
1-6. Copying Data in Bulk from One Table to Another 33
1-7. Changing Values in a Row 33
1-8. Updating Multiple Fields with One Statement 34
1-9. Removing Unwanted Rows from a Table 35
1-10. Removing All Rows from a Table 36
1-11. Selecting from the Results of Another Query 37
1-12. Basing a Where Condition on a Query 38
1-13. Finding and Eliminating NULLs in Queries 39
1-14. Sorting as a Person Expects 41
1-15. Enabling Other Sorting and Comparison Options 43
1-16. Conditional Inserting or Updating Based on Existence 44
Summarizing and Aggregating Data 46
2-1. Summarizing the Values in a Column 46
2-2. Summarizing Data for Different Groups 49
2-3. Grouping Data by Multiple Fields 50
2-4. Ignoring Groups in Aggregate Data Sets 51
2-5. Aggregating Data at Multiple Levels 53
2-6. Using Aggregate Results in Other Queries 55
2-7. Counting Members in Groups and Sets 56
2-8. Finding Duplicates and Unique Values in a Table 58
2-9. Calculating Totals and Subtotals 60
2-10. Building Your Own Aggregate Function 62
2-11. Accessing Values from Subsequent or Preceding Rows 65
2-12. Assigning Ranking Values to Rows in a Query Result 68
2-13. Finding First and Last Values within a Group 70
2-14. Performing Aggregations over Moving Windows 72
2-15. Removing Duplicate Rows Based on a Subset of Columns 74
2-16. Finding Sequence Gaps in a Table 78
Querying from Multiple Tables 81
3-1. Joining Corresponding Rows from Two or More Tables 82
3-2. Stacking Query Results Vertically 84
3-3. Writing an Optional Join 86
3-4. Making a Join Optional in Both Directions 87
3-5. Removing Rows Based on Data in Other Tables 89
3-6. Finding Matched Data Across Tables 90
3-7. Joining on Aggregates 92
3-8. Finding Missing Rows 93
3-9. Finding Rows that Tables Do Not Have in Common 95
3-10. Generating Test Data 98
3-11. Updating Rows Based on Data in Other Tables 100
3-12. Manipulating and Comparing NULLs in Join Conditions 102
Creating and Deriving Data 104
4-1. Deriving New Columns 104
4-2. Returning Nonexistent Rows 108
4-3. Changing Rows into Columns 110
4-4. Pivoting on Multiple Columns 113
4-5. Changing Columns into Rows 116
4-6. Concatenating Data for Readability 118
4-7. Translating Strings to Numeric Equivalents 121
4-8. Generating Random Data 123
4-9. Creating a Comma-Separated Values File 126
Common Query Patterns 129
5-1. Changing Nulls into Real Values 129
5-2. Sorting on Null Values 132
5-3. Paginating Query Results 133
5-4. Testing for the Existence of Data 137
5-5. Conditional Branching In One SQL Statement 139
5-6. Conditional Sorting and Sorting By Function 140
5-7. Overcoming Issues and Errors when Subselects Return Unexpected Multiple Values 142
5-8. Converting Numbers Between Different Bases 144
5-9. Searching for a String Without Knowing the Column or Table 147
5-10. Predicting Data Values and Trends Beyond a Series End 150
5-11. Explicitly (Pessimistically) Locking Rows for an Update 153
5-12. Synchronizing the Contents of Two Tables 158
Data Types and Their Problems 161
Working with Date and Time Values 162
6-1. Converting Datetime Values into Readable Strings 162
6-2. Converting Strings to Datetime Values 164
6-3. Detecting Overlapping Date Ranges 165
6-4. Automatically Tracking Date and Time for Data Changes 167
6-5. Generating a Gapless Time Series from Data with Gaps 169
6-6. Converting Dates and Times Between Time Zones 171
6-7. Detecting Leap Years 173
6-8. Computing the Last Date in a Month 174
6-9. Determining the First Date or Day in a Month 175
6-10. Calculating the Day of the Week 176
6-11. Grouping and Aggregating by Time Periods 178
6-12. Finding the Difference Between Two Dates or Date Parts 179
6-13. Determining the Dates of Easter for Any Year 181
6-14. Calculating “X Day Active” Users for a Web Site 183
Strings 185
7-1. Searching for a Substring 185
7-2. Extracting a Substring 188
7-3. Single-Character String Substitutions 190
7-4. Searching for a Pattern 192
7-5. Extracting a Pattern 196
7-6. Counting Patterns 197
7-7. Replacing Text in a String 200
7-8. Speeding Up String Searches 202
Working with Numbers 205
8-1. Converting Between String and Numeric Data Types 205
8-2. Converting Between Numeric Data Types 206
8-3. Choosing Data Type Precision and Scale 208
8-4. Performing Calculations Correctly with Non-Numbers and Infinite Numbers 210
8-5. Validating Numbers in Strings 212
8-6. Generating Consecutive Numbers 214
8-7. Generating Numbers to a Formula or Pattern 216
8-8. Handling Nulls in Numeric Calculations 218
8-9. Automatically Rounding Numbers 220
8-10. Automatically Generating Lists of Numbers 222
Your Development Environment 224
Managing Transactions 225
9-1. Partially Rolling Back a Transaction 225
9-2. Identifying Blocking Transactions 229
9-3. Optimizing Row and Table Locking 230
9-4. Avoiding Deadlock Scenarios 232
9-5. Deferring Constraint Validation 234
9-6. Ensuring Read-Consistency Across a Transaction 241
9-7. Managing Transaction Isolation Levels 242
Data Dictionary 245
Graphical Tools vs. SQL 245
Data Dictionary Architecture 246
10-1. Displaying User Information 249
10-2. Determining the Tables You Can Access 251
10-3. Displaying a Table’s Disk Space Usage 253
10-4. Displaying Table Row Counts 256
10-5. Displaying Indexes for a Table 257
10-6. Showing Foreign Key Columns Not Indexed 258
10-7. Displaying Constraints 260
10-8. Showing Primary Key and Foreign Key Relationships 262
10-9. Displaying Object Dependencies 263
10-10. Displaying Synonym Metadata 266
10-11. Displaying View Text 267
10-12. Displaying Database Code 269
10-13. Displaying Granted Roles 270
10-14. Displaying Object Privileges 272
10-15. Displaying System Privileges 273
Common Reporting Problems 278
11-1. Avoiding Repeating Rows in Reports 278
11-2. Parameterizing a SQL Report 281
11-3. Returning Detail Columns in Grouped Results 284
11-4. Sorting Results into Equal-Size Buckets 286
11-5. Creating Report Histograms 288
11-6. Filtering Results by Relative Rank 290
11-7. Comparing Hypotheses on Sets of Data 292
11-8. Graphically Representing Data Distribution with Text 294
11-9. Producing Web-Page Reports Directly from the Database 295
Cleansing Data 301
12-1. Detecting Duplicate Rows 301
12-2. Removing Duplicate Rows 303
12-3. Determining if Data Can Be Loaded as Numeric 304
12-4. Determining if Data Can Be Loaded as a Date 305
12-5. Performing Case-Insensitive Queries 307
12-6. Obfuscating Values 308
12-7. Dropping All Indexes 311
12-8. Disabling Constraints 313
12-9. Disabling Triggers 318
12-10. Removing Data from a Table 319
12-11. Showing Differences in Schemas 321
Tree-Structured Data 326
13-1. Traversing Hierarchical Data from Top to Bottom 328
13-2. Sorting Nodes Within a Hierarchical Level 331
13-3. Generating Pathnames from Hierarchical Tables 334
13-4. Identifying Leaf Data in a Hierarchical Table 337
13-5. Detecting Cycles in Hierarchical Data 342
13-6. Generating a Fixed Number of Sequential Primary Keys 343
Working with XML Data 347
14-1. Translating SQL to XML 347
14-2. Storing XML in Native Form 351
14-3. Shredding XML for Relational Use 353
14-4. Extracting Key XML Elements from an XML Document 355
14-5. Generating Complex XML Documents 356
14-6. Validating XML Schema 358
14-7. Changing XML in Place 361
Partitioning 363
15-1. Determining if a Table Should be Partitioned 365
15-2. Partitioning by Range 366
15-3. Partitioning by List 367
15-4. Partitioning by Hash 368
15-5. Partitioning a Table in Multiple Ways 369
15-6. Creating Partitions on Demand 371
15-7. Partitioning by Referential Constraints 372
15-8. Partitioning on a Virtual Column 374
15-9. Application-Controlled Partitioning 375
15-10. Configuring Partitions with Tablespaces 376
15-11. Automatically Moving Updated Rows 377
15-12. Partitioning an Existing Table 378
15-13. Adding a Partition to a Partitioned Table 380
15-14. Exchanging a Partition with an Existing Table 381
15-15. Renaming a Partition 383
15-16. Splitting a Partition 384
15-17. Merging Partitions 385
15-18. Dropping a Partition 387
15-19. Removing Rows from a Partition 388
15-20. Generating Statistics for a Partition 389
15-21. Creating an Index that Maps to a Partition (Local Index) 389
15-22. Creating an Index with Its Own Partitioning Scheme ( Global Index) 392
LOBs 394
16-1. Loading Large Documents into CLOB Columns 395
16-2. Loading Image Data into BLOB Columns 398
16-3. Using SQL*Loader to Bulk-Load Large Objects 400
16-4. Accessing Large Objects Using HTTP 402
16-5. Making External Large Objects (BFILEs) Available to the Database 407
16-6. Deleting or Updating LOBs in a Database Table 409
Database Administration 413
17-1. Creating a Database 414
17-2. Dropping a Database 416
17-3. Verifying Connection Information 417
17-4. Creating Tablespaces 419
17-5. Dropping a Tablespace 421
17-6. Adjusting Tablespace Size 422
17-7. Limiting Database Resources per Session 423
17-8. Associating a Group of Privileges 426
17-9. Creating Users 429
17-10. Dropping Users 430
17-11. Modifying Passwords 431
17-12. Enforcing Password Complexity 432
Object Management 434
18-1. Creating a Table 434
18-2. Storing Data Temporarily 436
18-3. Moving a Table 438
18-4. Renaming Objects 439
18-5. Dropping a Table 442
18-6. Undropping a Table 443
18-7. Creating an Index 444
18-8. Creating a Function-Based Index 447
18-9. Creating a Bitmap Index 448
18-10. Creating an Index-Organized Table 449
18-11. Creating a View 450
18-12. Creating an Alternate Name for an Object 452
18-13. Enforcing Unique Rows in a Table 454
18-14. Ensuring Lookup Values Exist 457
18-15. Checking Data for a Condition 458
18-16. Creating a Connection Between Databases 460
18-17. Creating an Auto-incrementing Value 462
SQL Monitoring and Tuning 465
19-1. Monitoring Real-Time SQL Execution Statistics 465
19-2. Displaying a Query’s Progress in the Execution Plan 467
19-3. Determining How Much SQL Work Is Left 470
19-4. Identifying Resource-Intensive SQL Statements 471
19-5. Using Oracle Performance Reports to Identify Resource- Intensive SQL 473
19-6. Using the Operating System to Identify Resource- Intensive Queries 477
19-7. Displaying an Execution Plan Using AUTOTRACE 479
19-8. Generating an Execution Plan Using DBMS_XPLAN 482
19-9. Tracing All SQL Statements for a Session 484
19-10. Interpreting an Execution Plan 491
19-11. Obtaining SQL Tuning Advice 496
19-12. Forcing Your Own Execution Plan on a Query 498
19-13. Viewing Optimizer Statistics 500
19-14. Generating Statistics 502
Database Troubleshooting 505
20-1. Determining Causes of Database Problems 505
20-2. Displaying Open Cursors 509
20-3. Determining If Online Redo Logs Are Sized Properly 511
20-4. Determining If Undo Is Sized Properly 513
20-5. Determining If Temporary Tablespace Is Sized Correctly 515
20-6. Displaying Tablespace Fullness 517
20-7. Showing Object Sizes 519
20-8. Monitoring Index Usage 521
20-9. Auditing Object Usage 522
20-10. Auditing at a Granular Level 524
Index 527