Skip to main content
Disciplines
Automotive Business IT + Informatics Construction + Real Estate Electrical Engineering + Electronics Energy + Sustainability Insurance + Risk Finance + Banking Management + Leadership Marketing + Sales Mechanical Engineering + Materials
Events
DE
EN
Books
Journals
Topic Page
Marketing
Start single access now
Access for companies
EXTENDED SEARCH
Log in
Top

2019 | Book

Understand Relational Databases Read chapter Read first chapter

Pro Oracle SQL Development

Best Practices for Writing Advanced Queries

Author: Jon Heller

Publisher: Apress

Part of: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Login to get access
insite
SEARCH

About this book

Write SQL statements that are more powerful, simpler, and faster using Oracle SQL and its full range of features. This book provides a clearer way of thinking about SQL by building sets, and 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.
Pro Oracle SQL Development is for anyone who already knows Oracle SQL and is ready to take their skills to the next level. Many developers, analysts, testers, and administrators use Oracle databases frequently, but their queries are limited because they do not have the knowledge, experience, or right environment to help them take full advantage of Oracle’s advanced features. This book will inspire you to achieve more with your Oracle SQL statements through tips for 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 covers advanced topics and shows you how to become an Oracle expert.

What You'll LearnUnderstand the power of Oracle SQL and where to apply it
Create a database development environment that is simple, scalable, and conducive to learningSolve complex problems that were previously solved in a procedural language
Write large Oracle SQL statements that are powerful, simple, and fast
Apply coding styles to make your SQL statements more readable
Tune large Oracle SQL statements to eliminate and avoid performance problems
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. For traditional database professionals the book offers new ways of thinking about the language they have used for so long. For modern full stack developers the book explains how a database can be much more than simply a place to store data.

Table of Contents

Frontmatter

Learn How to Learn

Frontmatter
Chapter 1. Understand Relational Databases
Abstract
Understanding the history and theory behind relational databases helps us make sense of things and avoid common traps. But there are also many times when we need to ignore the theory and build practical solutions. This book assumes you are already familiar with relational databases and SQL; the information provided here is not merely introductory, it is foundational.
Jon Heller
Chapter 2. Create an Efficient Database Development Process
Abstract
Most Oracle database development environments are set up like fragile china shops. There’s an intricate structure that must be maintained at all costs, and the smallest change can break things and stop everyone from working. Nobody knows exactly how the database got to be the way it is, and everybody is worried they won’t know how to fix it when it breaks. Typical Oracle database development processes are woefully behind industry norms.
Jon Heller
Chapter 3. Increase Confidence and Knowledge with Testing
Abstract
Testing is more than simply demonstrating a program is correct. We must constantly challenge ourselves to make ourselves better. Likewise, we must also challenge our programs and our ideas. Testing is how we bring science into our craft and replace guesses and myths with real knowledge about how the world works. Building good tests can take a lot of time and effort, and Oracle databases present unique challenges for building reproducible test cases. Luckily, Oracle provides many tools to help us test, such as SQL, PL/SQL, the data dictionary, dynamic performance views, and much more.
Jon Heller
Chapter 4. Find Reliable Sources
Abstract
Oracle SQL programming has an epistemic problem. Epistemology is the study of knowledge, or simply asking ourselves what do we know and how do we know it. We don’t need to take a philosophy class before we start programming, but we do need to carefully consider our sources. The Oracle programming culture places too much weight on unreliable epistemologies, such as tradition and scripture, and not enough weight on science and reason. If we listen to the wrong sources, we will learn the wrong lessons.
Jon Heller
Chapter 5. Master the Entire Stack
Abstract
Oracle SQL development requires more than just knowledge about Oracle SQL. While this book focuses on the Oracle SQL part, this chapter looks at the entire technology stack we use to write SQL. This is not a generic technology how-to book, so we will look at our technology stack through the eyes of a SQL developer. We need to invest in all of our tools and processes, from low-level hardware to high-level project management.
Jon Heller

Write Powerful SQL with Sets and Advanced Features

Frontmatter
Chapter 6. Build Sets with Inline Views and ANSI Join Syntax
Abstract
We’re almost ready to dive into advanced SQL features. In Part I we built a solid foundation for SQL programming. Before we start using advanced features, we need to discuss how to construct our SQL statements.
Jon Heller
Chapter 7. Query the Database with Advanced SELECT Features
Abstract
SELECT is the most important SQL statement type. Even when we’re changing data, most of the logic will go in the SELECT and WHERE clauses of the statement. Before we can insert, update, or delete a set, we must be able to choose a set.
Jon Heller
Chapter 8. Modify Data with Advanced DML
Abstract
Now that we learned how to write advanced SQL statements to retrieve data, it is time to learn how to write advanced SQL statements to change data. Oracle Data Manipulation Language (DML) lets us insert, update, and delete data.
Jon Heller
Chapter 9. Improve the Database with Advanced Oracle Schema Objects
Abstract
So far this book has used pre-built objects but now it’s time to start making our own. This book assumes you are familiar with basic Data Definition Language commands like CREATE TABLE. This chapter describes advanced features of Oracle schema objects and the advanced DDL commands to create and maintain them.
Jon Heller
Chapter 10. Optimize the Database with Oracle Architecture
Abstract
SQL and the relational model are logical constructs built on top of our slow, physical machines. Even E.F. Codd’s original paper warned that implementing the relational model would run into physical limitations. The more advanced features we use, and the more stress we put on the database, the more likely it is for Oracle’s abstractions to fail. Oracle has put a lot of effort into making our SQL code atomic, consistent, isolated, and durable. But no system can hide all of its implementation details, and we need to understand Oracle internals in order to make things work efficiently.
Jon Heller

