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

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