You are previewing Oracle SQL Recipes: A Problem-Solution Approach.
O'Reilly logo
Oracle SQL Recipes: A Problem-Solution Approach

Book Description

Have you ever been faced with a new type of query to write, or been asked to create an unfamiliar database object? In such situations, you have probably wanted a good, solid example upon which to build, and instead have been forced into the drudgery of parsing railroad-style syntax diagrams in Oracle's manual set. This book frees you from that drudgery by providing tested and working examples of SQL used to solve common problems faced by developers and database administrators on a daily basis. When you're under pressure to get results fast, Oracle SQL Recipes is there at your side.

  • Example-based, providing quality solutions to everyday problems

  • Respects your time by putting solutions first and keeping discussions short

  • Solves the most commonly encountered SQL problems

What you'll learn

  • Recognize common query patterns and apply their corresponding solutions.

  • Solve common reporting problems such as sorting rows into buckets.

  • Troubleshoot SQL performance problems.

  • Create and manage database objects such as tables, indexes, and views.

  • Work with XML and tree-structured data.

  • Take advantage of analytic functions, regular expressions, hierarchical query support, and other powerful aspects of Oracle SQL.

Who is this book for?

Oracle SQL Recipes is written for developers and database administrators who write SQL to run against an Oracle database. It is further written for those whose time is precious, and who just wish for a good example to help them get on with their work. If you want to spend your time writing SQL rather than reading about it, then this is the right book for you.

