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):
The next query adds a WHERE clause to...
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
the previous example to just get customer #1 and #2:
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.
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:
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.
