Pro Oracle SQL Development: Best Practices for Writing Advanced Queries. 2 Ed

Table of Contents....5 About the Author....14 About the Technical Reviewer....15 Acknowledgments....16 Introduction....17 Chapter 1: Understand Relational Databases....23 History of Relational Databases....23 Relational Model and Why It Matters....26 History....26 Terminology....26 Simplicity....27 Sets and Tables....28 Problems Implementing a Relational Model....28 Relational Model and Why It Doesn’t Matter....29 The NULL Problem Isn’t a Problem....29 Column Order Is Important....31 Denormalization....31 All Rows Are Distinct....32 SQL Programming Language....32 History and Terminology....32 SQL Alternatives....33 Is SQL a Programming Language?....36 Different Database Types....36 Alternative Database Models....37 Different Oracle Databases (OLTP vs. DW)....38 Key Concepts....39 NULL....40 JOIN....42 Join Visualizations....42 Inner Join....44 Left and Right Outer Joins....45 Full Outer Join....46 Cross Join....47 Summary....48 Chapter 2: Create an Efficient Database Development Process....49 Shared Database vs. Private Database....49 Create an Infinite Number of Databases....50 Advantages of Private Databases....51 Create Private Databases: Local Installation....54 Create Private Databases: Other Options....55 Rapidly Drop and Recreate Schemas....57 Why Deploy Often?....57 How to Deploy Often?....58 SQL*Plus Installation Scripts....59 Comments....60 SQL*Plus Settings and Messages....60 Check Prerequisites....60 Drop Old Schemas....61 Scripts for Object Types....61 Grant to Roles....61 Validate the Schemas....61 SQL*Plus Patch Scripts....62 Control Schemas with Version-Controlled Text Files....63 Single Source of Truth....63 Load Objects from the Repository and File System....64 Create and Save Changes Manually....65 Empower Everyone....67 Power Imbalance....67 Transparency....68 Lower Barriers to Entry....69 Summary....69 Chapter 3: Increase Confidence and Knowledge with Testing....70 Build Confidence with Automated Tests....70 Fix Bugs Faster....70 Gain Confidence and Avoid Biases....71 Test-Driven Development....72 Create Useful Test Data....72 Create Large Test Data....74 Remove Test Data....75 How to Build Automated Tests....75 Build Knowledge with Minimal, Complete, and Verifiable Examples....78 Why Spend So Much Time Building Reproducible Test Cases?....78 Minimal....79 Complete....80 Verifiable....81 Sharing Tests....83 Avoiding the XY Problem....84 Oracle Performance Testing....84 Oracle Detective Kit....86 Data Dictionary Views....86 Dynamic Performance Views....88 Relational Tools for Inspecting Databases....91 Non-relational Tools for Inspecting Databases....93 Summary....94 Chapter 4: Find Reliable Sources....95 Places to Go....96 The Problems with Forums....96 The Problems with Static Websites....97 Read the Manual....97 The Manual Is Not Perfect....100 My Oracle Support....102 People to See....103 Summary....104 Chapter 5: Master the Entire Stack....105 Not Just Faster....105 Typing....107 Operating Systems and Supporting Programs....108 Operating Systems....108 Text Editors....108 Comparison Tools....109 Reporting Tools and Excel....110 SQL and PL/SQL....111 SQL*Plus....112 When We Should Use SQL*Plus....112 When We Should Not Use SQL*Plus....113 Integrated Development Environment....114 Learn an IDE....115 When Not to Use an IDE Feature....116 Oracle IDE Comparison....116 Worksheets, Notebooks, Snippets, Scripts, and Gists....117 Get Organized....117 Worksheets....118 Summary....121 Chapter 6: Build Sets with Inline Views and ANSI Join Syntax....122 Spaghetti Code from Nonstandard Syntax....122 Hard-to-Read Old Syntax....123 Hard-to-Debug Old Syntax....124 Accidental Cross Joins in Old Syntax....125 Nonstandard but Still Useful....126 Too Much Context....127 The Importance of Reducing Context....127 Avoid Correlated Subqueries....128 Avoid Common Table Expressions....129 Sets, Chunking, and Functional Programming to the Rescue....130 Sets....130 Chunking....131 Functional Programming....133 Inline Views....134 What Is an Inline View?....134 Inline Views Make Code Larger but Simpler....135 Simple Inline Views for a Large Example....136 ANSI Joins....138 Example....138 Summary....141 Chapter 7: Query the Database with Advanced SELECT Features....142 Operators, Functions, Expressions, and Conditions....143 Semantics....143 How to Know When We’re Missing Something....143 Precedence Rules....144 Simplify....145 CASE and DECODE....146 Joins....148 Partitioned Outer Joins....149 Lateral, Cross Apply, and Outer Apply....150 Equijoin or Non-equijoin....151 Semi-join or Anti-join....151 Self-Joins....152 Natural Joins and USING Considered Harmful....153 Sorting....155 Sorting Syntax....155 Sorting Performance, Resources, and Implicit Sorting....156 Set Operators....157 UNION and UNION ALL....159 INTERSECT and MINUS....159 Set Operator Complications....160 Advanced Grouping....161 ROLLUP, GROUP*, CUBE....162 LISTAGG....164 Advanced Aggregate Functions....165 Analytic Functions....166 Analytic Function Syntax....166 Analytic Function Examples....167 Regular Expressions....170 Regular Expression Syntax....170 Regular Expression Examples....171 Regular Expression Limitations....174 Row Limiting....175 Row Limiting Clause....175 ROWNUM....176 Analytic Function Row Limiting....177 Pivoting and Unpivoting....178 Old Pivot Syntax....179 New Pivot Syntax....181 UNPIVOT....182 Table References....184 Flashback....184 Sample....185 Partition Extension Clause....185 Common Table Expressions....186 Example....186 PL/SQL Common Table Expressions....188 Performance and Overuse....189 Recursive Queries....190 CONNECT BY Syntax....191 Recursive Common Table Expressions....192 XML....194 XMLType....194 DBMS_XMLGEN and Creating XML....195 XMLTABLE....196 XML Programming Languages....198 JSON....198 Store JSON in the Database....199 Create JSON Data....200 Query JSON....201 National Language Support....203 Character Sets....203 Length Semantics....204 NLS Comparing and Sorting....205 Display Formats....207 Summary....208 Chapter 8: Modify Data with Advanced DML....209 INSERT....210 UPDATE....211 DELETE....212 MERGE....214 Updatable Views....216 DML Hints....217 Error Logging....219 Returning....221 TRUNCATE....222 COMMIT, ROLLBACK, and SAVEPOINT....225 ALTER SYSTEM....227 ALTER SESSION....229 Input and Output....230 Useful PL/SQL Packages....233 Summary....236 Chapter 9: Improve the Database with Advanced Schema Objects....237 ALTER....237 Tables....239 Table Types....239 Table Properties....243 ALTER and DROP Table....248 Column Types and Properties....248 Constraints....251 Constraint Performance Impact....251 Altering Constraints....252 Constraint Exceptions....252 NOVALIDATE and Parallel Constraints....253 Other Constraints....255 Indexes....256 Index Concepts....256 Index Features....258 Rebuilding Indexes....261 Partitioning....262 Partitioning Concepts....262 Partitioning Features....265 Views....268 Creating Views....268 Expanding Views....269 Users....270 Sequences....272 Synonyms....274 Materialized Views....275 Materialized Views for Multi-table Constraints....275 Database Links....278 PL/SQL Objects....279 Other Schema Objects....280 Global Objects....281 GRANT and REVOKE....282 Summary....283 Chapter 10: Optimize the Database with Oracle Architecture....284 Storage Structures....284 Column Values....285 Row Pieces....286 Blocks and Row-Level Locking....287 Extents....289 Segments....289 Data Files....291 Tablespaces....292 Automatic Storage Management....293 Wasted Space....293 Redo....294 Redo in Theory....295 Redo in Practice....295 Undo and Multiversion Read Consistency....297 Undo for Rollback....297 Undo for Multiversion Consistency....299 Temporary Tablespace....301 Memory....302 Caches....303 Multitenant....306 Database Types....307 Summary....310 Chapter 11: Stop Coding and Start Writing....311 The Hypocrisy of Examples....312 Comments....313 Comment Styles....313 Comment Mechanics....314 Comment ASCII Art....316 Choose Good Names....317 Name Styles....317 Avoid Quoted Identifiers....318 Name Length and Changes....318 Whitespace....319 Make Bugs Obvious....321 Fail Fast....321 Avoid Pokémon Exception Handling....321 Use Bad Names and Weird Values....323 Use Fragile SQL....324 The Path to Writing Good SQL....325 Summary....326 Chapter 12: Write Large SQL Statements....327 Imperative Programming Size Limits Do Not Apply....327 One Large SQL Statement vs. Multiple Small SQL Statements....328 Performance Risks of Large SQL Statements....330 Large SQL Parsing Problems....330 Large SQL Increases Optimizer Risks....331 Large SQL Resource Consumption Problems....334 Performance Benefits of Large SQL Statements....334 Large SQL Improves Clarity....334 Large SQL Increases Optimizer Opportunities....335 Large SQL Reduces Input/Output....336 Large SQL Reduces Context Switches....336 Large SQL Improves Parallelism....337 Reading and Debugging Large SQL Statements....339 Inside Out....339 Navigating Inline Views....341 Summary....343 Chapter 13: Write Beautiful SQL Statements....344 How to Measure Code Complexity....345 Avoid Unnecessary Aliases....346 Prefixes and Suffixes....347 SQL Object and Column Names....348 PL/SQL Variable Names....349 Referencing Tables and Columns....349 Avoid Abbreviations....350 Use Tabs for Left Alignment....351 Avoid Code Formatters....354 Lowercase....355 Summary....356 Chapter 14: Use SQL More Often with Basic Dynamic SQL....357 When to Use Dynamic SQL....357 Run DDL....357 Unknown Until Run Time....358 Simplify Privileges....360 Rule Engines....361 When Not to Use Dynamic SQL....361 Basic Features....362 Bind Variables for Performance and Security....363 How to Simplify String Concatenation....365 Multiline Strings....365 Alternative Quoting Mechanism....366 Templating....368 Code Generation, Not Generic Code....369 Summary....370 Chapter 15: Avoid Anti-patterns....371 Avoid the Second System Syndrome and Rewriting from Scratch....371 Avoid the Stringly Typed Entity–Attribute–Value Model....373 EAV Pros and Cons....373 Never Use the Wrong Type....374 Subtle Conversion Bugs in Oracle SQL....375 Avoid Soft Coding....378 Avoid Object-Relational Tables....379 Avoid Java in the Database....380 Java Is Not Always Available....380 Java Does Not Fit Perfectly....381 SQL and PL/SQL Are Almost Always Better Choices....381 Avoid TO_DATE....382 Avoid String-to-Date Conversion....382 Use DATE, TIMESTAMP, and INTERVAL Literals....383 Avoid CURSOR....385 Avoid Custom SQL Parsing....386 Avoid Automating Everything....388 Avoid Cargo Cult Syntax....389 Avoid Undocumented Features....389 Avoid Deprecated Features....390 Avoid Simplistic Explanations for Generic Errors....391 Dead Processes....391 Deadlocks....392 Top of the Error Stack....393 Avoid Unnecessarily Small Parameters....394 Avoid Conflating Planning with Premature Optimization....395 Anti-patterns Discussed in Other Chapters....395 Summary....396 Chapter 16: Understand SQL Performance with Algorithm Analysis....397 Algorithm Analysis Introduction....398 O(1/N): Batching to Reduce Overhead....401 O(1): Hashing and Other Operations....403 How Hashing Works....404 Hash Partitioning....405 Hash Clusters....406 Hash Joins....407 Other....408 O(LOG(N)): Index Access....408 1/((1-P)+P/N): Amdahl’s Law....410 O(N): Full Table Scans, Other Operations....412 O(N*LOG(N)): Full Table Scan vs. Index Access, Sorting, Joining, Global vs. Local Index, Gathering Statistics....412 Full Table Scan vs. Index Access....413 Sorting....414 Joining....416 Global vs. Local Index....419 Gathering Optimizer Statistics....420 O(N^2): Cross Joins, Nested Loops, Other Operations....422 O(N!): Join Order....424 O(∞): The Optimizer....424 Summary....425 Chapter 17: Understand SQL Tuning Theories....426 Managing User Expectations....426 Performance Tuning State of Mind....427 Performance Tuning Is Not Debugging....428 Motivated Troubleshooting....428 Different Approaches....429 Why Not Database Tuning?....431 Declarative Programming (Why Execution Plans Are Important)....431 Declarative Quirks....432 Execution Plans....432 Operations (What Execution Plan Decisions Are Available)....434 Operation Details....434 Execution Plans and Recursive SQL....435 Why Operations Matter....435 First Operations....436 Joining....437 Table Access....438 Index Access....439 Grouping and Sorting....441 Set Operators....441 Optimizer Statistics....442 Parallel....442 Partition....445 Filter....446 Other....448 Cardinality and Optimizer Statistics (Building Execution Plans I)....449 Cardinality Is Important....449 Cardinality Differences....451 Cost Doesn’t Matter....452 Optimizer Statistics....452 Optimizer Statistics Example....454 Transformations and Dynamic Optimizations (Building Execution Plans II)....457 Transformations....457 Adaptive Cursor Sharing and Adaptive Statistics....459 Adaptive Query Plans....460 Summary....464 Chapter 18: Improve SQL Performance....465 Application Tuning: Logging and Profiling....465 Logging....466 Profiling: DBMS_PROFILER....467 Profiling: DBMS_HPROF....469 Application Tuning Through Batching....470 Installation and Patch Scripts....471 OLTP Applications....473 Data Warehouses....474 Database Tuning....475 Measure Database Performance....475 Automatic Workload Repository (AWR)....479 Active Session History (ASH)....481 Automatic Database Diagnostic Monitor (ADDM)....482 Advisors....484 Automatic Indexing....485 Other Tools....489 SQL Tuning: Find Slow SQL....489 Get Organized....490 Slow Is Based on DB Time....490 Find Currently Running Slow SQL....491 Find Historically Slow SQL....492 SQL Tuning: Find Plans....493 Graphical Plans Considered Harmful....493 Text Is Best....495 DBMS_XPLAN Functions....497 DBMS_XPLAN FORMAT Parameter....497 “Note” Section....499 Other Ways to Get Execution Plans....500 SQL Tuning: Find Actual Times and Cardinalities for Operations....500 GATHER_PLAN_STATISTICS....501 Real-Time SQL Monitor Reports (Text)....505 Real-Time SQL Monitor Reports (Active)....508 Degree of Parallelism....509 What to Look for in Execution Plans....511 SQL Tuning: Changing Execution Plans....512 Changing Execution Plans....513 Hints....515 SQL Profile Example....517 SQL Tuning: Gathering Optimizer Statistics....519 Manual Statistics....519 Automatic Statistics....521 Other Statistics....522 Summary....523 Chapter 19: Solve Challenging Problems with Arcane SQL Features....525 Oracle vs. the Unix Philosophy....525 MODEL....526 Row Pattern Matching....530 Any Types....531 APEX....533 Oracle Text....534 Multilingual Engine....537 Spatial....539 Other Features....541 Machine Learning....541 OLAP....541 Property Graph....542 Virtual Private Database....542 Database In-Memory....542 Advanced Compression....543 Converged Database and Connections....543 Summary....543 Chapter 20: Use SQL More Often with Advanced Dynamic SQL....544 Parsing....544 PL/Scope....545 PLSQL_LEXER....546 ANTLR....547 DBMS_SQL....549 DBMS_XMLGEN....550 PL/SQL Common Table Expressions....552 Method4 Dynamic SQL....552 Polymorphic Table Functions....554 SQL Macros....556 Summary....559 Chapter 21: Level Up Your Skills with PL/SQL....560 Is PL/SQL Worth Mastering?....560 Create a PL/SQL Playground....561 PL/SQL Integration Features....562 Tips for Packaging Code....563 Session Data....563 Transactions I: COMMIT, ROLLBACK, and SAVEPOINT....566 Transactions II: Implicit Cursor Attributes....568 Transactions III: Row-Level Locking....569 Transactions IV: Isolation and Consistency....571 Simple Variables....573 Cursors....575 Records....579 Collections....581 Functions....584 Table Functions....587 Pipelined Functions....588 Parallel Pipelined Functions....589 Autonomous Transactions for DML and DDL....590 Autonomous Transactions for Logging....591 Definer’s Rights vs. Invoker’s Rights....593 Triggers....594 Conditional Compilation....599 Other PL/SQL Features....600 Start Teaching and Creating....601 Teach Others....601 Create Open Source Projects....601 Appendix A: SQL Style Guide Cheat Sheet....603 Appendix B: Computer Science Topics....605 Index....607