- Shopping Bag ( 0 items )
Improve Database Performance by Tuning SQL Statements
Tune all types of SQL statements—from a simple SELECT statement to a complex non-correlated subquery—using tips and techniques from SQL expert Don Burleson. Officially authorized by Oracle
Corporationthis in-depth resource explains how to take any SQL statement, view the internal execution plan, and change the execution plan to improve the performance of the statement. You'll get details on Oracle's optimizer modes, SQL extensions, the STATSPACK utility, and a wealth of methods for tuning Oracle SQL statements.
From the official Oracle Press comes a comprehensive guide to tuning SQL statements for optimal execution. This expert resource explains how to view the internal execution plan of any SQL statement and change it to improve the performance of the statement. You'll get details on Oracle's optimizer modes, SQL extensions, the STATSPACK utility, and a wealth of methods for tuning Oracle SQL statements.
This chapter will introduce the nature of Oracle SQL and lay the foundation for techniques that we will be using throughout this book. In this chapter, we will cover the following topics.
The navigational database access tools required the programmer to navigate through the data structures by means of pointer chasing. Here is an actual example of a query from the popular IDMS database, an early CODASYL network database...
...Here we see that the query navigates between data records, accessing the record, finding a pointer, and moving between pointers according to the pointer values (as shown in Figure 1-1). The point is that this type of database query requires knowledge of the internal structures of the database in order to extract data.
The equivalent statement in SQL is quite different in syntax and function. Unlike a navigational database access language, SQL is designed to require only a specification of the columns you want to display, the tables that contain the data, and the join criteria for the tables....
We will take a closer look at the basic structure of SQL later in this chapter. While SQL is generally associated with relational databases, it is important to note that SQL is also popular in nonrelational databases. The IDMS network database developers renamed their product to IDMS/R after they created a SQL engine, and several object-oriented databases now offer SQL front ends that make their databases appear to be relational.
In Codd and Date's model, the tables are represented as two-dimensional arrays of "rows" and "columns." Rows were called "tuples" (rhymes with "couples"), and columns were called "attributes." A table will always have a field or several fields that make a "primary key" for a table. In their relational database model, the tables are independent, unlike in hierarchical and network models, where they are pointerconnected.
The relational database model offered the following improvements over the existing hierarchical and network databases:
PART I Background1 Introduction to SQL