Oracle SQL Recipes - A Problem-Solution Approach

Contents at a Glance

Contents

About the Authors

Introduction

Foundations of Data Manipulation

The Basics

1-1. Retrieving Data from a Table

1-2. Selecting All Columns from a Table

1-3. Sorting Your Results

1-4. Adding Rows to a Table

1-5. Copying Rows from One Table to Another

1-6. Copying Data in Bulk from One Table to Another

1-7. Changing Values in a Row

1-8. Updating Multiple Fields with One Statement

1-9. Removing Unwanted Rows from a Table

1-10. Removing All Rows from a Table

1-11. Selecting from the Results of Another Query

1-12. Basing a Where Condition on a Query

1-13. Finding and Eliminating NULLs in Queries

1-14. Sorting as a Person Expects

1-15. Enabling Other Sorting and Comparison Options

1-16. Conditional Inserting or Updating Based on Existence

Summarizing and Aggregating Data

2-1. Summarizing the Values in a Column

2-2. Summarizing Data for Different Groups

2-3. Grouping Data by Multiple Fields

2-4. Ignoring Groups in Aggregate Data Sets

2-5. Aggregating Data at Multiple Levels

2-6. Using Aggregate Results in Other Queries

2-7. Counting Members in Groups and Sets

2-8. Finding Duplicates and Unique Values in a Table

2-9. Calculating Totals and Subtotals

2-10. Building Your Own Aggregate Function

2-11. Accessing Values from Subsequent or Preceding Rows

2-12. Assigning Ranking Values to Rows in a Query Result

2-13. Finding First and Last Values within a Group

2-14. Performing Aggregations over Moving Windows

2-15. Removing Duplicate Rows Based on a Subset of Columns

2-16. Finding Sequence Gaps in a Table

Querying from Multiple Tables

3-1. Joining Corresponding Rows from Two or More Tables

3-2. Stacking Query Results Vertically

3-3. Writing an Optional Join

3-4. Making a Join Optional in Both Directions

3-5. Removing Rows Based on Data in Other Tables

3-6. Finding Matched Data Across Tables

3-7. Joining on Aggregates

3-8. Finding Missing Rows

3-9. Finding Rows that Tables Do Not Have in Common

3-10. Generating Test Data

3-11. Updating Rows Based on Data in Other Tables

3-12. Manipulating and Comparing NULLs in Join Conditions

Creating and Deriving Data

4-1. Deriving New Columns

4-2. Returning Nonexistent Rows

4-3. Changing Rows into Columns

4-4. Pivoting on Multiple Columns

4-5. Changing Columns into Rows

4-6. Concatenating Data for Readability

4-7. Translating Strings to Numeric Equivalents

4-8. Generating Random Data

4-9. Creating a Comma-Separated Values File

Common Query Patterns

5-1. Changing Nulls into Real Values

5-2. Sorting on Null Values

5-3. Paginating Query Results

5-4. Testing for the Existence of Data

5-5. Conditional Branching In One SQL Statement

5-6. Conditional Sorting and Sorting By Function

5-7. Overcoming Issues and Errors when Subselects Return Unexpected Multiple Values

5-8. Converting Numbers Between Different Bases

5-9. Searching for a String Without Knowing the Column or Table

5-10. Predicting Data Values and Trends Beyond a Series End

5-11. Explicitly (Pessimistically) Locking Rows for an Update

5-12. Synchronizing the Contents of Two Tables

Data Types and Their Problems

Working with Date and Time Values

6-1. Converting Datetime Values into Readable Strings

6-2. Converting Strings to Datetime Values

6-3. Detecting Overlapping Date Ranges

6-4. Automatically Tracking Date and Time for Data Changes

6-5. Generating a Gapless Time Series from Data with Gaps

6-6. Converting Dates and Times Between Time Zones

6-7. Detecting Leap Years

6-8. Computing the Last Date in a Month

6-9. Determining the First Date or Day in a Month

6-10. Calculating the Day of the Week

6-11. Grouping and Aggregating by Time Periods

6-12. Finding the Difference Between Two Dates or Date Parts

6-13. Determining the Dates of Easter for Any Year

6-14. Calculating “X Day Active” Users for a Web Site

Strings

7-1. Searching for a Substring

7-2. Extracting a Substring

7-3. Single-Character String Substitutions

7-4. Searching for a Pattern

7-5. Extracting a Pattern

7-6. Counting Patterns

7-7. Replacing Text in a String

7-8. Speeding Up String Searches

Working with Numbers

8-1. Converting Between String and Numeric Data Types

8-2. Converting Between Numeric Data Types

8-3. Choosing Data Type Precision and Scale

8-4. Performing Calculations Correctly with Non-Numbers and Infinite Numbers

8-5. Validating Numbers in Strings

8-6. Generating Consecutive Numbers

8-7. Generating Numbers to a Formula or Pattern

8-8. Handling Nulls in Numeric Calculations

8-9. Automatically Rounding Numbers

8-10. Automatically Generating Lists of Numbers

Your Development Environment

Managing Transactions

9-1. Partially Rolling Back a Transaction

9-2. Identifying Blocking Transactions

9-3. Optimizing Row and Table Locking

9-4. Avoiding Deadlock Scenarios

9-5. Deferring Constraint Validation

9-6. Ensuring Read-Consistency Across a Transaction

9-7. Managing Transaction Isolation Levels

Data Dictionary

Graphical Tools vs. SQL

Data Dictionary Architecture

10-1. Displaying User Information

10-2. Determining the Tables You Can Access

10-3. Displaying a Table’s Disk Space Usage

10-4. Displaying Table Row Counts

10-5. Displaying Indexes for a Table

10-6. Showing Foreign Key Columns Not Indexed

