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