Sumário Itens Encontrados: 528Part I: Learn How to Learn 1Chapter 1: Understand Relational Databases 3History of Relational Databases 3Relational Model and Why It Matters 6History 6Terminology 7Simplicity 7Sets and Tables 8Problems Implementing a Relational Model 8Relational Model and Why It Doesnât Matter 9The NULL Problem Isnât a Problem 9Column Order Matters 11Denormalization 11All Rows Are Distinct 12SQL Programming Language 12History and Terminology 12SQL Alternatives 13Is SQL a Programming Language? 16viDifferent Database Types 17Alternative Database Models 17Different Oracle Databases (OLTP vs. DW) 18Key Concepts 19NULL 20JOIN 22Summary 28Chapter 2: Create an Efficient Database Development Process 29Shared Database vs. Private Database 29Create an Infinite Number of Databases 30Advantages of Private Databases 31How to Implement Private Databases 34Rapidly Drop and Recreate Schemas 36Why Deploy Often? 36How to Deploy Often? 37SQL*Plus Installation Scripts 38SQL*Plus Patch Scripts 40Control and Integrate Schemas with Version-ControlledText Files 42Single Source of Truth 42Load Objects from the Repository and File System 43Create and Save Changes Manually 44Empower Everyone 46Power Imbalance Between Developers and Administrators 47Improve Communication 48Transparency 49Lower Barriers to Entry 49Summary 50Chapter 3: Increase Confidence and Knowledge with Testing 51Build Confidence with Automated Tests 51Fix Bugs Faster 51viiGain Confidence, Avoid Biases 52Test-Driven Development 53Create Useful Test Data 53Create Large Test Data 55Remove Test Data 56How to Build Automated Tests 56Build Knowledge with Minimal, Complete, and Verifiable Examples 59Why Spend So Much Time Building Reproducible Test Cases? 59Minimal 60Complete 61Verifiable 62Sharing Tests 64Avoiding the XY Problem 65Oracle Performance Testing 65Oracle Detective Kit 67Data Dictionary Views 67Dynamic Performance Views 69Relational Tools for Inspecting Databases 72Non-relational Tools for Inspecting Databases 74Summary 75Chapter 4: Find Reliable Sources 77Places to Go 78The Problems with Forums 78The Problems with Static Websites 79Read the Manual 79The Manual Is Not Perfect 82My Oracle Support 83People to See 84Summary 85Chapter 5: Master the Entire Stack 87Not Just Faster 87Typing 89Operating Systems and Supporting Programs 89Operating Systems 90Text Editors 90Comparison Tools 91Reporting Tools and Excel 92SQL and PL/SQL 93SQL*Plus 94When We Should Use SQL*Plus 94When We Should Not Use SQL*Plus 95Integrated Development Environment 96Learn an IDE 97When Not to Use an IDE Feature 98Oracle IDE Comparison 98Worksheets, Notebooks, Snippets, Scripts, and Gists 99Get Organized 99Worksheets 100Summary 103Part II: Write Powerful SQL with Sets and Advanced Features 105Chapter 6: Build Sets with Inline Views and ANSI Join Syntax 107Spaghetti Code from Nonstandard Syntax 107Hard to Read Old Syntax 108Hard to Debug Old Syntax 109Accidental Cross Joins in Old Syntax 110Nonstandard but Still Useful 111Too Much Context 112The Importance of Limiting Context 112ixAvoid Correlated Subqueries 113Avoid Common Table Expressions 114Sets, Chunking, and Functional Programming to the Rescue 115Sets 115Chunking 116Functional Programming 118Inline Views 119What Is an Inline View? 119Inline Views Make Code Bigger but Simpler 120Simple Inline Views for a Large Example 121ANSI Joins 122Example 123Summary 126Chapter 7: Query the Database with Advanced SELECT Features 127Operators, Functions, Expressions, and Conditions 128Semantics 128How to Know When Weâre Missing Something 128Precedence Rules 129Simplify 130CASE and DECODE 131Joins 133Partitioned Outer Joins 134Lateral, Cross Apply, and Outer Apply 136Equijoin or Non-equijoin 136Semi-join or Anti-join 136Self-joins 138Natural Joins and USING Considered Harmful 139Sorting 140Sorting Syntax 140Sorting Performance, Resources, and Implicit Sorting 142xSet Operators 142UNION and UNION ALL 143INTERSECT and MINUS 144Set Operator Complications 145Advanced Grouping 146ROLLUP, GROUP*, CUBE 146LISTAGG 148Advanced Aggregate Functions 149Analytic Functions 150Analytic Function Syntax 150Analytic Function Examples 151Regular Expressions 154Regular Expression Syntax 154Regular Expression Examples 155Regular Expression Limitations 158Row Limiting 159Row Limiting Clause 159ROWNUM 160Analytic Function Row Limiting 161Pivoting and Unpivoting 162Old Pivot Syntax 163New Pivot Syntax 164UNPIVOT 165Table References 168Flashback 168Sample 169Partition Extension Clause 169Common Table Expressions 170Example 170PL/SQL Common Table Expressions 172xiPerformance and Over-use 173Recursive Queries 174CONNECT BY Syntax 174Recursive Common Table Expressions 176XML 177XMLType 177DBMS_XMLGEN and Creating XML 179XMLTABLE 180XML Programming Languages 181JSON 182Build and Store JSON in the Database 182Querying JSON 184National Language Support 185Character Sets 186Length Semantics 187NLS Comparing and Sorting 188Display Formats 189Summary 190Chapter 8: Modify Data with Advanced DML 191INSERT 192UPDATE 193DELETE 194MERGE 196Updatable Views 198DML Hints 199Error Logging 201Returning 203TRUNCATE 204COMMIT, ROLLBACK, and SAVEPOINT 207ALTER SYSTEM 209ALTER SESSION 211Input and Output 212Useful PL/SQL Packages 214Summary 217Chapter 9: Improve the Database with Advanced Oracle Schema Objects 219ALTER 219Tables 221Table Types 221Table Properties 225ALTER and DROP Table 229Column Types and Properties 229Constraints 231Constraint Performance Impact 232Altering Constraints 232Constraint Exceptions 233NOVALIDATE and Parallel Constraints 234Other Constraints 236Indexes 237Index Concepts 237Index Features 239Rebuilding Indexes 242Partitioning 243Partitioning Concepts 243Partitioning Features 246Views 248Creating Views 248Expanding Views 249Users 250Sequences 252Synonyms 254Materialized Views 254Materialized Views for Multi-table Constraints 255Database Links 257PL/SQL Objects 259Other Schema Objects 260Global Objects 260GRANT and REVOKE 261Summary 263Chapter 10: Optimize the Database with Oracle Architecture 265Redo 265Redo in Theory 266Redo in Practice 266Undo and Multiversion Read Consistency 268Undo for Rollback 268Undo for Multiversion Consistency 270Storage Structures 272Column Values 273Row Pieces 274Blocks and Row-Level Locking 275Extents 277Segments 277Data Files 278Tablespaces 279Automatic Storage Management 280Wasted Space 281Temporary Tablespace 282Memory 283Caches 285Database Types 287Summary 289xivPart III: Write Elegant SQL with Patterns and Styles 291Chapter 11: Stop Coding and Start Writing 293The Hypocrisy of Examples 294Comments 295Comment Styles 295Comment Mechanics 296Comment ASCII Art 297Choose Good Names 298Name Styles 298Avoid Quoted Identifiers 299Name Length and Changes 300Whitespace 301Make Bugs Obvious 302Fail Fast 303Avoid Pokémon Exception Handling 303Use Bad Names and Weird Values 305Use Fragile SQL 306The Path to Writing Good SQL 307Summary 308Chapter 12: Write Large SQL Statements 309Imperative Programming Size Limits Do Not Apply 309One Large SQL Statement vs. Multiple Small SQL Statements 310Performance Risks of Large SQL Statements 312Large SQL Parsing Problems 312Large SQL Increases Optimizer Risks 313Large SQL Resource Consumption Problems 315Performance Benefits of Large SQL Statements 316Large SQL Improves Clarity 316Large SQL Increases Optimizer Opportunities 316Large SQL Reduces Input/Output 318Large SQL Reduces Context Switches 318Large SQL Improves Parallelism 319Reading and Debugging Large SQL Statements 321Inside Out 321Navigating Inline Views 322Summary 325Chapter 13: Write Beautiful SQL Statements 327How to Measure Code Complexity 328Avoid Unnecessary Aliases 329Prefixes and Suffixes 330Object and Variable Names 331Referencing Tables and Columns 331Avoid Abbreviations 332Use Tabs for Left Alignment 333Avoid Code Formatters 336Lower Case 337Summary 338Chapter 14: Use SQL More Often with Basic Dynamic SQL 339When to Use Dynamic SQL 339Running DDL 340Unknown Until Run Time 341Simplify Privileges 342Rule Engines 343When Not to Use Dynamic SQL 343Basic Features 344Bind Variables for Performance and Security 345How to Simplify String Concatenation 347Multiline Strings 347Alternative Quoting Mechanism 348Templating 350Code Generation, Not Generic Code 351Summary 353Chapter 15: Avoid Anti-Patterns 355Avoid Second System Syndrome and Rewriting from Scratch 355Avoid Stringly Typed EntityâAttributeâValue Model 357EAV Pros and Cons 357Never Use the Wrong Type 358Subtle Conversion Bugs in Oracle SQL 359Avoid Soft Coding 361Avoid Object-Relational Tables 362Avoid Java in the Database 364Java Is Not Always Available 364Java Does Not Fit Perfectly 364SQL and PL/SQL Are Almost Always Better Choices 365Avoid TO_DATE 365Avoid String-to-Date Conversion 366Use DATE, TIMESTAMP, and INTERVAL Literals 367Avoid CURSOR 368Avoid Custom SQL Parsing 370Avoid Automating Everything 372Avoid Cargo Cult Syntax 373Avoid Undocumented Features 373Avoid Deprecated Features 374Avoid Simplistic Explanations for Generic Errors 374Dead Processes 375Deadlocks 375Top of the Error Stack 376Avoid Unnecessarily Small Parameters 377Anti-Patterns Discussed in Other Chapters 378Summary 378Part IV: Improve SQL Performance 379Chapter 16: Understand SQL Performance with Algorithm Analysis 381Algorithm Analysis Introduction 382O(1/N) â Batching to Reduce Overhead 386O(1) â Hashing, Other Operations 388How Hashing Works 388Hash Partitioning 390Hash Clusters 391Hash Joins 392Other 392O(LOG(N)) â Index Access 3931 /((1-P)+P/N) â Amdahlâs Law 395O(N) â Full Table Scans, Other Operations 397O(N*LOG(N)) â Full Table Scan vs. Index, Sorting, Joining,Global vs. Local Index, Gathering Statistics 398Full Table Scan vs. Index 399Sorting 400Joining 401Global vs. Local Index 404Gathering Optimizer Statistics 404O(N^2) â Cross Joins, Nested Loops, Other Operations 407O(N!) â Join Order 409O(?) â The Optimizer 409Summary 410Chapter 17: Understand SQL Tuning Theories 411Managing User Expectations 411Performance Tuning State of Mind 412Performance Tuning Is Not Debugging 413Motivated Troubleshooting 413Different Approaches 414Why Not Database Tuning? 416Declarative Programming (Why Execution Plans Are Important) 416Declarative Quirks 416Execution Plans 417Operations (What Execution Plan Decisions Are Available) 418Operation Details 419Execution Plans and Recursive SQL 419Why Operations Matter 420First Operations 421Joining 421Table Access 423Index Access 424Grouping and Sorting 425Set Operators 425Optimizer Statistics 426Parallel 426Partition 429Filter 430Other 431Cardinality and Optimizer Statistics (Building Execution Plans I) 432Cardinality Is Important 433Cardinality Differences 435Cost Doesnât Matter 436Optimizer Statistics 436Optimizer Statistics Example 438Transformations and Dynamic Optimizations (Building Execution Plans II) 440Transformations 440Adaptive Cursor Sharing and Adaptive Statistics 442Adaptive Query Plans 444Clear, Simple, and Wrong 447Summary 448Chapter 18: Improve SQL Performance 449Application Tuning â Logging and Profiling 449Logging 450Profiling â DBMS_PROFILER 451Profiling â DBMS_HPROF 453Application Tuning Through Batching 454Installation and Patch Scripts 455OLTP Applications 457Data Warehouses 458Database Tuning 459Measure Database Performance 460Automatic Workload Repository (AWR) 463Active Session History (ASH) 465Automatic Database Diagnostic Monitor (ADDM) 466Advisors 468Other Tools 469SQL Tuning â Find Slow SQL 470Get Organized 470Slow Is Based on DB Time 470Find Currently Running Slow SQL 471Find Historically Slow SQL 473SQL Tuning â Find Execution Plans 473Graphical Execution Plans Considered Harmful 473Text Is Best 475DBMS_XPLAN Functions 477DBMS_XPLAN FORMAT Parameter 478Note Section 479Other Ways to Get Execution Plans 480SQL Tuning â Find Actual Times and Cardinalities for Operations 480GATHER_PLAN_STATISTICS 481Real-Time SQL Monitor Reports (Text) 485Real-Time SQL Monitor Reports (Active) 488Degree of Parallelism 489What to Look for in Execution Plans 491SQL Tuning â Changing Execution Plans 493Changing Execution Plans 493Hints 495SQL Profile Example 497SQL Tuning â Gathering Optimizer Statistics 499Manual Statistics 499Automatic Statistics 501Other Statistics 501Summary 503Part V: Solve Anything with Oracle SQL 505Chapter 19: Solve Challenging Problems with Arcane SQL Features 507Oracle vs. the Unix Philosophy 507MODEL 508Row Pattern Matching 512Any Types 513APEX 515Oracle Text 517Other Features 519Advanced Analytics (Data Mining) 519Spatial 519OLAP 519Property Graph 520Virtual Private Database 520Database In-Memory 521Advanced Compression 521Summary 521Chapter 20: Use SQL More Often with Advanced Dynamic SQL 523Parsing 523PL/Scope 524PLSQL_LEXER 525ANTLR 526DBMS_SQL 528DBMS_XMLGEN 529PL/SQL Common Table Expressions 531Method4 Dynamic SQL 532Polymorphic Table Functions 533Method5 534Summary 535Chapter 21: Level Up Your Skills with PL/SQL 537Is PL/SQL Worth Mastering? 537The Focus Is Still on SQL 538Create a PL/SQL Playground 538PL/SQL Integration Features 539Tips for Packaging Code 540Session Data 540Transactions I â COMMIT, ROLLBACK, and SAVEPOINT 543Transactions II â Implicit Cursor Attributes 545Transactions III â Row-Level Locking 546Transactions IV â Isolation and Consistency 547Simple Variables 549Cursors 551Records 554Collections 556Functions 559Table Functions 561Pipelined Functions 563Parallel Pipelined Functions 564Autonomous Transactions for DML and DDL 565Autonomous Transactions for Logging 566Definerâs Rights vs. Invokerâs Rights 568Triggers 569Conditional Compilation 574Other PL/SQL Features 575Start Teaching and Creating 575Teach Others 576Create Open Source Projects 576Part VI: Appendices 579Appendix A: SQL Style Guide Cheat Sheet 581Appendix B: Computer Science Topics 583Index 585About the AuthorJon Heller is an expert SQL and PL/SQL programmer with17 years of Oracle experience. During that time he hasworked as a database analyst, developer, and administrator.In his spare time, he is active on Stack Overflow wherehe is a top user in the Oracle and PL/SQL tags. He enjoyscreating open source software for Oracle, such as the remoteexecution program Method5. He has a masterâs degree incomputer science from North Carolina State University andlives in Iowa with his wife and two sons.