10-7. Displaying Constraints

10-8. Showing Primary Key and Foreign Key Relationships

10-9. Displaying Object Dependencies

10-10. Displaying Synonym Metadata

10-11. Displaying View Text

10-12. Displaying Database Code

10-13. Displaying Granted Roles

10-14. Displaying Object Privileges

10-15. Displaying System Privileges

Common Reporting Problems

11-1. Avoiding Repeating Rows in Reports

11-2. Parameterizing a SQL Report

11-3. Returning Detail Columns in Grouped Results

11-4. Sorting Results into Equal-Size Buckets

11-5. Creating Report Histograms

11-6. Filtering Results by Relative Rank

11-7. Comparing Hypotheses on Sets of Data

11-8. Graphically Representing Data Distribution with Text

11-9. Producing Web-Page Reports Directly from the Database

Cleansing Data

12-1. Detecting Duplicate Rows

12-2. Removing Duplicate Rows

12-3. Determining if Data Can Be Loaded as Numeric

12-4. Determining if Data Can Be Loaded as a Date

12-5. Performing Case-Insensitive Queries

12-6. Obfuscating Values

12-7. Dropping All Indexes

12-8. Disabling Constraints

12-9. Disabling Triggers

12-10. Removing Data from a Table

12-11. Showing Differences in Schemas

Tree-Structured Data

13-1. Traversing Hierarchical Data from Top to Bottom

13-2. Sorting Nodes Within a Hierarchical Level

13-3. Generating Pathnames from Hierarchical Tables

13-4. Identifying Leaf Data in a Hierarchical Table

13-5. Detecting Cycles in Hierarchical Data

13-6. Generating a Fixed Number of Sequential Primary Keys

Working with XML Data

14-1. Translating SQL to XML

14-2. Storing XML in Native Form

14-3. Shredding XML for Relational Use

14-4. Extracting Key XML Elements from an XML Document

14-5. Generating Complex XML Documents

14-6. Validating XML Schema

14-7. Changing XML in Place

Partitioning

15-1. Determining if a Table Should be Partitioned

15-2. Partitioning by Range

15-3. Partitioning by List

15-4. Partitioning by Hash

15-5. Partitioning a Table in Multiple Ways

15-6. Creating Partitions on Demand

15-7. Partitioning by Referential Constraints

15-8. Partitioning on a Virtual Column

15-9. Application-Controlled Partitioning

15-10. Configuring Partitions with Tablespaces

15-11. Automatically Moving Updated Rows

15-12. Partitioning an Existing Table

15-13. Adding a Partition to a Partitioned Table

15-14. Exchanging a Partition with an Existing Table

15-15. Renaming a Partition

15-16. Splitting a Partition

15-17. Merging Partitions

15-18. Dropping a Partition

15-19. Removing Rows from a Partition

15-20. Generating Statistics for a Partition

15-21. Creating an Index that Maps to a Partition (Local Index)

15-22. Creating an Index with Its Own Partitioning Scheme ( Global Index)

LOBs

16-1. Loading Large Documents into CLOB Columns

16-2. Loading Image Data into BLOB Columns

16-3. Using SQL*Loader to Bulk-Load Large Objects

16-4. Accessing Large Objects Using HTTP

16-5. Making External Large Objects (BFILEs) Available to the Database

16-6. Deleting or Updating LOBs in a Database Table

Database Administration

17-1. Creating a Database

17-2. Dropping a Database

17-3. Verifying Connection Information

17-4. Creating Tablespaces

17-5. Dropping a Tablespace

17-6. Adjusting Tablespace Size

17-7. Limiting Database Resources per Session

17-8. Associating a Group of Privileges

17-9. Creating Users

17-10. Dropping Users

17-11. Modifying Passwords

17-12. Enforcing Password Complexity

Object Management

18-1. Creating a Table

18-2. Storing Data Temporarily

18-3. Moving a Table

18-4. Renaming Objects

18-5. Dropping a Table

18-6. Undropping a Table

18-7. Creating an Index

18-8. Creating a Function-Based Index

18-9. Creating a Bitmap Index

18-10. Creating an Index-Organized Table

18-11. Creating a View

18-12. Creating an Alternate Name for an Object

18-13. Enforcing Unique Rows in a Table

18-14. Ensuring Lookup Values Exist

18-15. Checking Data for a Condition

18-16. Creating a Connection Between Databases

18-17. Creating an Auto-incrementing Value

SQL Monitoring and Tuning

19-1. Monitoring Real-Time SQL Execution Statistics

19-2. Displaying a Query’s Progress in the Execution Plan

19-3. Determining How Much SQL Work Is Left

19-4. Identifying Resource-Intensive SQL Statements

19-5. Using Oracle Performance Reports to Identify Resource- Intensive SQL

19-6. Using the Operating System to Identify Resource- Intensive Queries

19-7. Displaying an Execution Plan Using AUTOTRACE

19-8. Generating an Execution Plan Using DBMS_XPLAN

19-9. Tracing All SQL Statements for a Session

19-10. Interpreting an Execution Plan

19-11. Obtaining SQL Tuning Advice

19-12. Forcing Your Own Execution Plan on a Query

19-13. Viewing Optimizer Statistics

19-14. Generating Statistics

Database Troubleshooting

20-1. Determining Causes of Database Problems

20-2. Displaying Open Cursors

20-3. Determining If Online Redo Logs Are Sized Properly

20-4. Determining If Undo Is Sized Properly

20-5. Determining If Temporary Tablespace Is Sized Correctly

20-6. Displaying Tablespace Fullness

20-7. Showing Object Sizes

20-8. Monitoring Index Usage

20-9. Auditing Object Usage

20-10. Auditing at a Granular Level

Index