Search and Find
Service
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
All prices incl. VAT