Write Elegant SQL with Patterns and Styles

Frontmatter
Chapter 11. Stop Coding and Start Writing
Abstract
Part I explained the importance of SQL and built a solid foundation for SQL development. Part II discussed sets and advanced features needed to build powerful SQL statements. We now have the motivation, efficient processes, and advanced knowledge, but that is not enough. Part III discusses the styles and patterns needed to write elegant SQL.
Jon Heller
Chapter 12. Write Large SQL Statements
Abstract
We must write large SQL statements to take full advantage of the power of Oracle SQL. Large procedures are an anti-pattern in procedural programming languages; we need to understand why SQL is different. Large SQL statements create several risks and opportunities; we must be aware of the consequences of parsing, optimizer transformations, resource consumption, context switches, and parallelism. Finally, we need to learn how to read and debug large SQL statements.
Jon Heller
Chapter 13. Write Beautiful SQL Statements
Abstract
The most important thing about programming styles is to have one. This chapter shows how traditional SQL styles can prevent us from realizing Oracle SQL’s true potential. This subjective and opinionated chapter finishes the discussion about how to write beautiful SQL statements.
Jon Heller
Chapter 14. Use SQL More Often with Basic Dynamic SQL
Abstract
Dynamic SQL is a powerful tool that helps us get the most out of Oracle SQL. With dynamic SQL we can build our code at run time. Writing code in code is challenging but offers many opportunities.
Jon Heller
Chapter 15. Avoid Anti-Patterns
Abstract
So far this book has mostly focused on what we should do. It is also helpful to discuss the things we should not do. This chapter contains a list of anti-patterns – programming concepts and styles that we should avoid.
Jon Heller

Improve SQL Performance

Frontmatter
Chapter 16. Understand SQL Performance with Algorithm Analysis
Abstract
Solving Oracle SQL performance issues is the pinnacle of SQL development. Performance tuning requires a combination of all the skills previously discussed in this book. We need to understand the development process (to know why problems happened and weren’t caught sooner), advanced features (to find alternative ways to implement code), and programming styles (in order to understand the code and rewrite it into something better).
Jon Heller
Chapter 17. Understand SQL Tuning Theories
Abstract
Algorithm analysis was a useful guide for understanding the foundations of Oracle performance. Now we must turn to the more traditional techniques and theories of database performance. First we need to discuss performance issues from the end user’s perspective. Then we will discuss several popular approaches to performance tuning. But all roads eventually lead to SQL tuning. For effective SQL tuning we need to understand the importance of execution plans, the operations available in execution plans, cardinality, optimizer statistics, transformations, and dynamic optimization features.
Jon Heller
Chapter 18. Improve SQL Performance
Abstract
It’s time to use our theories and start building practical SQL tuning solutions. This chapter starts with high-level applications and then drills down through databases and SQL statements, until we get to operations.
Jon Heller

Solve Anything with Oracle SQL

Frontmatter
Chapter 19. Solve Challenging Problems with Arcane SQL Features
Abstract
It’s time to discuss the most advanced Oracle features. These features can solve extremely challenging problems; the kind of problems that many developers think are impossible to solve in a database.
Jon Heller
Chapter 20. Use SQL More Often with Advanced Dynamic SQL
Abstract
SQL is Oracle’s greatest strength. Dynamic SQL is important because it gives us more opportunities to use SQL. Chapter 14 introduced the basic features: using dynamic SQL for DDL, unknown objects, and simplifying privileges; EXECUTE IMMEDIATE and bind variable syntax; simplifying dynamic code with multiline strings, alternative quoting syntax, and templating; and the benefit of generating code instead of creating generic code.
Jon Heller
Chapter 21. Level Up Your Skills with PL/SQL
Abstract
If you use Oracle SQL long enough eventually, you will want to learn PL/SQL. SQL is the primary language for Oracle development, but we at least need PL/SQL to package our work. The first step in PL/SQL development is to create a safe playground for learning PL/SQL. There’s not enough room here for a full PL/SQL tutorial, but we can at least discuss the PL/SQL features most likely to help us enhance our SQL. To become a true Oracle master, you’ll need PL/SQL to help you teach others and create programs.
Jon Heller

Appendices

Frontmatter
Appendix A. SQL Style Guide Cheat Sheet
Abstract
Follow these style tips to write clear, powerful SQL statements. This simple list summarizes the programming style recommendations made throughout this book. There are exceptions to every rule, but we should still know what the rules are and why the rules exist.
Jon Heller
Appendix B. Computer Science Topics
Abstract
You don’t need a computer science degree to apply the practical advice in this book and become a better SQL developer. For database developers it can be useful to branch out in different directions and learn about other languages, system architecture, project management, etc. But a deeper understanding of database processing can also help your career and create interesting opportunities. Use the following list to explore the theoretical foundations of many of the topics in this book:
Jon Heller
Backmatter
Metadata
Title
Pro Oracle SQL Development
Author
Jon Heller
Copyright Year
2019
Publisher
Apress
Electronic ISBN
978-1-4842-4517-0
Print ISBN
978-1-4842-4516-3
DOI
https://doi.org/10.1007/978-1-4842-4517-0

Premium Partner

    Image Credits