Pro Oracle SQL (Expert's Voice in Oracle)

Prelim......Page 1 Contents at a Glance......Page 5 Contents......Page 7 About the Authors......Page 17 About the Technical Reviewers......Page 19 Acknowledgments......Page 21 The SQL Language......Page 23 Interfacing to the Database......Page 24 Connect to a Database......Page 25 Configuring the SQL*Plus environment......Page 26 Executing Commands......Page 28 The Five Core SQL Statements......Page 30 The SELECT Statement......Page 31 The FROM Clause......Page 32 The WHERE Clause......Page 33 The GROUP BY Clause......Page 34 The SELECT List......Page 35 The ORDER BY Clause......Page 36 Single-table Inserts......Page 37 Multi-table Inserts......Page 38 The UPDATE Statement......Page 40 The DELETE Statement......Page 44 The MERGE Statement......Page 46 Summary......Page 49 Oracle Architecture Basics......Page 51 SGA – The Shared Pool......Page 53 The Library Cache......Page 54 Identical Statements......Page 55 SGA – The Buffer Cache......Page 59 Query Transformation......Page 61 View Merging......Page 62 Subquery Unnesting......Page 66 Predicate Pushing......Page 69 Query Rewrite with Materialized Views......Page 72 Determining the Execution Plan......Page 74 Executing the Plan and Fetching Rows......Page 78 SQL Execution – Putting It All Together......Page 81 Summary......Page 82 Full Scan Access Methods......Page 83 How Full Scan Operations are Chosen......Page 84 Full Scans and Throwaway......Page 87 Full Scans and Multiblock Reads......Page 88 Full Scans and the Highwater Mark......Page 89 Index Scan Access Methods......Page 95 Index Structure......Page 96 Index Scan Types......Page 97 Index Unique Scan......Page 100 Index range scan......Page 101 Index Full Scan......Page 103 Index Skip Scan......Page 107 Index Fast Full Scan......Page 109 Join Methods......Page 110 Nested Loops Joins......Page 111 Sort-Merge Joins......Page 114 Hash Joins......Page 116 Cartesian Joins......Page 118 Outer Joins......Page 120 Summary......Page 126 Thinking in Sets......Page 127 Moving from Procedural to Set-based Thinking......Page 128 Procedural vs Set-based Thinking: An Example......Page 133 Set Operations......Page 135 UNION and UNION ALL......Page 136 MINUS......Page 139 Sets and Nulls......Page 141 NULLs and Unintuitive Results......Page 142 NULL Behavior in Set Operations......Page 145 NULLs and GROUP BY and ORDER BY......Page 146 NULLs and Aggregate Functions......Page 148 Summary......Page 149 Asking Good Questions......Page 151 Unknown......Page 0 Categories of Questions......Page 152 Questions about the Question......Page 154 Questions about Data......Page 156 Building Logical Expressions......Page 162 Summary......Page 174 Using Explain Plan......Page 175 Understanding How EXPLAIN PLAN can Miss the Mark......Page 182 Reading the Plan......Page 185 Viewing the Associated Execution Plan......Page 188 Collecting the Plan Statistics......Page 190 Identifying SQL Statements for Later Plan Retrieval......Page 193 Understanding DBMS_XPLAN in Detail......Page 196 Using Plan Information for Solving Problems......Page 202 Summary......Page 211 Advanced Grouping......Page 213 Basic GROUP BY Usage......Page 214 HAVING Clause......Page 217 CUBE Extension to GROUP BY......Page 219 Putting CUBE To Work......Page 223 Eliminate NULLs with the GROUPING() Function......Page 229 Extending Reports with GROUPING()......Page 231 Extending Reports With GROUPING_ID()......Page 232 GROUPING SETS and ROLLUP()......Page 236 GROUP BY Restrictions......Page 239 Summary......Page 242 Example Data......Page 243 Anatomy of Analytic Functions......Page 244 List of Functions......Page 245 Aggregation Functions......Page 246 Aggregate Function Over An Entire Partition......Page 247 Granular Window Specifications......Page 248 Example 1: Returning a Value from Prior Row......Page 249 Understanding that Offset is in Rows......Page 250 Example 2: Returning a Value from an Upcoming Row......Page 251 First_value & Last_value......Page 252 Example: Last_value to Calculate Minimum......Page 253 Nth_value (11gR2)......Page 254 Rank......Page 256 Dense_rank......Page 257 Row_number......Page 258 Ratio_to_report......Page 259 Percentile_cont......Page 260 Percentile_disc......Page 262 NTILE......Page 263 Stddev......Page 264 Performance Tuning......Page 265 Predicates......Page 266 Advanced topics......Page 268 Dynamic SQL......Page 269 Nesting Analytic Functions......Page 270 Parallelism......Page 271 Summary......Page 272 Spreadsheets......Page 273 Example Data......Page 274 Anatomy of a Model Clause......Page 275 Rules......Page 276 Positional and Symbolic References......Page 277 Positional Notation......Page 278 Symbolic Notation......Page 279 Returning Updated Rows......Page 280 Row Evaluation Order......Page 282 Rule Evaluation Order......Page 284 Aggregation......Page 285 Iteration......Page 286 An Example......Page 287 PRESENTV and NULLs......Page 288 Lookup Tables......Page 289 NULLs......Page 291 ACYCLIC......Page 293 CYCLIC......Page 294 Sequential......Page 295 Predicate Pushing......Page 296 Materialized Views......Page 298 Parallelism......Page 299 Partitioning in Model Clause Execution......Page 300 Indexes......Page 302 Subquery Factoring......Page 303 Summary......Page 304 Standard Usage......Page 305 Testing Execution Plans......Page 308 Testing Over Multiple Executions......Page 312 Testing the Effects of Query Changes......Page 315 Seizing Other Optimization Opportunities......Page 318 Applying Subquery Factoring to PL/SQL......Page 323 A CONNECT BY Example......Page 326 The Example Using an RSF......Page 328 Restrictions on RSF......Page 329 Differences from CONNECT BY......Page 330 The LEVEL Pseudocolumn......Page 331 The SYS_CONNECT_BY_PATH Function......Page 333 The CONNECT_BY_ROOT Operator......Page 335 The CONNECT_BY_ISCYCLE Pseudocolumn and NOCYCLE Parameter......Page 338 The CONNECT_BY_ISLEAF Pseudocolumn......Page 341 Summary......Page 346 Semi-joins......Page 347 Semi-join Plans......Page 356 Controlling Semi-join Plans Using Hints......Page 361 Controlling Semi-join Plans at the Instance Level......Page 364 Semi-join Restrictions......Page 367 Anti-joins......Page 369 Anti-join Plans......Page 375 Controlling Anti-join Plans Using Hints......Page 385 Controlling Anti-join Plans at the Instance Level......Page 386 Anti-join Restrictions......Page 389 Summary......Page 393 Indexes......Page 395 When to use Indexes......Page 396 Choice of Columns......Page 398 The Null Issue......Page 400 B-tree indexes......Page 401 Bitmap Indexes......Page 402 Index Organized Tables......Page 403 Partitioned Indexes......Page 405 Local Indexes......Page 406 Global Indexes......Page 407 Hash Partitioning vs. Range Partitioning......Page 408 Compressed Indexes......Page 411 Function Based Indexes......Page 413 Reverse Key Indexes......Page 416 Descending Indexes......Page 417 Invisible Indexes......Page 418 Virtual Indexes......Page 420 Bitmap Join Indexes......Page 421 Summary......Page 423 Direct Path Inserts......Page 425 Multi-Table Inserts......Page 428 Conditional Insert......Page 429 DML Error Logging......Page 430 UPDATE......Page 437 DELETE......Page 443 Syntax and Usage......Page 447 Performance Comparison......Page 451 Summary......Page 453 What is a Transaction?......Page 455 ACID Properties of a Transaction......Page 456 Transaction Isolation Levels......Page 457 Multi-Version Read Consistency......Page 459 Set Transaction......Page 460 Grouping Operations into Transactions......Page 461 The Order Entry Schema......Page 462 The Active Transaction......Page 469 Using Savepoints......Page 471 Serializing Transactions......Page 474 Isolating Transactions......Page 477 Autonomous Transactions......Page 480 Summary......Page 485 Testing and Quality Assurance......Page 487 Test Cases......Page 488 Testing Methods......Page 489 Unit Tests......Page 490 Schema Changes......Page 494 Repeating the Unit Tests......Page 498 Execution Plan Comparison......Page 500 Adding Instrumentation to Code......Page 506 Testing for Performance......Page 510 Testing to Destruction......Page 512 Troubleshooting through Instrumentation......Page 513 Summary......Page 517 Plan Instability: Understanding The Problem......Page 519 Changes to Statistics......Page 520 Changes to the Environment......Page 522 Bind Variable Peeking......Page 524 Capturing Data on Currently-Running Queries......Page 527 Reviewing the History of a Statement’s Performance......Page 528 Aggregating Statistics by Plan......Page 530 Looking for Statistical Variance by Plan......Page 531 Checking for Variations Around a Point in Time......Page 533 Modifying Query Structure......Page 535 Giving the Optimizer some Hints......Page 536 Plan Control: Without Access to the Code......Page 544 Option 1: Change the Statistics......Page 545 Option 3: Add or Remove Access Paths......Page 547 Outlines......Page 548 SQL Profiles......Page 560 SQL Plan Baselines......Page 577 Conclusion......Page 584 A......Page 587 D......Page 588 F......Page 589 H......Page 590 M......Page 591 N......Page 592 P......Page 593 R......Page 594 S......Page 595 T......Page 596 Y, Z......Page 597

Pro Oracle SQL unlocks the power of SQL in the Oracle Database—one of the most potent SQL implementations on the market today. To master it requires a three-pronged approach: learn the language features, learn the supporting features that Oracle provides to help use the language effectively, and learn to think and work in sets.

Karen Morton and her team help you master powerful aspects of Oracle SQL not found in competing databases. You’ll learn analytic functions, the MODEL clause, and advanced grouping syntax—features thatwill helpin creating good queries for reporting and business intelligence applications. Pro Oracle SQL also helps you minimize parsing overhead, read execution plans, test for correct results, and exert control over SQL execution in your database. You’ll learn when to create indexes, how to verify that they make a difference, how to use SQL Profiles to optimize SQL in packaged applications, and much more. You’llalso understandhow SQL is optimized for working in sets, and that the key to getting accurate results lies in making sure that queries ask clear and precise questions.

What’s the bottom-line? Pro Oracle SQL helps you work at a truly professional level in Oracle dialect of SQL. You’ll master the language, the tools to work effectively with the language, and the right way to think about a problem in SQL. Pro Oracle SQL helps you rise above the crowd to provide stellar service in your chosen profession.

• Endorsed by the OakTable Network, a group of Oracle technologists well-known for their rigorous and scientific approach to Oracle Database performance
• Comprehensive—goes beyond the language with a focus on what you need to know to write successful queries and data manipulation statements.

What you’ll learn

• Master powerful SQL features implemented only in Oracle Database
• Read and interpret SQL execution plans
• Quickly diagnose and fix badly performing SQL
• Control execution plans through hints, profiles, and plan baselines
• Optimize queries within packaged applications without touching the code
• Recognize when not to waste time on SQL that is performing optimally

Who this book is for

Pro Oracle SQL is aimed at developers and database administrators who submit SQL for execution by an Oracle database. Readers should already know the basic four SQL statements, and be ready to learn deeply about Oracle’s specific implementation of the language, including Oracle-specific features and syntax. Readers should also want to learn about Oracle Database features such as analytic queries, the MODEL clause, and subquery refactoring that are designed to help developers and database administrators exert control over their SQL environment and its execution.

Table of Contents

1. Core SQL
2. SQL Execution
3. Access and Join Methods
4. SQL is About Sets
5. It’s About the Question
6. SQL Execution Plans
7. Advanced Grouping
8. Analytic Functions
9. The Model Clause
10. Subquery Factoring
11. Semi-joins and Anti-joins
12. Indexes
13. Beyond the SELECT
14. Transaction Processing
15. Testing and Quality Assurance
16. Plan Stability and Control

Pro Oracle SQL unlocks the power of SQL in the Oracle Database—one of the most potent SQL implementations on the market today. To master it requires a three-pronged approach: learn the language features, learn the supporting features that Oracle provides to help use the language effectively, and learn to think and work in sets. Karen Morton and her team help you master powerful aspects of Oracle SQL not found in competing databases. You’ll learn analytic functions, the MODEL clause, and advanced grouping syntax—features that will help in creating good queries for reporting and business intelligence applications. Pro Oracle SQL also helps you minimize parsing overhead, read execution plans, test for correct results, and exert control over SQL execution in your database. You’ll learn when to create indexes, how to verify that they make a difference, how to use SQL Profiles to optimize SQL in packaged applications, and much more. You’ll also understand how SQL is optimized for working in sets, and that the key to getting accurate results lies in making sure that queries ask clear and precise questions. What’s the bottom-line? Pro Oracle SQL helps you work at a truly professional level in Oracle dialect of SQL. You’ll master the language, the tools to work effectively with the language, and the right way to think about a problem in SQL. Pro Oracle SQL helps you rise above the crowd to provide stellar service in your chosen profession. Endorsed by the OakTable Network, a group of Oracle technologists well-known for their rigorous and scientific approach to Oracle Database performance Comprehensive—goes beyond the language with a focus on what you need to know to write successful queries and data manipulation statements. What you’ll learn Master powerful SQL features implemented only in Oracle Database Read and interpret SQL execution plans Quickly diagnose and fix badly performing SQL Control execution plans through hints, profiles, and plan baselines Optimize queries within packaged applications without touching the code Recognize when not to waste time on SQL that is performing optimally Who this book is for Pro Oracle SQL is aimed at developers and database administrators who submit SQL for execution by an Oracle database. Readers should already know the basic four SQL statements, and be ready to learn deeply about Oracle’s specific implementation of the language, including Oracle-specific features and syntax. Readers should also want to learn about Oracle Database features such as analytic queries, the MODEL clause, and subquery refactoring that are designed to help developers and database administrators exert control over their SQL environment and its execution. Table of Contents Core SQL SQL Execution Access and Join Methods SQL is About Sets It’s About the Question SQL Execution Plans Advanced Grouping Analytic Functions The Model Clause Subquery Factoring Semi-joins and Anti-joins Indexes Beyond the SELECT Transaction Processing Testing and Quality Assurance Plan Stability and Control CHAPTER 2 SQL Execution; Oracle Architecture Basics; SGA - The Shared Pool; The Library Cache; Identical Statements; SGA - The Buffer Cache; Query Transformation; View Merging; Subquery Unnesting; Predicate Pushing; Query Rewrite with Materialized Views; Determining the Execution Plan; Executing the Plan and Fetching Rows; SQL Execution - Putting It All Together; Summary; CHAPTER 3 Access and Join Methods; Full Scan Access Methods; How Full Scan Operations are Chosen; Full Scans and Throwaway; Full Scans and Multiblock Reads; Full Scans and the Highwater Mark; Index Scan Access Methods