Oracle Performance Tuning - Part 2 - Page 2

Use Efficient SQL

Suppose you have a choice between the following two queries (using the HR schema again):

Query 1

select d.department_id, 
 d.department_name, 
 r.region_name
from departments d, 
 locations l, 
 countries c, regions r
where d.location_id=l.location_id
and l.country_id=c.country_id
and c.region_id=r.region_id;

and

select department_id, 
 department_name, 
 region_name
from departments natural join locations
natural join countries natural join regions;

This leads to four questions.

1.  Are these queries querying for the same result set?

2.  If they are the same, would you expect any difference in their execution plans?

3.  If the plans are the same, what is it that makes these queries different?

4.  Can anything be done to improve the cost?

The answer to the first question is yes, they are the same. The answer to the second question is no, not really, because the same steps are involved in terms of joining tables. The answer to the third question has to do with the amount of typing or coding involved.

The use of the "natural join," "join on" and "right/left outer join" keywords is what matters in this example. If you understand what a natural join is (still joining two tables, but the column names involved are the same), doesn't it look easier to use the second query?

The proof of the answer to the second question is shown below.

Query 1's Execution Plan

Query 2's Execution Plan

As for the answer to the last question, efficient SQL can mean different things to different people. In this case, what about using a view? Will the cost be any different from either of the original queries (you can see for yourself what the answer is), or are there other considerations to take into account?

Suppose we have a view named cost_example, created as follows:

create or replace view cost_example
as
select department_id, department_name, region_name
from departments natural join locations
natural join countries natural join regions;
 

Let's look at a record in the view.

SQL> select department_id, department_name, region_name
  2  from cost_example
  3  where department_id=70;
 
DEPARTMENT_ID DEPARTMENT_NAME                REGION_NAME
------------- ------------------------------ ------------
           70 Public Relations               Europe
 

Out of the three columns or fields, can any of them be changed? If so, why? If not, why not?

Let's suppose the region name is now Asia instead of Europe.

SQL> update cost_example
  2  set region_name = 'Asia'
  3  where region_name = 'Europe';
set region_name = 'Asia'
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Can the department name be changed?

SQL> update cost_example
  2  set department_name = 'PR'
  3  where department_name = 'Public Relations';
 
1 row updated.

The reason why the record in the view can be updated (the department name, anyway) is that DEPARTMENTS is a key-preserved table (its primary key DEPARTMENT_ID was used in the creation of the view).

The point of this example is this: just because you obtain the lowest cost does not mean you cannot do anything else to make a query better. Better, in this case, applies to developers using simpler join constructs, and applies to users in that providing views for their use saves you the effort of having to explain how to do complex joins. The caution on views is to keep track of key-preserved versus non key-preserved tables so that what you intend to be modifiable is indeed just that.

In Closing

The main points of this article are:

• Use bind variables
• Use efficient SQL
• Use coding standards
• Consider the technical or SQL know-how of your user population and create views as appropriate

None of these steps is especially difficult to perform or implement. For programmers used to using the "tableA.column_name = tableB.column_name" format for joins, moving to the use of natural joins saves quite a bit of typing, plus there is the benefit of having key column names match up (the foreign key column in the child table has the same column name as the primary key in the parent table). As shown, some measures may not have a big impact, but when taken as a whole, every little bit helps to improve performance. In Part 3, we will look at more examples of steps you can take to improve performance.

» See All Articles by Columnist Steve Callan