Oracle Database 11g SQL Tuning

One of the main strengths of SQL is that you don't have to tell the database exactly how to obtain the data requested. You simply run a query specifying the information you want, and the database software figures out the best way to get it. Sometimes, you can improve the performance of your SQL statements by "tuning" them. In the following sections, you'll see tuning tips that can make your queries run faster; later, you'll see more advanced tuning techniques.

@47383 This is an excerpt from Chapter 16 of the book "Oracle Database 11g SQL" by Jason Price, copyright 2008

from Oracle Press, a division of McGraw-Hill. Click here to download the full chapter.

In this chapter, you will do the following:

  • Learn about SQL tuning
  • See SQL tuning tips that you can use to shorten the length of time your queries take to execute
  • Learn about the Oracle optimizer
  • See how to compare the cost of performing queries
  • Examine optimizer hints
  • Learn about some additional tuning tools

Introducing SQL Tuning

One of the main strengths of SQL is that you don't have to tell the database exactly how to obtain the data requested. You simply run a query specifying the information you want, and the database software figures out the best way to get it. Sometimes, you can improve the performance of your SQL statements by "tuning" them. In the following sections, you'll see tuning tips that can make your queries run faster; later, you'll see more advanced tuning techniques.

Use a WHERE Clause to Filter Rows

Many novices retrieve all the rows from a table when they only want one row (or a few rows). This is very wasteful. A better approach is to add a WHERE clause to a query. That way, you restrict the rows retrieved to just those actually needed.

For example, say you want the details for customer #1 and #2. The following query retrieves all the rows from the customers table in the store schema (wasteful):

-- BAD (retrieves all rows from the customers table)
SELECT *
FROM customers;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- --------- ------------
1 John Brown 01-JAN-65 800-555-1211
2 Cynthia Green 05-FEB-68 800-555-1212
3 Steve White 16-MAR-71 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue 20-MAY-70

The next query adds a WHERE clause to the previous example to just get customer #1 and #2:


-- GOOD (uses a WHERE clause to limit the rows retrieved)
SELECT *
FROM customers
WHERE customer_id IN (1, 2);
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- --------- ------------
1 John Brown 01-JAN-65 800-555-1211
2 Cynthia Green 05-FEB-68 800-555-1212

You should avoid using functions in the WHERE clause, as that increases execution time.

Use Table Joins Rather than Multiple Queries

If you need information from multiple related tables, you should use join conditions rather than multiple queries. In the following bad example, two queries are used to get the product name and the product type name for product #1 (using two queries is wasteful). The first query gets the name and product_type_id column values from the products table for product #1. The second query then uses that product_type_id to get the name column from the product_types table.

-- BAD (two separate queries when one would work)
SELECT name, product_type_id
FROM products
WHERE product_id = 1; 


NAME PRODUCT_TYPE_ID
------------------------------ ---------------
Modern Science 1


SELECT name
FROM product_types
WHERE product_type_id = 1; 

NAME
----------
Book

Instead of using the two queries, you should write one query that uses a join between the products and product_types tables. The following good query shows this:


-- GOOD (one query with a join)>
SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id
AND p.product_id = 1; 

    
     
NAME NAME
------------------------------ ----------
Modern Science Book 
    

This query results in the same product name and product type name being retrieved as in the first example, but the results are obtained using one query. One query is generally more efficient than two.

You should choose the join order in your query so that you join fewer rows to tables later in the join order. For example, say you were joining three related tables named tab1, tab2, and tab3. Assume tab1 contains 1,000 rows, tab2 100 rows, and tab3 10 rows. You should join tab1 with tab2 first, followed by tab2 and tab3.

Also, avoid joining complex views in your queries, because doing so causes the queries for the views to be run first, followed by your actual query. Instead, write your query using the tables rather than the views.

Continue reading this chapter by downloading a free .pdf of SQL Tuning from Oracle Database 11g SQL.

This was first published in January 2008

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close