Let's very quickly recap the choice of "weapons" you have available to you in your "arsenal" for performance tuning before we start to look at how to optimise joins:-
A join in mathematical terms is the intersection of two sets, expressed in Boolean algerbra as A^B. What this means is that the 2 sets are combined and any overlap btween the 2 sets (those items that exist in both sets) is the intersection i.e. the result of the join. To use a very simple example, if you had two lists of developers: the first list containing the names of those developers who had worked on project A and the second, the list of those developers who had worked on project B, then the intersction of those 2 sets (lists) would those developers who worked on both projects.
With that very important definition of joins out of the way, we can now concentrate on what this means in terms of a relational database (specifically Oracle) and how to optimise them.
The most important thing to remember about joins is that you must specify the join condition. If you don't, then you will get the Cartesian product of the 2 tables involved in the join i.e. every row in table A will appear in the result once for every row in table A, which is another way of saying that the result will consist of x times y rows, where x is the number of rows in table A and y the number of rows in table B. That is very unlikely to be what you want and will do nothing for the performance of your database, so remember always specify the join conditions. In fact there should be n-1 join conditions where n is the number of tables in the join.
Just to complete the definitions: the join condition specifies which columns in each of the tables will be compared and how they will be compared (most of the time the column values are compared for equality - an equi-join, but that doesn't have to be the case).
Let's use the following data to provide concrete examples:
The join of Emp and Dept. on the department id (where emp.dept_id = dept.id) would produce the following result:
The sql statement to achieve this would be as follows:
SELECT emp.id, emp.name, dept.id,
FROM emp, dept
WHERE emp.dept_id = dept_id
This is an example of a simple join - in this case an equi-join because the values in the columns must match - and it shows two important rules:
This type of join is usually made between the foreign key of one table and the primary key of another, often for parent/child (master/detail) relationships, although you can join any related columns.
One more important fact to remember about equi-joins is that only rows that match between the tables will be returned. For example if we were to join emp and dept, but some employees had not been assigned to a department, then these employees would not be returned by the query. Similarly if any departments had no employees assigned to them, then they would not be returned.
This article (part 3) continues here.
Learn how to optimise views in part 4 - the next article on Oracle performance tuning.
Looking for more Oracle tips and tricks ? If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
Send us your questions/feedback and we'll answer them in the next newsletter.
Smartsoft Computing Ltd
Tel: 0845 0031320
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2009. All rights reserved.