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
SUMMARY
-
Items Found: 328
- Chapter 1: Core SQL 1
- Chapter 2: SQL Execution 29
- Chapter 3: Access and Join Methods 61
- Chapter 4: SQL is About Sets 105
- Chapter 5: It’s About the Question 129
- Chapter 6: SQL Execution Plans 153
- Chapter 7: Advanced Grouping 191
- Chapter 8: Analytic Functions 221
- Chapter 9: The Model Clause 251
- Chapter 10: Subquery Factoring 283
- Chapter 11: Semi-joins and Anti-joins 325
- Chapter 12: Indexes 373
- Chapter 13: Beyond the SELECT 403
- Chapter 14: Transaction Processing 433
- Chapter 15: Testing and Quality Assurance 465
- Chapter 16: Plan Stability and Control 497
- Index 565
- Introduction
- Chapter 1: Core SQL 1
- The SQL Language 1
- Interfacing to the Database 2
- Review of SQL*Plus 3
- Connect to a Database 3
- Configuring the SQL*Plus environment 4
- Executing Commands 6
- The Five Core SQL Statements 8
- The SELECT Statement 9
- The FROM Clause 10
- The WHERE Clause 11
- The GROUP BY Clause 12
- The HAVING Clause 13
- The SELECT List 13
- The ORDER BY Clause 14
- The INSERT Statement 15
- Single-table Inserts 15
- Multi-table Inserts 16
- The UPDATE Statement 18
- The DELETE Statement 22
- The MERGE Statement 24
- Summary 27
- vi
- Chapter 2: SQL Execution 29
- Oracle Architecture Basics 29
- SGA – The Shared Pool 31
- The Library Cache 32
- Identical Statements 33
- SGA – The Buffer Cache 37
- Query Transformation 39
- View Merging 40
- Subquery Unnesting 44
- Predicate Pushing 47
- Query Rewrite with Materialized Views 50
- Determining the Execution Plan 52
- Executing the Plan and Fetching Rows 56
- SQL Execution – Putting It All Together 59
- Summary 60
- Chapter 3: Access and Join Methods 61
- Full Scan Access Methods 61
- How Full Scan Operations are Chosen 62
- Full Scans and Throwaway 65
- Full Scans and Multiblock Reads 66
- Full Scans and the Highwater Mark 67
- Index Scan Access Methods 73
- Index Structure 74
- Index Scan Types 75
- Index Unique Scan 78
- Index range scan 79
- Index Full Scan 81
- Index Skip Scan 85
- Index Fast Full Scan 87
- Join Methods 88
- Nested Loops Joins 89
- Sort-Merge Joins 92
- vii
- Hash Joins 94
- Cartesian Joins 96
- Outer Joins 98
- Summary 104
- Chapter 4: SQL is About Sets 105
- Thinking in Sets 105
- Moving from Procedural to Set-based Thinking 106
- Procedural vs Set-based Thinking: An Example 111
- Set Operations 113
- UNION and UNION ALL 114
- MINUS 117
- INTERSECT 119
- Sets and Nulls 119
- NULLs and Unintuitive Results 120
- NULL Behavior in Set Operations 123
- NULLs and GROUP BY and ORDER BY 124
- NULLs and Aggregate Functions 126
- Summary 127
- Chapter 5: It’s About the Question 129
- Asking Good Questions 129
- The Purpose of Questions 130
- Categories of Questions 130
- Questions about the Question 133
- Questions about Data 135
- Building Logical Expressions 141
- Summary 154
- Chapter 6: SQL Execution Plans 153
- Explain Plans 153
- Using Explain Plan 153
- Understanding How EXPLAIN PLAN can Miss the Mark 160
- Reading the Plan 163
- Execution Plans 166
- Viewing Recently Generated SQL 166
- Viewing the Associated Execution Plan 166
- Collecting the Plan Statistics 168
- Identifying SQL Statements for Later Plan Retrieval 171
- Understanding DBMS_XPLAN in Detail 174
- Using Plan Information for Solving Problems 180
- Summary 189
- Chapter 7: Advanced Grouping 191
- Basic GROUP BY Usage 192
- HAVING Clause 195
- “New” GROUP BY Functionality 197
- CUBE Extension to GROUP BY 197
- Putting CUBE To Work 201
- Eliminate NULLs with the GROUPING() Function 207
- Extending Reports with GROUPING() 209
- Extending Reports With GROUPING_ID() 210
- GROUPING SETS and ROLLUP() 214
- GROUP BY Restrictions 217
- Summary 220
- Chapter 8: Analytic Functions 221
- Example Data 221
- Anatomy of Analytic Functions 222
- List of Functions 223
- Aggregation Functions 224
- Aggregate Function Over An Entire Partition 225
- Granular Window Specifications 226
- Default Window Specification 227
- Lead and Lag 227
- Syntax and Ordering 227
- Example 1: Returning a Value from Prior Row 227
- Understanding that Offset is in Rows 228
- Example 2: Returning a Value from an Upcoming Row 229
- ix
- First_value & Last_value 230
- Example: First_value to Calculate Maximum 231
- Example: Last_value to Calculate Minimum 231
- Other Analytic Functions 232
- Nth_value (11gR2) 232
- Rank 234
- Dense_rank 235
- Row_number 236
- Ratio_to_report 237
- Percent_rank 238
- Percentile_cont 238
- Percentile_disc 240
- NTILE 241
- Stddev 242
- Listagg 243
- Performance Tuning 243
- Execution Plans 244
- Predicates 244
- Indexes 246
- Advanced topics 246
- Dynamic SQL 247
- Nesting Analytic Functions 248
- Parallelism 249
- PGA size 250
- Organizational Behavior 250
- Summary 250
- Chapter 9: The Model Clause 251
- Spreadsheets 251
- Inter-Row Referencing via the Model clause 252
- Example Data 252
- Anatomy of a Model Clause 253
- Rules 254
- Positional and Symbolic References 255
- Positional Notation 256
- x
- Symbolic Notation 257
- FOR Loops 258
- Returning Updated Rows 258
- Evaluation Order 260
- Row Evaluation Order 260
- Rule Evaluation Order 262
- Aggregation 263
- Iteration 264
- An Example 265
- PRESENTV and NULLs 266
- Lookup Tables 267
- NULLs 269
- Performance Tuning with the Model Clause 271
- Execution Plans 271
- Predicate Pushing 274
- Materialized Views 276
- Parallelism 277
- Partitioning in Model Clause Execution 278
- Indexes 280
- Subquery Factoring 281
- Summary 282
- Chapter 10: Subquery Factoring 283
- Standard Usage 283
- Optimizing SQL 286
- Testing Execution Plans 286
- Testing Over Multiple Executions 290
- Testing the Effects of Query Changes 293
- Seizing Other Optimization Opportunities 296
- Applying Subquery Factoring to PL/SQL 301
- Recursive Subqueries 304
- A CONNECT BY Example 304
- The Example Using an RSF 306
- Restrictions on RSF 307
- Differences from CONNECT BY 308
- xi
- Duplicating CONNECT BY Functionality 309
- The LEVEL Pseudocolumn 309
- The SYS_CONNECT_BY_PATH Function 311
- The CONNECT_BY_ROOT Operator 313
- The CONNECT_BY_ISCYCLE Pseudocolumn and NOCYCLE Parameter 316
- The CONNECT_BY_ISLEAF Pseudocolumn 319
- Summary 324
- Chapter 11: Semi-joins and Anti-joins 325
- Semi-joins 325
- Semi-join Plans 334
- Controlling Semi-join Plans 339
- Controlling Semi-join Plans Using Hints 339
- Controlling Semi-join Plans at the Instance Level 342
- Semi-join Restrictions 345
- Semi-join Requirements 347
- Anti-joins 347
- Anti-join Plans 353
- Controlling Anti-join Plans 363
- Controlling Anti-join Plans Using Hints 363
- Controlling Anti-join Plans at the Instance Level 364
- Anti-join Restrictions 367
- Anti-join Requirements 371
- Summary 371
- Chapter 12: Indexes 373
- Understanding Indexes 374
- When to use Indexes 374
- Choice of Columns 376
- The Null Issue 378
- Index Structural Types 379
- B-tree indexes 379
- Bitmap Indexes 380
- Index Organized Tables 381
- Partitioned Indexes 383
- Local Indexes 384
- Global Indexes 385
- Hash Partitioning vs. Range Partitioning 386
- Solutions to Match Application Characteristics 390
- Compressed Indexes 390
- Function Based Indexes 392
- Reverse Key Indexes 395
- Descending Indexes 396
- Solutions to Management Problems 397
- Invisible Indexes 397
- Virtual Indexes 399
- Bitmap Join Indexes 400
- Summary 402
- Chapter 13: Beyond the SELECT 403
- INSERT 403
- Direct Path Inserts 403
- Multi-Table Inserts 406
- Conditional Insert 407
- DML Error Logging 409
- UPDATE 417
- DELETE 424
- MERGE 428
- Syntax and Usage 428
- Performance Comparison 432
- Summary 435
- Chapter 14: Transaction Processing 433
- What is a Transaction? 433
- ACID Properties of a Transaction 434
- Transaction Isolation Levels 435
- Multi-Version Read Consistency 437
- Transaction Control Statements 438
- Commit 438
- Savepoint 438
- Rollback 438
- Set Transaction 438
- Set Constraints 439
- Grouping Operations into Transactions 439
- The Order Entry Schema 440
- The Active Transaction 447
- Using Savepoints 449
- Serializing Transactions 452
- Isolating Transactions 455
- Autonomous Transactions 458
- Summary 463
- Chapter 15: Testing and Quality Assurance 465
- Test Cases 466
- Testing Methods 467
- Unit Tests 468
- Regression Tests 472
- Schema Changes 472
- Repeating the Unit Tests 476
- Execution Plan Comparison 478
- Instrumentation 484
- Adding Instrumentation to Code 484
- Testing for Performance 488
- Testing to Destruction 490
- Troubleshooting through Instrumentation 491
- Summary 495
- Chapter 16: Plan Stability and Control 497
- Plan Instability: Understanding The Problem 497
- Changes to Statistics 498
- Changes to the Environment 500
- xiv
- Changes to the SQL 502
- Bind Variable Peeking 502
- Identifying Plan Instability 505
- Capturing Data on Currently-Running Queries 505
- Reviewing the History of a Statement’s Performance 506
- Aggregating Statistics by Plan 508
- Looking for Statistical Variance by Plan 509
- Checking for Variations Around a Point in Time 511
- Plan Control: Solving the Problem 513
- Modifying Query Structure 513
- Making Appropriate Use of Literals 514
- Giving the Optimizer some Hints 514
- Plan Control: Without Access to the Code) 522
- Option 1: Change the Statistics 523
- Option 2: Change Database Parameters 525
- Option 3: Add or Remove Access Paths 525
- Option 4: Apply Hint Based Plan-Control Mechanisms 526
- Outlines 526
- SQL Profiles 538
- SQL Plan Baselines 555
- Hint Based Plan Control Mechanisms Wrap Up 562
- Conclusion 562
- Index 565
