Oracle Tips by Burleson Consulting
The acronym “SQL” is
short for Structured Query Language. Unfortunately, SQL is not
structured, SQL is not only for queries, and SQL is not a language,
per se, because SQL is embedded within other languages such as C and
COBOL. Regardless of the mistaken name, SQL has emerged as the
dominant access method for relational databases.
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 basic nature of SQL
This section will compare SQL to navigational database query
The beginning of SQL This
section will show how SQL has evolved as the de facto standard for
The SQL optimizer This
will be a brief introduction to the process of SQL optimization.
The goals of SQL tuning
This will cover the overall goals of SQL tuning.
SQL tuning as a phase of Oracle tuning
This section will explore how SQL tuning fits in to the
overall tuning model.
The barriers to SQL tuning
This section discusses the problems encountered when attempting to
tune Oracle SQL.
The process of SQL tuning
This section will explore the general goals for tuning an
individual SQL statement.
Our SQL tuning toolkit
This section will introduce the toolkit that we will be using
throughout the book for examining SQL statements for tuning.
The Basic Nature of SQL
The SQL standard proposal was originally
created as an alternative to the cumbersome navigational languages
of existing databases. In the 1960s, the IBM IMS database was the
only large-scale commercial database management system. Unlike
databases on the relational model, IMS is a hierarchical database
with an internal pointer structure used for navigating between
The navigational database access tools
required the programmer to navigate thorough 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:
MOVE 'JONES' TO CUST-DESC.
OBTAIN CALC CUSTOMER.
MOVE CUSTOMER-ADDRESS TO OUT-REC.
FIND FIRST ORDER WITHIN CUSTOMER-ORDER.
PERFORM ORDER-LOOP UNTIL END-OF-SET.
OBTAIN FIRST ORDER-LINE-REC WITHIN ORDER-LINE.
PERFORM ORDER-LINE-LOOP UNTIL END-OF-SET.
FIND NEXT ORDER WITHIN CUSTOMER-ORDER.
OBTAIN NEXT ORDER-LINE-REC WITHIN ORDER-LINE.
MOVE QUANTITY-ORDERED TO OUT-REC.
OBTAIN OWNER WITHIN ORDER-LINE-PRODUCT
MOVE PRODUCT-NAME TO OUT-REC.
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.
Figure 1: A navigational database query
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.
customer_name = ‘JONES’
c.cust_nbr = o.cust_nbr
o.order_nbr = l.order.nbr
l.product_nbr = p.product_nbr
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 non-relational 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.