SUMMARY

SEARCH

Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.

BOOK ORACLE SQL RECIPES

SUMMARY

288

PART 1 ? ? ? Foundation of Data Manipulation 1
?Chapter 1: The Basics 3
?Chapter 2: Summarizing and Aggregating Data 23
?Chapter 3: Querying from Multiple Tables 59
?Chapter 4: Creating and Deriving Data . 83
?Chapter 5: Common Query Patterns 109
PART 2 ? ? ? Data Types and Their Problems . 141
?Chapter 6: Working with Date and Time Values 143
?Chapter 7: Strings . 167
?Chapter 8: Working with Numbers 187
PART3 ? ? ? Your Development Environment . 207
?Chapter 9: Managing Transactions 209
?Chapter 10: Data Dictionary . 229
PART 4 ? ? ? Data Dictionary 261
?Chapter 11: Common Reporting Problems 263
?Chapter 12: Cleansing Data . 287
?Chapter 13: Tree-Structured Data 313
?Chapter 14: Working with XML Data 335
?Chapter 15: Partitioning 351
?Chapter 16: LOBs 383
PART 5 ? ? ? Administration 401
?Chapter 17: Database Administration . 403
?Chapter 18: Object Mamagement . 425
?Chapter 19: SQL Monitoring and Tuning 457
?Chapter 20: Database Troubleshooting497
?Index 519
PART 1 Foundation of Data Manipulation 1
Chapter 1: The Basics 3
1-1. Retrieving Data from a Table 3
1-2. Selecting All Columns from a Table 5
1-3. Sorting Your Results 6
1-4. Adding Rows to a Table . 7
1-5. Copying Rows from One Table to Another 9
1-6. Copying Data in Bulk from One Table to Another 10
1-7. Changing Values in a Row . 10
1-8. Updating Multiple Fields with One Statement . 11
1-9. Removing Unwanted Rows from a Table 12
1-10. Removing All Rows from a Table 13
1-11. Selecting from the Results of Another Query 14
1-12. Basing a Where Condition on a Query . 15
1-13. Finding and Eliminating NULLs in Queries . 16
1-14. Sorting as a Person Expects . 18
1-15. Enabling Other Sorting and Comparison Options 20
1-16. Conditional Inserting or Updating Based on Existence . 21
Chapter 2: Summarizing and Aggregating Data 23
2-1. Summarizing the Values in a Column 23
2-2. Summarizing Data for Different Groups . 26
2-3. Grouping Data by Multiple Fields 27
2-4. Ignoring Groups in Aggregate Data Sets 28
2-5. Aggregating Data at Multiple Levels 30
2-6. Using Aggregate Results in Other Queries 32
2-7. Counting Members in Groups and Sets 33
2-8. Finding Duplicates and Unique Values in a Table 35
2-9. Calculating Totals and Subtotals 37
2-10. Building Your Own Aggregate Function 39
2-11. Accessing Values from Subsequent or Preceding Rows 42
2-12. Assigning Ranking Values to Rows in a Query Result . 45
2-13. Finding First and Last Values within a Group 47
2-14. Performing Aggregations over Moving Windows 49
2-15. Removing Duplicate Rows Based on a Subset of Columns 51
2-16. Finding Sequence Gaps in a Table 55
Chapter 3: Querying from Multiple Tables 59
3-1. Joining Corresponding Rows from Two or More Tables . 60
3-2. Stacking Query Results Vertically 62
3-3. Writing an Optional Join . 64
3-4. Making a Join Optional in Both Directions 65
3-5. Removing Rows Based on Data in Other Tables 67
3-6. Finding Matched Data Across Tables 68
3-7. Joining on Aggregates . 70
3-8. Finding Missing Rows . 71
3-9. Finding Rows that Tables Do Not Have in Common . 73
3-10. Generating Test Data 76
3-11. Updating Rows Based on Data in Other Tables 78
3-12. Manipulating and Comparing NULLs in Join Conditions . 80
Chapter 4: Creating and Deriving Data . 83
4-1. Deriving New Columns 83
4-2. Returning Nonexistent Rows 87
4-3. Changing Rows into Columns 89
4-4. Pivoting on Multiple Columns 92
4-5. Changing Columns into Rows 95
4-6. Concatenating Data for Readability 97
4-7. Translating Strings to Numeric Equivalents 100
4-8. Generating Random Data 102
4-9. Creating a Comma-Separated Values File 105
Chapter 5: Common Query Patterns 109
5-1. Changing Nulls into Real Values 109
5-2. Sorting on Null Values 112
5-3. Paginating Query Results 113
5-4. Testing for the Existence of Data 117
5-5. Conditional Branching In One SQL Statement 119
5-6. Conditional Sorting and Sorting By Function . 120
5-7. Overcoming Issues and Errors when Subselects
Return Unexpected Multiple Values 122
5-8. Converting Numbers Between Different Bases 124
5-9. Searching for a String Without Knowing the Column or Table . 127
5-10. Predicting Data Values and Trends Beyond a Series End 130
5-11. Explicitly (Pessimistically) Locking Rows for an Update . 133
5-12. Synchronizing the Contents of Two Tables 138
PART 2 Data Types and Their Problems 141
Chapter 6: Working with Date and Time Values 143
6-1. Converting Datetime Values into Readable Strings 143
6-2. Converting Strings to Datetime Values . 145
6-3. Detecting Overlapping Date Ranges 146
6-4. Automatically Tracking Date and Time for Data Changes 148
6-5. Generating a Gapless Time Series from Data with Gaps . 150
6-6. Converting Dates and Times Between Time Zones 152
6-7. Detecting Leap Years 154
6-8. Computing the Last Date in a Month . 155
6-9. Determining the First Date or Day in a Month 156
6-10. Calculating the Day of the Week . 157
6-11. Grouping and Aggregating by Time Periods 159
6-12. Finding the Difference Between Two Dates or Date Parts . 160
6-13. Determining the Dates of Easter for Any Year 162
6-14. Calculating "X Day Active" Users for a Web Site . 164
Chapter 7: Strings . 167
7-1. Searching for a Substring 167
7-2. Extracting a Substring 170
7-3. Single-Character String Substitutions 172
7-4. Searching for a Pattern 174
7-5. Extracting a Pattern 178
7-6. Counting Patterns 179
7-7. Replacing Text in a String 182
7-8. Speeding Up String Searches 184
Chapter 8: Working with Numbers 187
8-1. Converting Between String and Numeric Data Types 187
8-2. Converting Between Numeric Data Types 188
8-3. Choosing Data Type Precision and Scale 190
8-4. Performing Calculations Correctly with Non-Numbers and Infinite Numbers 192
8-5. Validating Numbers in Strings 194
8-6. Generating Consecutive Numbers 196
8-7. Generating Numbers to a Formula or Pattern 198
8-8. Handling Nulls in Numeric Calculations 200
8-9. Automatically Rounding Numbers 202
8-10. Automatically Generating Lists of Numbers 204
PART 3 Your Development Environment 207
Chapter 9: Managing Transactions 209
9-1. Partially Rolling Back a Transaction 209
9-2. Identifying Blocking Transactions 213
9-3. Optimizing Row and Table Locking 214
9-4. Avoiding Deadlock Scenarios 216
9-5. Deferring Constraint Validation 218
9-6. Ensuring Read-Consistency Across a Transaction . 225
9-7. Managing Transaction Isolation Levels 226
Chapter 10: Data Dictionary 229
Graphical Tools vs. SQL . 229
Data Dictionary Architecture 230
10-1. Displaying User Information 233
10-2. Determining the Tables You Can Access 235
10-3. Displaying a Table's Disk Space Usage 237
10-4. Displaying Table Row Counts . 240
10-5. Displaying Indexes for a Table 241
10-6. Showing Foreign Key Columns Not Indexed 242
10-7. Displaying Constraints 244
10-8. Showing Primary Key and Foreign Key Relationships 246
10-9. Displaying Object Dependencies 247
10-10. Displaying Synonym Metadata 250
10-11. Displaying View Text 251
10-12. Displaying Database Code 253
10-13. Displaying Granted Roles . 254
10-14. Displaying Object Privileges 256
10-15. Displaying System Privileges . 257
PART 4 Data Dictionary . 261
Chapter 11: Common Reporting Problems 263
11-1. Avoiding Repeating Rows in Reports . 263
11-2. Parameterizing a SQL Report . 266
11-3. Returning Detail Columns in Grouped Results 269
11-4. Sorting Results into Equal-Size Buckets 271
11-5. Creating Report Histograms 273
11-6. Filtering Results by Relative Rank 275
11-7. Comparing Hypotheses on Sets of Data 277
11-8. Graphically Representing Data Distribution with Text 279
11-9. Producing Web-Page Reports Directly from the Database . 280
Chapter 12: Cleansing Data . 287
12-1. Detecting Duplicate Rows 287
12-2. Removing Duplicate Rows 289
12-3. Determining if Data Can Be Loaded as Numeric 290
12-4. Determining if Data Can Be Loaded as a Date 291
12-5. Performing Case-Insensitive Queries . 293
12-6. Obfuscating Values . 294
12-7. Dropping All Indexes 297
12-8. Disabling Constraints . 299
12-9. Disabling Triggers 304
12-10. Removing Data from a Table 305
12-11. Showing Differences in Schemas 307
Chapter 13: Tree-Structured Data 313
13-1. Traversing Hierarchical Data from Top to Bottom 315
13-2. Sorting Nodes Within a Hierarchical Level 318
13-3. Generating Pathnames from Hierarchical Tables 321
13-4. Identifying Leaf Data in a Hierarchical Table . 324
13-5. Detecting Cycles in Hierarchical Data 329
13-6. Generating a Fixed Number of Sequential Primary Keys . 330
Chapter 14: Working with XML Data 335
14-1. Translating SQL to XML . 335
14-2. Storing XML in Native Form 339
14-3. Shredding XML for Relational Use 341
14-4. Extracting Key XML Elements from an XML Document . 343
14-5. Generating Complex XML Documents 344
14-6. Validating XML Schema 346
14-7. Changing XML in Place . 349
Chapter 15: Partitioning 351
15-1. Determining if a Table Should be Partitioned 353
15-2. Partitioning by Range . 354
15-3. Partitioning by List 355
15-4. Partitioning by Hash 356
15-5. Partitioning a Table in Multiple Ways . 357
15-6. Creating Partitions on Demand . 359
15-7. Partitioning by Referential Constraints . 360
15-8. Partitioning on a Virtual Column 362
15-9. Application-Controlled Partitioning . 363
15-10. Configuring Partitions with Tablespaces . 364
15-11. Automatically Moving Updated Rows . 365
15-12. Partitioning an Existing Table 366
15-13. Adding a Partition to a Partitioned Table . 368
15-14. Exchanging a Partition with an Existing Table 369
15-15. Renaming a Partition 371
15-16. Splitting a Partition . 372
15-17. Merging Partitions 373
15-18. Dropping a Partition 375
15-19. Removing Rows from a Partition . 376
15-20. Generating Statistics for a Partition 377
15-21. Creating an Index that Maps to a Partition (Local Index) . 377
15-22. Creating an Index with Its Own Partitioning Scheme (Global Index) 380
Chapter 16: LOBs 383
16-1. Loading Large Documents into CLOB Columns . 384
16-2. Loading Image Data into BLOB Columns 387
16-3. Using SQL*Loader to Bulk-Load Large Objects . 389
16-4. Accessing Large Objects Using HTTP . 391
16-5. Making External Large Objects (BFILEs) Available to the Database 396
16-6. Deleting or Updating LOBs in a Database Table 398
PART 5 Administration 401
Chapter 17: Database Administration . 403
17-1. Creating a Database 404
17-2. Dropping a Database 406
17-3. Verifying Connection Information . 407
17-4. Creating Tablespaces . 409
17-5. Dropping a Tablespace . 411
17-6. Adjusting Tablespace Size 412
17-7. Limiting Database Resources per Session 413
17-8. Associating a Group of Privileges 416
17-9. Creating Users 419
17-10. Dropping Users . 420
17-11. Modifying Passwords . 421
17-12. Enforcing Password Complexity 422
Chapter 18: Object Mamagement . 425
18-1. Creating a Table 425
18-2. Storing Data Temporarily . 427
18-3. Moving a Table 429
18-4. Renaming Objects 430
18-5. Dropping a Table 433
18-6. Undropping a Table . 434
18-7. Creating an Index . 435
18-8. Creating a Function-Based Index . 438
18-9. Creating a Bitmap Index 439
18-10. Creating an Index-Organized Table . 440
18-11. Creating a View . 441
18-12. Creating an Alternate Name for an Object 443
18-13. Enforcing Unique Rows in a Table 445
18-14. Ensuring Lookup Values Exist 448
18-15. Checking Data for a Condition 449
18-16. Creating a Connection Between Databases 451
18-17. Creating an Auto-incrementing Value 453
Chapter 19: SQL Monitoring and Tuning 457
19-1. Monitoring Real-Time SQL Execution Statistics 457
19-2. Displaying a Query's Progress in the Execution Plan . 459
19-3. Determining How Much SQL Work Is Left 462
19-4. Identifying Resource-Intensive SQL Statements 463
19-5. Using Oracle Performance Reports to Identify Resource-Intensive SQL 465
19-6. Using the Operating System to Identify Resource-Intensive Queries 469
19-7. Displaying an Execution Plan Using AUTOTRACE . 471
19-8. Generating an Execution Plan Using DBMS_XPLAN 474
19-9. Tracing All SQL Statements for a Session 476
19-10. Interpreting an Execution Plan 483
19-11. Obtaining SQL Tuning Advice . 488
19-12. Forcing Your Own Execution Plan on a Query 490
19-13. Viewing Optimizer Statistics 492
19-14. Generating Statistics 494
Chapter 20: Database Troubleshooting . 497
20-1. Determining Causes of Database Problems 497
20-2. Displaying Open Cursors 501
20-3. Determining If Online Redo Logs Are Sized Properly . 503
20-4. Determining If Undo Is Sized Properly 505
20-5. Determining If Temporary Tablespace Is Sized Correctly 507
20-6. Displaying Tablespace Fullness 509
20-7. Showing Object Sizes 511
20-8. Monitoring Index Usage 513
20-9. Auditing Object Usage 514
20-10. Auditing at a Granular Level 516
Index 519