Pro Oracle SQL Development : Best Practices for Writing Advanced Queries

Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Part I: Learn How to Learn Chapter 1: Understand Relational Databases History of Relational Databases Relational Model and Why It Matters History Terminology Simplicity Sets and Tables Problems Implementing a Relational Model Relational Model and Why It Doesn’t Matter The NULL Problem Isn’t a Problem Column Order Is Important Denormalization All Rows Are Distinct SQL Programming Language History and Terminology SQL Alternatives Is SQL a Programming Language? Different Database Types Alternative Database Models Different Oracle Databases (OLTP vs. DW) Key Concepts NULL JOIN Join Visualizations Inner Join Left and Right Outer Joins Full Outer Join Cross Join Summary Chapter 2: Create an Efficient Database Development Process Shared Database vs. Private Database Create an Infinite Number of Databases Advantages of Private Databases Create Private Databases: Local Installation Create Private Databases: Other Options Rapidly Drop and Recreate Schemas Why Deploy Often? How to Deploy Often? SQL*Plus Installation Scripts Comments SQL*Plus Settings and Messages Check Prerequisites Drop Old Schemas Scripts for Object Types Grant to Roles Validate the Schemas SQL*Plus Patch Scripts Control Schemas with Version-Controlled Text Files Single Source of Truth Load Objects from the Repository and File System Create and Save Changes Manually Empower Everyone Power Imbalance Transparency Lower Barriers to Entry Summary Chapter 3: Increase Confidence and Knowledge with Testing Build Confidence with Automated Tests Fix Bugs Faster Gain Confidence and Avoid Biases Test-Driven Development Create Useful Test Data Create Large Test Data Remove Test Data How to Build Automated Tests Build Knowledge with Minimal, Complete, and Verifiable Examples Why Spend So Much Time Building Reproducible Test Cases? Minimal Complete Verifiable Sharing Tests Avoiding the XY Problem Oracle Performance Testing Oracle Detective Kit Data Dictionary Views Dynamic Performance Views Relational Tools for Inspecting Databases Non-relational Tools for Inspecting Databases Summary Chapter 4: Find Reliable Sources Places to Go The Problems with Forums The Problems with Static Websites Read the Manual The Manual Is Not Perfect My Oracle Support People to See Summary Chapter 5: Master the Entire Stack Not Just Faster Typing Operating Systems and Supporting Programs Operating Systems Text Editors Comparison Tools Reporting Tools and Excel SQL and PL/SQL SQL*Plus When We Should Use SQL*Plus When We Should Not Use SQL*Plus Integrated Development Environment Learn an IDE When Not to Use an IDE Feature Oracle IDE Comparison Worksheets, Notebooks, Snippets, Scripts, and Gists Get Organized Worksheets Summary Part II: Write Powerful SQL with Sets and Advanced Features Chapter 6: Build Sets with Inline Views and ANSI Join Syntax Spaghetti Code from Nonstandard Syntax Hard-to-Read Old Syntax Hard-to-Debug Old Syntax Accidental Cross Joins in Old Syntax Nonstandard but Still Useful Too Much Context The Importance of Reducing Context Avoid Correlated Subqueries Avoid Common Table Expressions Sets, Chunking, and Functional Programming to the Rescue Sets Chunking Functional Programming Inline Views What Is an Inline View? Inline Views Make Code Larger but Simpler Simple Inline Views for a Large Example ANSI Joins Example Summary Chapter 7: Query the Database with Advanced SELECT Features Operators, Functions, Expressions, and Conditions Semantics How to Know When We’re Missing Something Precedence Rules Simplify CASE and DECODE Joins Partitioned Outer Joins Lateral, Cross Apply, and Outer Apply Equijoin or Non-equijoin Semi-join or Anti-join Self-Joins Natural Joins and USING Considered Harmful Sorting Sorting Syntax Sorting Performance, Resources, and Implicit Sorting Set Operators UNION and UNION ALL INTERSECT and MINUS Set Operator Complications Advanced Grouping ROLLUP, GROUP*, CUBE LISTAGG Advanced Aggregate Functions Analytic Functions Analytic Function Syntax Analytic Function Examples Regular Expressions Regular Expression Syntax Regular Expression Examples Regular Expression Limitations Row Limiting Row Limiting Clause ROWNUM Analytic Function Row Limiting Pivoting and Unpivoting Old Pivot Syntax New Pivot Syntax UNPIVOT Table References Flashback Sample Partition Extension Clause Common Table Expressions Example PL/SQL Common Table Expressions Performance and Overuse Recursive Queries CONNECT BY Syntax Recursive Common Table Expressions XML XMLType DBMS_XMLGEN and Creating XML XMLTABLE XML Programming Languages JSON Store JSON in the Database Create JSON Data Query JSON National Language Support Character Sets Length Semantics NLS Comparing and Sorting Display Formats Summary Chapter 8: Modify Data with Advanced DML INSERT UPDATE DELETE MERGE Updatable Views DML Hints Error Logging Returning TRUNCATE COMMIT, ROLLBACK, and SAVEPOINT ALTER SYSTEM ALTER SESSION Input and Output Useful PL/SQL Packages Summary Chapter 9: Improve the Database with Advanced Schema Objects ALTER Tables Table Types Table Properties ALTER and DROP Table Column Types and Properties Constraints Constraint Performance Impact Altering Constraints Constraint Exceptions NOVALIDATE and Parallel Constraints Other Constraints Indexes Index Concepts Index Features Rebuilding Indexes Partitioning Partitioning Concepts Partitioning Features Views Creating Views Expanding Views Users Sequences Synonyms Materialized Views Materialized Views for Multi-table Constraints Database Links PL/SQL Objects Other Schema Objects Global Objects GRANT and REVOKE Summary Chapter 10: Optimize the Database with Oracle Architecture Storage Structures Column Values Row Pieces Blocks and Row-Level Locking Extents Segments Data Files Tablespaces Automatic Storage Management Wasted Space Redo Redo in Theory Redo in Practice Undo and Multiversion Read Consistency Undo for Rollback Undo for Multiversion Consistency Temporary Tablespace Memory Caches Multitenant Database Types Summary Part III: Write Elegant SQL with Patterns and Styles Chapter 11: Stop Coding and Start Writing The Hypocrisy of Examples Comments Comment Styles Comment Mechanics Comment ASCII Art Choose Good Names Name Styles Avoid Quoted Identifiers Name Length and Changes Whitespace Make Bugs Obvious Fail Fast Avoid Pokémon Exception Handling Use Bad Names and Weird Values Use Fragile SQL The Path to Writing Good SQL Summary Chapter 12: Write Large SQL Statements Imperative Programming Size Limits Do Not Apply One Large SQL Statement vs. Multiple Small SQL Statements Performance Risks of Large SQL Statements Large SQL Parsing Problems Large SQL Increases Optimizer Risks Large SQL Resource Consumption Problems Performance Benefits of Large SQL Statements Large SQL Improves Clarity Large SQL Increases Optimizer Opportunities Large SQL Reduces Input/Output Large SQL Reduces Context Switches Large SQL Improves Parallelism Reading and Debugging Large SQL Statements Inside Out Navigating Inline Views Summary Chapter 13: Write Beautiful SQL Statements How to Measure Code Complexity Avoid Unnecessary Aliases Prefixes and Suffixes SQL Object and Column Names PL/SQL Variable Names Referencing Tables and Columns Avoid Abbreviations Use Tabs for Left Alignment Avoid Code Formatters Lowercase Summary Chapter 14: Use SQL More Often with Basic Dynamic SQL When to Use Dynamic SQL Run DDL Unknown Until Run Time Simplify Privileges Rule Engines When Not to Use Dynamic SQL Basic Features Bind Variables for Performance and Security How to Simplify String Concatenation Multiline Strings Alternative Quoting Mechanism Templating Code Generation, Not Generic Code Summary Chapter 15: Avoid Anti-patterns Avoid the Second System Syndrome and Rewriting from Scratch Avoid the Stringly Typed Entity–Attribute–Value Model EAV Pros and Cons Never Use the Wrong Type Subtle Conversion Bugs in Oracle SQL Avoid Soft Coding Avoid Object-Relational Tables Avoid Java in the Database Java Is Not Always Available Java Does Not Fit Perfectly SQL and PL/SQL Are Almost Always Better Choices Avoid TO_DATE Avoid String-to-Date Conversion Use DATE, TIMESTAMP, and INTERVAL Literals Avoid CURSOR Avoid Custom SQL Parsing Avoid Automating Everything Avoid Cargo Cult Syntax Avoid Undocumented Features Avoid Deprecated Features Avoid Simplistic Explanations for Generic Errors Dead Processes Deadlocks Top of the Error Stack Avoid Unnecessarily Small Parameters Avoid Conflating Planning with Premature Optimization Anti-patterns Discussed in Other Chapters Summary Part IV: Improve SQL Performance Chapter 16: Understand SQL Performance with Algorithm Analysis Algorithm Analysis Introduction O(1/N): Batching to Reduce Overhead O(1): Hashing and Other Operations How Hashing Works Hash Partitioning Hash Clusters Hash Joins Other O(LOG(N)): Index Access 1/((1-P)+P/N): Amdahl’s Law O(N): Full Table Scans, Other Operations O(N*LOG(N)): Full Table Scan vs. Index Access, Sorting, Joining, Global vs. Local Index, Gathering Statistics Full Table Scan vs. Index Access Sorting Joining Global vs. Local Index Gathering Optimizer Statistics O(N^2): Cross Joins, Nested Loops, Other Operations O(N!): Join Order O(∞): The Optimizer Summary Chapter 17: Understand SQL Tuning Theories Managing User Expectations Performance Tuning State of Mind Performance Tuning Is Not Debugging Motivated Troubleshooting Different Approaches Why Not Database Tuning? Declarative Programming (Why Execution Plans Are Important) Declarative Quirks Execution Plans Operations (What Execution Plan Decisions Are Available) Operation Details Execution Plans and Recursive SQL Why Operations Matter First Operations Joining Table Access Index Access Grouping and Sorting Set Operators Optimizer Statistics Parallel Partition Filter Other Cardinality and Optimizer Statistics (Building Execution Plans I) Cardinality Is Important Cardinality Differences Cost Doesn’t Matter Optimizer Statistics Optimizer Statistics Example Transformations and Dynamic Optimizations (Building Execution Plans II) Transformations Adaptive Cursor Sharing and Adaptive Statistics Adaptive Query Plans Summary Chapter 18: Improve SQL Performance Application Tuning: Logging and Profiling Logging Profiling: DBMS_PROFILER Profiling: DBMS_HPROF Application Tuning Through Batching Installation and Patch Scripts OLTP Applications Data Warehouses Database Tuning Measure Database Performance Automatic Workload Repository (AWR) Active Session History (ASH) Automatic Database Diagnostic Monitor (ADDM) Advisors Automatic Indexing Other Tools SQL Tuning: Find Slow SQL Get Organized Slow Is Based on DB Time Find Currently Running Slow SQL Find Historically Slow SQL SQL Tuning: Find Plans Graphical Plans Considered Harmful Text Is Best DBMS_XPLAN Functions DBMS_XPLAN FORMAT Parameter “Note” Section Other Ways to Get Execution Plans SQL Tuning: Find Actual Times and Cardinalities for Operations GATHER_PLAN_STATISTICS Real-Time SQL Monitor Reports (Text) Real-Time SQL Monitor Reports (Active) Degree of Parallelism What to Look for in Execution Plans SQL Tuning: Changing Execution Plans Changing Execution Plans Hints SQL Profile Example SQL Tuning: Gathering Optimizer Statistics Manual Statistics Automatic Statistics Other Statistics Summary Part V: Solve Anything with Oracle SQL Chapter 19: Solve Challenging Problems with Arcane SQL Features Oracle vs. the Unix Philosophy MODEL Row Pattern Matching Any Types APEX Oracle Text Multilingual Engine Spatial Other Features Machine Learning OLAP Property Graph Virtual Private Database Database In-Memory Advanced Compression Converged Database and Connections Summary Chapter 20: Use SQL More Often with Advanced Dynamic SQL Parsing PL/Scope PLSQL_LEXER ANTLR DBMS_SQL DBMS_XMLGEN PL/SQL Common Table Expressions Method4 Dynamic SQL Polymorphic Table Functions SQL Macros Summary Chapter 21: Level Up Your Skills with PL/SQL Is PL/SQL Worth Mastering? Create a PL/SQL Playground PL/SQL Integration Features Tips for Packaging Code Session Data Transactions I: COMMIT, ROLLBACK, and SAVEPOINT Transactions II: Implicit Cursor Attributes Transactions III: Row-Level Locking Transactions IV: Isolation and Consistency Simple Variables Cursors Records Collections Functions Table Functions Pipelined Functions Parallel Pipelined Functions Autonomous Transactions for DML and DDL Autonomous Transactions for Logging Definer’s Rights vs. Invoker’s Rights Triggers Conditional Compilation Other PL/SQL Features Start Teaching and Creating Teach Others Create Open Source Projects Part VI: Appendixes Appendix A: SQL Style Guide Cheat Sheet Appendix B: Computer Science Topics Index Write SQL statements that are more powerful, simpler, and faster using the advanced features of Oracle SQL. This updated second edition includes the newest advanced features: improved data structures (such as more JSON support and more table options), improved automated processes (such as automatic indexing), and improved SQL language extensions (such as polymorphic table functions, SQL macros, and the multilingual engine). Pro Oracle SQL Development is for anyone who already knows Oracle SQL and is ready to take their skills to the next level. This book provides a clearer way of thinking about SQL by building sets, and it provides practical advice for using complex features while avoiding anti-patterns that lead to poor performance and wrong results. Relevant theories, real-world best practices, and style guidelines help you get the most out of Oracle SQL. Many developers, testers, analysts, and administrators use Oracle databases frequently, but their queries are limited because they do not take advantage of Oracle’s advanced features. This book inspires you to achieve more with your Oracle SQL statements by creating your own style for writing simple, yet powerful, SQL. It teaches you how to think about and solve performance problems in Oracle SQL, and it covers enough advanced topics to put you on the path to becoming an Oracle expert. What You'll Learn Solve challenging problems with declarative SQL instead of procedural languages Write SQL statements that are large and powerful, but also elegant and fast Create development environments that are simple, scalable, and conducive to learning Visualize and understand SQL more intuitively Apply advanced syntax, objects, and architecture Avoid SQL anti-patterns that accumulate technical debt Tune SQL statements with multiple strategies that can significantly improve performance Who This Book Is For Developers, testers, analysts, and administrators who want to harness the full power of Oracle SQL to solve their problems as simply and as quickly as possible; traditional database professionals looking for new ways of thinking about the language they have used for so long; and modern full stack developers who need an explanation of how a database can be much more than simply a place to store data