Oracle Performance Tuning - Part 2 - Page 2
September 28, 2005
Use Efficient SQL
Suppose you have a choice between the following two queries (using the HR schema again):
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;
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.
The main points of this article are:
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.