Pro Oracle SQL

Pro Oracle SQL

by Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Released November 2010
Publisher(s): Apress
ISBN: 9781430232285

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

Pro Oracle SQL unlocks the power of SQL in the Oracle Database—one of the most potent SQL implementations on the market today. To master it requires a three-pronged approach: learn the language features, learn the supporting features that Oracle provides to help use the language effectively, and learn to think and work in sets.

Karen Morton and her team help you master powerful aspects of Oracle SQL not found in competing databases. You'll learn analytic functions, the MODEL clause, and advanced grouping syntax—techniques that will help in creating good queries for reporting and business intelligence applications. Pro Oracle SQL also helps you minimize parsing overhead, read execution plans, test for correct results, and exert control over SQL execution in your database. You'll learn when to create indexes, how to verify that they make a difference, how to use SQL Profiles to optimize SQL in packaged applications, and much more. You'll also understand how SQL is optimized for working in sets, and that the key to getting accurate results lies in making sure that queries ask clear and precise questions.

What's the bottom-line? Pro Oracle SQL helps you work at a truly professional level in Oracle dialect of SQL. You'll master the language, the tools to work effectively with the language, and the right way to think about a problem in SQL. Pro Oracle SQL helps you rise above the crowd to provide stellar service in your chosen profession.

  • Endorsed by the OakTable Network, a group of Oracle technologists well-known for their rigorous and scientific approach to Oracle Database performance

  • Comprehensive - goes beyond the language with a focus on what you need to know to write successful queries and data manipulation statements.