Table of Contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewer
  4. Acknowledgements
  5. Introduction
    1. Who This Book Is For
    2. How This Book Is Structured
    3. Choosing Tools
    4. Working With Example Data for SQL Recipes
    5. Conventions
    6. Contacting the Authors
  6. 1. Foundation of Data Manipulation
    1. 1. The Basics
      1. 1.1. Retrieving Data from a Table
      2. 1.2. Selecting All Columns from a Table
      3. 1.3. Sorting Your Results
      4. 1.4. Adding Rows to a Table
      5. 1.5. Copying Rows from One Table to Another
      6. 1.6. Copying Data in Bulk from One Table to Another
      7. 1.7. Changing Values in a Row
      8. 1.8. Updating Multiple Fields with One Statement
      9. 1.9. Removing Unwanted Rows from a Table
      10. 1.10. Removing All Rows from a Table
      11. 1.11. Selecting from the Results of Another Query
      12. 1.12. Basing a Where Condition on a Query
      13. 1.13. Finding and Eliminating NULLs in Queries
      14. 1.14. Sorting as a Person Expects
      15. 1.15. Enabling Other Sorting and Comparison Options
      16. 1.16. Conditional Inserting or Updating Based on Existence
    2. 2. Summarizing and Aggregating Data
      1. 2.1. Summarizing the Values in a Column
      2. 2.2. Summarizing Data for Different Groups
      3. 2.3. Grouping Data by Multiple Fields
      4. 2.4. Ignoring Groups in Aggregate Data Sets
      5. 2.5. Aggregating Data at Multiple Levels
      6. 2.6. Using Aggregate Results in Other Queries
      7. 2.7. Counting Members in Groups and Sets
      8. 2.8. Finding Duplicates and Unique Values in a Table
      9. 2.9. Calculating Totals and Subtotals
      10. 2.10. Building Your Own Aggregate Function
      11. 2.11. Accessing Values from Subsequent or Preceding Rows
      12. 2.12. Assigning Ranking Values to Rows in a Query Result
      13. 2.13. Finding First and Last Values within a Group
      14. 2.14. Performing Aggregations over Moving Windows
      15. 2.15. Removing Duplicate Rows Based on a Subset of Columns
      16. 2.16. Finding Sequence Gaps in a Table
    3. 3. Querying from Multiple Tables
      1. 3.1. Joining Corresponding Rows from Two or More Tables
      2. 3.2. Stacking Query Results Vertically
      3. 3.3. Writing an Optional Join
      4. 3.4. Making a Join Optional in Both Directions
      5. 3.5. Removing Rows Based on Data in Other Tables
      6. 3.6. Finding Matched Data Across Tables
      7. 3.7. Joining on Aggregates
      8. 3.8. Finding Missing Rows
      9. 3.9. Finding Rows that Tables Do Not Have in Common
      10. 3.10. Generating Test Data
      11. 3.11. Updating Rows Based on Data in Other Tables
      12. 3.12. Manipulating and Comparing NULLs in Join Conditions
    4. 4. Creating and Deriving Data
      1. 4.1. Deriving New Columns
      2. 4.2. Returning Nonexistent Rows
      3. 4.3. Changing Rows into Columns
      4. 4.4. Pivoting on Multiple Columns
      5. 4.5. Changing Columns into Rows
      6. 4.6. Concatenating Data for Readability
      7. 4.7. Translating Strings to Numeric Equivalents
      8. 4.8. Generating Random Data
      9. 4.9. Creating a Comma-Separated Values File
    5. 5. Common Query Patterns
      1. 5.1. Changing Nulls into Real Values
      2. 5.2. Sorting on Null Values
      3. 5.3. Paginating Query Results
      4. 5.4. Testing for the Existence of Data
      5. 5.5. Conditional Branching In One SQL Statement
      6. 5.6. Conditional Sorting and Sorting By Function
      7. 5.7. Overcoming Issues and Errors when Subselects Return Unexpected Multiple Values
      8. 5.8. Converting Numbers Between Different Bases
      9. 5.9. Searching for a String Without Knowing the Column or Table
      10. 5.10. Predicting Data Values and Trends Beyond a Series End
      11. 5.11. Explicitly (Pessimistically) Locking Rows for an Update
      12. 5.12. Synchronizing the Contents of Two Tables
  7. 2. Data Types and Their Problems
    1. 6. Working with Date and Time Values
      1. 6.1. Converting Datetime Values into Readable Strings
      2. 6.2. Converting Strings to Datetime Values
      3. 6.3. Detecting Overlapping Date Ranges
      4. 6.4. Automatically Tracking Date and Time for Data Changes
      5. 6.5. Generating a Gapless Time Series from Data with Gaps
      6. 6.6. Converting Dates and Times Between Time Zones
      7. 6.7. Detecting Leap Years
      8. 6.8. Computing the Last Date in a Month
      9. 6.9. Determining the First Date or Day in a Month
      10. 6.10. Calculating the Day of the Week
      11. 6.11. Grouping and Aggregating by Time Periods
      12. 6.12. Finding the Difference Between Two Dates or Date Parts
      13. 6.13. Determining the Dates of Easter for Any Year
      14. 6.14. Calculating "X Day Active" Users for a Web Site
    2. 7. Strings
      1. 7.1. Searching for a Substring
      2. 7.2. Extracting a Substring
      3. 7.3. Single-Character String Substitutions
      4. 7.4. Searching for a Pattern
      5. 7.5. Extracting a Pattern
      6. 7.6. Counting Patterns
      7. 7.7. Replacing Text in a String
      8. 7.8. Speeding Up String Searches
    3. 8. Working with Numbers
      1. 8.1. Converting Between String and Numeric Data Types
      2. 8.2. Converting Between Numeric Data Types
      3. 8.3. Choosing Data Type Precision and Scale
      4. 8.4. Performing Calculations Correctly with Non-Numbers and Infinite Numbers
      5. 8.5. Validating Numbers in Strings
      6. 8.6. Generating Consecutive Numbers
      7. 8.7. Generating Numbers to a Formula or Pattern
      8. 8.8. Handling Nulls in Numeric Calculations
      9. 8.9. Automatically Rounding Numbers
      10. 8.10. Automatically Generating Lists of Numbers
  8. 3. Your Development Environment
    1. 9. Managing Transactions
      1. 9.1. Partially Rolling Back a Transaction
      2. 9.2. Identifying Blocking Transactions
      3. 9.3. Optimizing Row and Table Locking
      4. 9.4. Avoiding Deadlock Scenarios
      5. 9.5. Deferring Constraint Validation
      6. 9.6. Ensuring Read-Consistency Across a Transaction
      7. 9.7. Managing Transaction Isolation Levels
    2. 10. Data Dictionary
      1. 10.1. Graphical Tools vs. SQL
      2. 10.2. Data Dictionary Architecture
      3. 10.3. Displaying User Information
      4. 10.4. Determining the Tables You Can Access
      5. 10.5. Displaying a Table's Disk Space Usage
      6. 10.6. Displaying Table Row Counts
      7. 10.7. Displaying Indexes for a Table
      8. 10.8. Showing Foreign Key Columns Not Indexed
      9. 10.9. Displaying Constraints
      10. 10.10. Showing Primary Key and Foreign Key Relationships
      11. 10.11. Displaying Object Dependencies
      12. 10.12. Displaying Synonym Metadata
      13. 10.13. Displaying View Text
      14. 10.14. Displaying Database Code
      15. 10.15. Displaying Granted Roles
      16. 10.16. Displaying Object Privileges
      17. 10.17. Displaying System Privileges
  9. 4. Data Dictionary
    1. 11. Common Reporting Problems
      1. 11.1. Avoiding Repeating Rows in Reports
      2. 11.2. Parameterizing a SQL Report
      3. 11.3. Returning Detail Columns in Grouped Results
      4. 11.4. Sorting Results into Equal-Size Buckets
      5. 11.5. Creating Report Histograms
      6. 11.6. Filtering Results by Relative Rank
      7. 11.7. Comparing Hypotheses on Sets of Data
      8. 11.8. Graphically Representing Data Distribution with Text
      9. 11.9. Producing Web-Page Reports Directly from the Database
    2. 12. Cleansing Data
      1. 12.1. Detecting Duplicate Rows
      2. 12.2. Removing Duplicate Rows
      3. 12.3. Determining if Data Can Be Loaded as Numeric
      4. 12.4. Determining if Data Can Be Loaded as a Date
      5. 12.5. Performing Case-Insensitive Queries
      6. 12.6. Obfuscating Values
      7. 12.7. Dropping All Indexes
      8. 12.8. Disabling Constraints
      9. 12.9. Disabling Triggers
      10. 12.10. Removing Data from a Table
      11. 12.11. Showing Differences in Schemas
    3. 13. Tree-Structured Data
      1. 13.1. Traversing Hierarchical Data from Top to Bottom
      2. 13.2. Sorting Nodes Within a Hierarchical Level
      3. 13.3. Generating Pathnames from Hierarchical Tables
      4. 13.4. Identifying Leaf Data in a Hierarchical Table
      5. 13.5. Detecting Cycles in Hierarchical Data
      6. 13.6. Generating a Fixed Number of Sequential Primary Keys
    4. 14. Working with XML Data
      1. 14.1. Translating SQL to XML
      2. 14.2. Storing XML in Native Form
      3. 14.3. Shredding XML for Relational Use
      4. 14.4. Extracting Key XML Elements from an XML Document
      5. 14.5. Generating Complex XML Documents
      6. 14.6. Validating XML Schema
      7. 14.7. Changing XML in Place
    5. 15. Partitioning
      1. 15.1. Determining if a Table Should be Partitioned
      2. 15.2. Partitioning by Range
      3. 15.3. Partitioning by List
      4. 15.4. Partitioning by Hash
      5. 15.5. Partitioning a Table in Multiple Ways
      6. 15.6. Creating Partitions on Demand
      7. 15.7. Partitioning by Referential Constraints
      8. 15.8. Partitioning on a Virtual Column
      9. 15.9. Application-Controlled Partitioning
      10. 15.10. Configuring Partitions with Tablespaces
      11. 15.11. Automatically Moving Updated Rows
      12. 15.12. Partitioning an Existing Table
      13. 15.13. Adding a Partition to a Partitioned Table
      14. 15.14. Exchanging a Partition with an Existing Table
      15. 15.15. Renaming a Partition
      16. 15.16. Splitting a Partition
      17. 15.17. Merging Partitions
      18. 15.18. Dropping a Partition
      19. 15.19. Removing Rows from a Partition
      20. 15.20. Generating Statistics for a Partition
      21. 15.21. Creating an Index that Maps to a Partition (LocalIndex)
      22. 15.22. Creating an Index with Its Own Partitioning Scheme (Global Index)
    6. 16. LOBs
      1. 16.1. Loading Large Documents into CLOB Columns
      2. 16.2. Loading Image Data into BLOB Columns
      3. 16.3. Using SQL*Loader to Bulk-Load Large Objects
      4. 16.4. Accessing Large Objects Using HTTP
      5. 16.5. Making External Large Objects (BFILEs) Available to the Database
      6. 16.6. Deleting or Updating LOBs in a Database Table
  10. 5. Administration
    1. 17. Database Administration
      1. 17.1. Creating a Database
      2. 17.2. Dropping a Database
      3. 17.3. Verifying Connection Information
      4. 17.4. Creating Tablespaces
      5. 17.5. Dropping a Tablespace
      6. 17.6. Adjusting Tablespace Size
      7. 17.7. Limiting Database Resources per Session
      8. 17.8. Associating a Group of Privileges
      9. 17.9. Creating Users
      10. 17.10. Dropping Users
      11. 17.11. Modifying Passwords
      12. 17.12. Enforcing Password Complexity
    2. 18. Object Management
      1. 18.1. Creating a Table
      2. 18.2. Storing Data Temporarily
      3. 18.3. Moving a Table
      4. 18.4. Renaming Objects
      5. 18.5. Dropping a Table
      6. 18.6. Undropping a Table
      7. 18.7. Creating an Index
      8. 18.8. Creating a Function-Based Index
      9. 18.9. Creating a Bitmap Index
      10. 18.10. Creating an Index-Organized Table
      11. 18.11. Creating a View
      12. 18.12. Creating an Alternate Name for an Object
      13. 18.13. Enforcing Unique Rows in a Table
      14. 18.14. Ensuring Lookup Values Exist
      15. 18.15. Checking Data for a Condition
      16. 18.16. Creating a Connection Between Databases
      17. 18.17. Creating an Auto-incrementing Value
    3. 19. Monitoring and Tuning
      1. 19.1. Monitoring Real-Time SQL Execution Statistics
      2. 19.2. Displaying a Query's Progress in the Execution Plan
      3. 19.3. Determining How Much SQL Work Is Left
      4. 19.4. Identifying Resource-Intensive SQL Statements
      5. 19.5. Using Oracle Performance Reports to Identify Resource-Intensive SQL
      6. 19.6. Using the Operating System to Identify Resource-Intensive Queries
      7. 19.7. Displaying an Execution Plan Using AUTOTRACE
      8. 19.8. Generating an Execution Plan Using DBMS_XPLAN
      9. 19.9. Tracing All SQL Statements for a Session
      10. 19.10. Interpreting an Execution Plan
      11. 19.11. Obtaining SQL Tuning Advice
      12. 19.12. Forcing Your Own Execution Plan on a Query
      13. 19.13. Viewing Optimizer Statistics
      14. 19.14. Generating Statistics
    4. 20. Database Troubleshooting
      1. 20.1. Determining Causes of Database Problems
      2. 20.2. Displaying Open Cursors
      3. 20.3. Determining If Online Redo Logs Are Sized Properly
      4. 20.4. Determining If Undo Is Sized Properly
      5. 20.5. Determining If Temporary Tablespace Is Sized Correctly
      6. 20.6. Displaying Tablespace Fullness
      7. 20.7. Showing Object Sizes
      8. 20.8. Monitoring Index Usage
      9. 20.9. Auditing Object Usage
      10. 20.10. Auditing at a Granular Level