Book description
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—techniques 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.
Table of contents
- Copyright
- About the Authors
- About the Technical Reviewers
- Acknowledgments
- 1. Core SQL
-
2. SQL Execution
- 2.1. Oracle Architecture Basics
- 2.2. SGA – The Shared Pool
- 2.3. The Library Cache
- 2.4. Identical Statements
- 2.5. SGA – The Buffer Cache
- 2.6. Query Transformation
- 2.7. View Merging
- 2.8. Subquery Unnesting
- 2.9. Predicate Pushing
- 2.10. Query Rewrite with Materialized Views
- 2.11. Determining the Execution Plan
- 2.12. Executing the Plan and Fetching Rows
- 2.13. SQL Execution – Putting It All Together
- 2.14. Summary
- 3. Access and Join Methods
- 4. SQL is About Sets
- 5. It's About the Question
- 6. SQL Execution Plans
-
7. Advanced Grouping
- 7.1. Basic GROUP BY Usage
- 7.2. HAVING Clause
- 7.3. "New" GROUP BY Functionality
- 7.4. CUBE Extension to GROUP BY
- 7.5. Putting CUBE To Work
- 7.6. Eliminate NULLs with the GROUPING() Function
- 7.7. Extending Reports with GROUPING()
- 7.8. Extending Reports With GROUPING_ID()
- 7.9. GROUPING SETS and ROLLUP()
- 7.10. GROUP BY Restrictions
- 7.11. Summary
- 8. Analytic Functions
-
9. The Model Clause
- 9.1. Spreadsheets
- 9.2. Inter-Row Referencing via the Model clause
- 9.3. Positional and Symbolic References
- 9.4. Returning Updated Rows
- 9.5. Evaluation Order
- 9.6. Aggregation
- 9.7. Iteration
- 9.8. Lookup Tables
- 9.9. NULLs
- 9.10. Performance Tuning with the Model Clause
- 9.11. Subquery Factoring
- 9.12. Summary
- 10. Subquery Factoring
- 11. Semi-joins and Anti-joins
- 12. Indexes
- 13. Beyond the SELECT
-
14. Transaction Processing
- 14.1. What is a Transaction?
- 14.2. ACID Properties of a Transaction
- 14.3. Transaction Isolation Levels
- 14.4. Multi-Version Read Consistency
- 14.5. Transaction Control Statements
- 14.6. Grouping Operations into Transactions
- 14.7. The Order Entry Schema
- 14.8. The Active Transaction
- 14.9. Using Savepoints
- 14.10. Serializing Transactions
- 14.11. Isolating Transactions
- 14.12. Autonomous Transactions
- 14.13. Summary
-
15. Testing and Quality Assurance
- 15.1. Test Cases
- 15.2. Testing Methods
- 15.3. Unit Tests
- 15.4. Regression Tests
- 15.5. Schema Changes
- 15.6. Repeating the Unit Tests
- 15.7. Execution Plan Comparison
- 15.8. Instrumentation
- 15.9. Adding Instrumentation to Code
- 15.10. Testing for Performance
- 15.11. Testing to Destruction
- 15.12. Troubleshooting through Instrumentation
- 15.13. Summary
-
16. Plan Stability and Control
- 16.1. Plan Instability: Understanding The Problem
- 16.2. Identifying Plan Instability
- 16.3. Plan Control: Solving the Problem
-
16.4. Plan Control: Without Access to the Code
- 16.4.1. Option 1: Change the Statistics
- 16.4.2. Option 2: Change Database Parameters
- 16.4.3. Option 3: Add or Remove Access Paths
- 16.4.4. Option 4: Apply Hint Based Plan-Control Mechanisms
- 16.4.5. Outlines
- 16.4.6. SQL Profiles
- 16.4.7. SQL Plan Baselines
- 16.4.8. Hint Based Plan Control Mechanisms Wrap Up
- 16.5. Conclusion
Product information
- Title: Pro Oracle SQL
- Author(s):
- Release date: November 2010
- Publisher(s): Apress
- ISBN: 9781430232285
You might also like
book
Pro Oracle SQL, Second Edition
Pro Oracle SQL, Second Edition unlocks the power of SQL in the Oracle database—one of the …
book
Oracle SQL: the Essential Reference
SQL (Structured Query Language) is the heart of a relational database management system. It's the language …
book
Oracle SQL Developer
Learn Database design, development,and administration using the feature-rich SQL Developer 4.1 interface About This Book Explore …
book
Expert PL/SQL Practices for Oracle Developers and DBAs
Expert PL/SQL Practices is a book of collected wisdom on PL/SQL programming from some of the …