BN.com Gift Guide

Oracle High-Performance SQL Tuning / Edition 1

Paperback (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $8.46
Usually ships in 1-2 business days
(Save 85%)
Other sellers (Paperback)
  • All (13) from $8.46   
  • New (7) from $37.26   
  • Used (6) from $8.46   

Overview

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.

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.

Read More Show Less

Product Details

  • ISBN-13: 9780072190588
  • Publisher: McGraw-Hill Professional Publishing
  • Publication date: 7/27/2001
  • Series: Oracle Press Series
  • Edition number: 1
  • Pages: 656
  • Sales rank: 1,447,570
  • Product dimensions: 7.20 (w) x 9.10 (h) x 1.47 (d)

Meet the Author

Don Burleson (Kittrell, NC) has 20 years of experience as a database administrator and has written 9 books on database management including Oracle SAP Administration (O’Reilly), High-Performance Oracle8 Tuning, High-Performance Oracle Data Warehousing, High-Performance Oracle Database Applications (Coriolis) and Oracle8 Tuning, German Edition (Sybex). Don has taught more than 100 University courses and regularly conducts in-house Oracle training.

Read More Show Less

Read an Excerpt

Chapter 1: Introduction to SQL

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 languages.
  • The beginning of SQL This section will show how SQL has evolved as the de facto standard for database access.
  • 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 into 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 database records.

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.

The Beginnings of SQL

In 1970, Dr. Edgar Codd of IBM and Chris Date developed a relational model for data storage. In the model, data would be stored in simple linear structures called "relations" or "tables." One of the best improvements of the relational model over its predecessors was its simplicity. Rather than requiring users to know dozens of navigational data manipulation Language (DML) commands, the relational model introduced a declarative language called SQL to simplify data access and manipulation.

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:

  • Simplicity The concept of tables with rows and columns is extremely simple and easy to understand. End users have a simple data model. Complex network diagrams used with the hierarchical and network databases are not used with a relational database.
  • Data independence Data independence is the ability to modify data structures (in this case, tables) without affecting existing programs. Much of this ability comes because tables are not hard-linked to one another. Columns can be added to tables, tables can be added to the database, and new data relationships can be added with little or no restructuring of the tables. A relational database provides a much higher degree of data independence than do hierarchical and network databases.
  • Declarative Data Access The SQL user specifies what data is wanted, and then the embedded SQL, a procedural language, determines how to get the data. In relational database access, the user tells the system the conditions for the retrieval of data. The system then gets the data that meets the selection conditions in the SQL statements. The database navigation is hidden from the end user or programmer, unlike in a CODASYL DML language, where the programmer had to know the details of the access path.
In the marketplace, the declarative data access capability was far more interesting than the internal storage components of the relational database, and SQL became synonymous with the relational model.

A Model for SQL

The first model of SQL can be thought of as having three categories of function: Define, Manipulate, and Authorize.
  • Define refers to the data definition language (DDL) that performs object create, drop, and alter functions.
  • Manipulate refers to the data manipulation language (DML) that performs select, insert, update, and delete functions.
  • Authorize refers to the control that performs grant and revoke functions.
Within the Manipulate functions, we see three dimensions to SQL, select, project, and join. These three simple metrics define the whole functionality of SQL.
Select Operation
A select operation reduces the length of a table by filtering out unwanted rows. By specifying conditions in the where clause, the user can filter unwanted rows out of the result set, as shown in Figure 1-2. In sum, the select operation reduces the results vertically.
Project Operation
Just as the select operation reduces the number of rows, the project operation reduces the number of columns. The column names specified in the SQL select determine those columns that are displayed, as shown in Figure 1-3. In sum, the project operation reduces the size of the result set horizontally.
Join Operation
A join operation such as is shown in Figure 1-4 is used to relate two or more independent tables that share a common column. In a join, two or more independent tables are merged according to a common column value....
Read More Show Less

Table of Contents

PART I Background

1 Introduction to SQL

2 Overview of Oracle SQL Extensions

3 Understanding SQL Execution

4 Overview of the SQL Optimizers

5 SQL Internal Processing

6 Tuning SQL Table Access
PART II Basic SQL Tuning

7 The Steps to Oracle SQL Tuning

8 Understanding Oracle SQL Utilities

9 Locating Significant SQL Statements

10 Tuning Full-Table Scans and Parallel Query

11 Optimizing Sorting for Oracle SQL Statements

12 Tuning with Oracle Hints

13 Tuning with Optimizer Plan Stability

14 Tuning with the Cost-Based Optimizer

15 Tuning with the Rule-Based Optimizer

16 Tuning Table Joins
PART III Advanced SQL Tuning

17 Tuning SQL DML Statements

18 Tuning SQL with Temporary Tables

19 Tuning SQL Subqueries

20 Tuning SQL with Indexes

21 Tuning Data Warehouse SQL

22 SQL Tuning with STATSPACK

23 Tuning SQL with Built-in Functions and Special OperatorsIndex
PART I Background

1 Introduction to SQL

2 Overview of Oracle SQL Extensions

3 Understanding SQL Execution

4 Overview of the SQL Optimizers

5 SQL Internal Processing

6 Tuning SQL Table Access
PART II Basic SQL Tuning

7 The Steps to Oracle SQL Tuning

8 Understanding Oracle SQL Utilities

9 Locating Significant SQL Statements

10 Tuning Full-Table Scans and Parallel Query

11 Optimizing Sorting for Oracle SQL Statements

12 Tuning with Oracle Hints

13 Tuning with Optimizer Plan Stability

14 Tuning with the Cost-Based Optimizer

15 Tuning with the Rule-Based Optimizer

16 Tuning Table Joins
PART III Advanced SQL Tuning

17 Tuning SQL DML Statements

18 Tuning SQL with Temporary Tables

19 Tuning SQL Subqueries

20 Tuning SQL with Indexes

21 Tuning Data Warehouse SQL

22 SQL Tuning with STATSPACK

23 Tuning SQL with Built-in Functions and Special Operators
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)