Oracle SQL Recipes: A Problem-Solution Approach

Oracle SQL Recipes: A Problem-Solution Approach

by Grant Allen, Bob Bryla, Darl Kuhn
Released November 2009
Publisher(s): Apress
ISBN: 9781430225096

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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

    Product information

    • Title: Oracle SQL Recipes: A Problem-Solution Approach
    • Author(s): Grant Allen, Bob Bryla, Darl Kuhn
    • Release date: November 2009
    • Publisher(s): Apress
    • ISBN: 9781430225096