Databases Reference
In-Depth Information
4
Optimizing SQL Code
In this chapter, we will cover the following topics:
F
Using bind variables
F
Avoiding full table scans
F
Exploring index lookup
F
Exploring index skip-scan and index range-scan
F
Introducing arrays and bulk operations
F
Optimizing joins
F
Using subqueries
F
Tracing SQL activity with SQL Trace and TKPROF
Introduction
In this chapter, we will see how to diagnose and solve typical performance problems caused
by poorly written SQL code. We will inspect both queries and Data Manipulation Language
(DML), starting with the correct use of bind variables in the first recipe.
This chapter will illustrate various aspects related to SQL code, providing solutions to the
most common issues. We will see how to avoid full table scans, when possible,using indexes.
For this, it is necessary to know the differences between index full scan, index skip-scan,
and index range-scan operations.
We will also discuss arrays and bulk operations, revealing some tricks to increase performance
in DML operations. Joins and subqueries will be discussed in the later part of the chapter.
The last recipe illustrates the use of SQL Trace and TKPROF, tools that help diagnose and
correct problems. After reading this chapter, if you experience a problem in the SQL code
of your database, you know how to start solving it using these tools.
