How To Optimise Joins To Ensure Your Oracle Database Is Operating At Peak Performance

Introduction

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:-

Definitions

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).

Learn how to develop your Oracle systems better, faster and smarter with high quality Oracle training from our top consultants . See the Oracle training page for more info or click here for details of our scheduled Oracle courses .

Examples

Let's use the following data to provide concrete examples:

Emp

Id	Name	Dept Id
1	Jack Jones	3
2	Mike Lewis	2
3	Susan Charles	3
4	Anne Malloy	4
5	Bharat Patel	6

Dept

Id	Name
1	HR
2	Sales
3	Marketing

The join of Emp and Dept. on the department id (where emp.dept_id = dept.id) would produce the following result:

Emp.Id	Emp.Name	Dept.Id	Dept.Name
1	Jack Jones	3	Marketing
2	Mike Lewis	2	Sales
3	Susan Charles	3	Marketing

The sql statement to achieve this would be as follows:

SELECT emp.id, emp.name, dept.id, dept.name
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:

There must be a matching column between the two tables (or between each pair of tables if there are more than two), and these columns must either have the same data type or be able to be converted to the same data type (the names of the columns are irrelevant however).
Columns that have the same name in more than one table must be fully qualified with either the full table name or the table alias.

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.

Optimising Joins

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
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses woopra.com to gather statistical information about our visitors. View woopra privacy policy .

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.