Chapter 6: The Basics of Efficient SQL

---

Overview

In the previous chapter we examined the basic syntax of SQL in Oracle Database. This chapter will attempt to detail the most simplistic aspects of SQL code tuning. In other words, we are going to discuss what in SQL statements is good for performance and what is not. The approach to performance in this chapter will be based on a purely SQL basis. We want to avoid the nitty-gritty and internal processing occurring in Oracle Database at this stage. It is essential to understand the basic facts about how to write well-performing SQL code first, without considering specific details of Oracle software.

The most important rule of thumb with SQL statements, and particularly SELECT statements, those most subject to tuning, is what is commonly known as the "KISS" rule "Keep It Simple Stupid!" The simpler your SQL statements are the faster they will be. There are two reasons for this. Firstly, simple SQL statements are much more easily tuned and secondly, the Optimizer will function a lot better when assessing less complex SQL code. The negative effect of this is granularity but this negative effect depends on how the application is coded. For instance, connecting to and disconnecting from the database for every SQL code statement is extremely inefficient.

Part of the approach in this chapter is to present SQL performance examples without bombarding the reader with the details of too much theory and reference material. Any reference items such as explanations of producing query plans will be covered later on in this book.

So what this chapter will cover is mostly a general type of SQL code tuning. Thus the title of this chapter: "The Basics of Efficient SQL." Let's start with a brief look at the SELECT statement.

---