Table of contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewers
  4. Acknowledgments
  5. 1. Core SQL
    1. 1.1. The SQL Language
    2. 1.2. Interfacing to the Database
    3. 1.3. Review of SQL*Plus
      1. 1.3.1. Connect to a Database
      2. 1.3.2. Configuring the SQL*Plus environment
      3. 1.3.3. Executing Commands
    4. 1.4. The Five Core SQL Statements
    5. 1.5. The SELECT Statement
      1. 1.5.1. The FROM Clause
      2. 1.5.2. The WHERE Clause
      3. 1.5.3. The GROUP BY Clause
      4. 1.5.4. The HAVING Clause
      5. 1.5.5. The SELECT List
      6. 1.5.6. The ORDER BY Clause
    6. 1.6. The INSERT Statement
      1. 1.6.1. Single-table Inserts
      2. 1.6.2. Multi-table Inserts
    7. 1.7. The UPDATE Statement
    8. 1.8. The DELETE Statement
    9. 1.9. The MERGE Statement
    10. 1.10. Summary
  6. 2. SQL Execution
    1. 2.1. Oracle Architecture Basics
    2. 2.2. SGA – The Shared Pool
    3. 2.3. The Library Cache
    4. 2.4. Identical Statements
    5. 2.5. SGA – The Buffer Cache
    6. 2.6. Query Transformation
    7. 2.7. View Merging
    8. 2.8. Subquery Unnesting
    9. 2.9. Predicate Pushing
    10. 2.10. Query Rewrite with Materialized Views
    11. 2.11. Determining the Execution Plan
    12. 2.12. Executing the Plan and Fetching Rows
    13. 2.13. SQL Execution – Putting It All Together
    14. 2.14. Summary
  7. 3. Access and Join Methods
    1. 3.1. Full Scan Access Methods
      1. 3.1.1. How Full Scan Operations are Chosen
      2. 3.1.2. Full Scans and Throwaway
      3. 3.1.3. Full Scans and Multiblock Reads
      4. 3.1.4. Full Scans and the Highwater Mark
    2. 3.2. Index Scan Access Methods
      1. 3.2.1. Index Structure
      2. 3.2.2. Index Scan Types
      3. 3.2.3. Index Unique Scan
      4. 3.2.4. Index range scan
      5. 3.2.5. Index Full Scan
      6. 3.2.6. Index Skip Scan
      7. 3.2.7. Index Fast Full Scan
    3. 3.3. Join Methods
      1. 3.3.1. Nested Loops Joins
      2. 3.3.2. Sort-Merge Joins
      3. 3.3.3. Hash Joins
      4. 3.3.4. Cartesian Joins
      5. 3.3.5. Outer Joins
    4. 3.4. Summary
  8. 4. SQL is About Sets
    1. 4.1. Thinking in Sets
      1. 4.1.1. Moving from Procedural to Set-based Thinking
      2. 4.1.2. Procedural vs Set-based Thinking: An Example
    2. 4.2. Set Operations
      1. 4.2.1. UNION and UNION ALL
      2. 4.2.2. MINUS
      3. 4.2.3. INTERSECT
    3. 4.3. Sets and Nulls
      1. 4.3.1. NULLs and Unintuitive Results
      2. 4.3.2. NULL Behavior in Set Operations
      3. 4.3.3. NULLs and GROUP BY and ORDER BY
      4. 4.3.4. NULLs and Aggregate Functions
    4. 4.4. Summary
  9. 5. It's About the Question
    1. 5.1. Asking Good Questions
    2. 5.2. The Purpose of Questions
    3. 5.3. Categories of Questions
    4. 5.4. Questions about the Question
    5. 5.5. Questions about Data
    6. 5.6. Building Logical Expressions
    7. 5.7. Summary
  10. 6. SQL Execution Plans
    1. 6.1. Explain Plans
      1. 6.1.1. Using Explain Plan
      2. 6.1.2. Understanding How EXPLAIN PLAN can Miss the Mark
      3. 6.1.3. Reading the Plan
    2. 6.2. Execution Plans
      1. 6.2.1. Viewing Recently Generated SQL
      2. 6.2.2. Viewing the Associated Execution Plan
      3. 6.2.3. Collecting the Plan Statistics
      4. 6.2.4. Identifying SQL Statements for Later Plan Retrieval
      5. 6.2.5. Understanding DBMS_XPLAN in Detail
      6. 6.2.6. Using Plan Information for Solving Problems
    3. 6.3. Summary
  11. 7. Advanced Grouping
    1. 7.1. Basic GROUP BY Usage
    2. 7.2. HAVING Clause
    3. 7.3. "New" GROUP BY Functionality
    4. 7.4. CUBE Extension to GROUP BY
    5. 7.5. Putting CUBE To Work
    6. 7.6. Eliminate NULLs with the GROUPING() Function
    7. 7.7. Extending Reports with GROUPING()
    8. 7.8. Extending Reports With GROUPING_ID()
    9. 7.9. GROUPING SETS and ROLLUP()
    10. 7.10. GROUP BY Restrictions
    11. 7.11. Summary
  12. 8. Analytic Functions
    1. 8.1. Example Data
    2. 8.2. Anatomy of Analytic Functions
    3. 8.3. List of Functions
    4. 8.4. Aggregation Functions
      1. 8.4.1. Aggregate Function Over An Entire Partition
      2. 8.4.2. Granular Window Specifications
      3. 8.4.3. Default Window Specification
    5. 8.5. Lead and Lag
      1. 8.5.1. Syntax and Ordering
      2. 8.5.2. Example 1: Returning a Value from Prior Row
      3. 8.5.3. Understanding that Offset is in Rows
      4. 8.5.4. Example 2: Returning a Value from an Upcoming Row
    6. 8.6. First_value & Last_value
      1. 8.6.1. Example: First_value to Calculate Maximum
      2. 8.6.2. Example: Last_value to Calculate Minimum
    7. 8.7. Other Analytic Functions
      1. 8.7.1. Nth_value (11gR2)
      2. 8.7.2. Rank
      3. 8.7.3. Dense_rank
      4. 8.7.4. Row_number
      5. 8.7.5. Ratio_to_report
      6. 8.7.6. Percent_rank
      7. 8.7.7. Percentile_cont
      8. 8.7.8. Percentile_disc
      9. 8.7.9. NTILE
      10. 8.7.10. Stddev
      11. 8.7.11. Listagg
    8. 8.8. Performance Tuning
      1. 8.8.1. Execution Plans
      2. 8.8.2. Predicates
      3. 8.8.3. Indexes
    9. 8.9. Advanced topics
      1. 8.9.1. Dynamic SQL
      2. 8.9.2. Nesting Analytic Functions
      3. 8.9.3. Parallelism
      4. 8.9.4. PGA size
    10. 8.10. Organizational Behavior
    11. 8.11. Summary
  13. 9. The Model Clause
    1. 9.1. Spreadsheets
    2. 9.2. Inter-Row Referencing via the Model clause
      1. 9.2.1. Example Data
      2. 9.2.2. Anatomy of a Model Clause
      3. 9.2.3. Rules
    3. 9.3. Positional and Symbolic References
      1. 9.3.1. Positional Notation
      2. 9.3.2. Symbolic Notation
      3. 9.3.3. FOR Loops
    4. 9.4. Returning Updated Rows
    5. 9.5. Evaluation Order
      1. 9.5.1. Row Evaluation Order
      2. 9.5.2. Rule Evaluation Order
    6. 9.6. Aggregation
    7. 9.7. Iteration
      1. 9.7.1. An Example
      2. 9.7.2. PRESENTV and NULLs
    8. 9.8. Lookup Tables
    9. 9.9. NULLs
    10. 9.10. Performance Tuning with the Model Clause
      1. 9.10.1. Execution Plans
        1. 9.10.1.1. ACYCLIC
        2. 9.10.1.2. ACYCLIC FAST
        3. 9.10.1.3. CYCLIC
        4. 9.10.1.4. Sequential
      2. 9.10.2. Predicate Pushing
      3. 9.10.3. Materialized Views
      4. 9.10.4. Parallelism
      5. 9.10.5. Partitioning in Model Clause Execution
      6. 9.10.6. Indexes
    11. 9.11. Subquery Factoring
    12. 9.12. Summary
  14. 10. Subquery Factoring
    1. 10.1. Standard Usage
    2. 10.2. Optimizing SQL
      1. 10.2.1. Testing Execution Plans
      2. 10.2.2. Testing Over Multiple Executions
      3. 10.2.3. Testing the Effects of Query Changes
      4. 10.2.4. Seizing Other Optimization Opportunities
      5. 10.2.5. Applying Subquery Factoring to PL/SQL
    3. 10.3. Recursive Subqueries
      1. 10.3.1. A CONNECT BY Example
      2. 10.3.2. The Example Using an RSF
      3. 10.3.3. Restrictions on RSF
      4. 10.3.4. Differences from CONNECT BY
    4. 10.4. Duplicating CONNECT BY Functionality
      1. 10.4.1. The LEVEL Pseudocolumn
      2. 10.4.2. The SYS_CONNECT_BY_PATH Function
      3. 10.4.3. The CONNECT_BY_ROOT Operator
      4. 10.4.4. The CONNECT_BY_ISCYCLE Pseudocolumn and NOCYCLE Parameter
      5. 10.4.5. The CONNECT_BY_ISLEAF Pseudocolumn
    5. 10.5. Summary
  15. 11. Semi-joins and Anti-joins
    1. 11.1. Semi-joins
    2. 11.2. Semi-join Plans
    3. 11.3. Controlling Semi-join Plans
      1. 11.3.1. Controlling Semi-join Plans Using Hints
      2. 11.3.2. Controlling Semi-join Plans at the Instance Level
    4. 11.4. Semi-join Restrictions
    5. 11.5. Semi-join Requirements
    6. 11.6. Anti-joins
    7. 11.7. Anti-join Plans
    8. 11.8. Controlling Anti-join Plans
      1. 11.8.1. Controlling Anti-join Plans Using Hints
      2. 11.8.2. Controlling Anti-join Plans at the Instance Level
    9. 11.9. Anti-join Restrictions
    10. 11.10. Anti-join Requirements
    11. 11.11. Summary
  16. 12. Indexes
    1. 12.1. Understanding Indexes
      1. 12.1.1. When to use Indexes
      2. 12.1.2. Choice of Columns
      3. 12.1.3. The Null Issue
    2. 12.2. Index Structural Types
      1. 12.2.1. B-tree indexes
      2. 12.2.2. Bitmap Indexes
      3. 12.2.3. Index Organized Tables
    3. 12.3. Partitioned Indexes
      1. 12.3.1. Local Indexes
      2. 12.3.2. Global Indexes
      3. 12.3.3. Hash Partitioning vs. Range Partitioning
    4. 12.4. Solutions to Match Application Characteristics
      1. 12.4.1. Compressed Indexes
      2. 12.4.2. Function Based Indexes
      3. 12.4.3. Reverse Key Indexes
      4. 12.4.4. Descending Indexes
    5. 12.5. Solutions to Management Problems
      1. 12.5.1. Invisible Indexes
      2. 12.5.2. Virtual Indexes
      3. 12.5.3. Bitmap Join Indexes
    6. 12.6. Summary
  17. 13. Beyond the SELECT
    1. 13.1. INSERT
      1. 13.1.1. Direct Path Inserts
      2. 13.1.2. Multi-Table Inserts
      3. 13.1.3. Conditional Insert
      4. 13.1.4. DML Error Logging
    2. 13.2. UPDATE
    3. 13.3. DELETE
    4. 13.4. MERGE
      1. 13.4.1. Syntax and Usage
      2. 13.4.2. Performance Comparison
    5. 13.5. Summary
  18. 14. Transaction Processing
    1. 14.1. What is a Transaction?
    2. 14.2. ACID Properties of a Transaction
    3. 14.3. Transaction Isolation Levels
    4. 14.4. Multi-Version Read Consistency
    5. 14.5. Transaction Control Statements
      1. 14.5.1. Commit
      2. 14.5.2. Savepoint
      3. 14.5.3. Rollback
      4. 14.5.4. Set Transaction
      5. 14.5.5. Set Constraints
    6. 14.6. Grouping Operations into Transactions
    7. 14.7. The Order Entry Schema
    8. 14.8. The Active Transaction
    9. 14.9. Using Savepoints
    10. 14.10. Serializing Transactions
    11. 14.11. Isolating Transactions
    12. 14.12. Autonomous Transactions
    13. 14.13. Summary
  19. 15. Testing and Quality Assurance
    1. 15.1. Test Cases
    2. 15.2. Testing Methods
    3. 15.3. Unit Tests
    4. 15.4. Regression Tests
    5. 15.5. Schema Changes
    6. 15.6. Repeating the Unit Tests
    7. 15.7. Execution Plan Comparison
    8. 15.8. Instrumentation
    9. 15.9. Adding Instrumentation to Code
    10. 15.10. Testing for Performance
    11. 15.11. Testing to Destruction
    12. 15.12. Troubleshooting through Instrumentation
    13. 15.13. Summary
  20. 16. Plan Stability and Control
    1. 16.1. Plan Instability: Understanding The Problem
      1. 16.1.1. Changes to Statistics
      2. 16.1.2. Changes to the Environment
      3. 16.1.3. Changes to the SQL
      4. 16.1.4. Bind Variable Peeking
    2. 16.2. Identifying Plan Instability
      1. 16.2.1. Capturing Data on Currently-Running Queries
      2. 16.2.2. Reviewing the History of a Statement's Performance
      3. 16.2.3. Aggregating Statistics by Plan
      4. 16.2.4. Looking for Statistical Variance by Plan
      5. 16.2.5. Checking for Variations Around a Point in Time
    3. 16.3. Plan Control: Solving the Problem
      1. 16.3.1. Modifying Query Structure
      2. 16.3.2. Making Appropriate Use of Literals
      3. 16.3.3. Giving the Optimizer some Hints
    4. 16.4. Plan Control: Without Access to the Code
      1. 16.4.1. Option 1: Change the Statistics
      2. 16.4.2. Option 2: Change Database Parameters
      3. 16.4.3. Option 3: Add or Remove Access Paths
      4. 16.4.4. Option 4: Apply Hint Based Plan-Control Mechanisms
        1. 16.4.4.1. Plan Control: With Hint-Based Mechanisms
      5. 16.4.5. Outlines
      6. 16.4.6. SQL Profiles
      7. 16.4.7. SQL Plan Baselines
      8. 16.4.8. Hint Based Plan Control Mechanisms Wrap Up
    5. 16.5. Conclusion

Product information

  • Title: Pro Oracle SQL
  • Author(s): Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
  • Release date: November 2010
  • Publisher(s): Apress
  • ISBN: 9781430232285