Tip

Oracle Database 11g SQL Tuning

@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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.