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