Oracle training, tutorials and consulting


For Better, Faster, Smarter, Oracle Solutions


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

Optimising Joins

The first thing to remember about optimising the performance of joins is that the performance can decrease considerably as a 3rd or 4th or even 5th table is added to the query. The more tables that are added, the more important it is to ensure that the query is properly tuned and tested.

There are several reasons why performance may be degraded as more tables are added to the query.

Designing Oracle Databases For Good Performance

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 .

With that in mind then, there are two ways of maximising performance, the first is to design your Oracle database to minmise the number of joins required and to minimise the number of tables in the joins. This requires knowledge of the data structure and the processes performed on the data. However, if de-normalisation is performed, the performance of queries may be enhanced at the expense of updates and overall flexibility in the system (thereby increasing maintenance costs).

The other way to improve performance is obviously to tune the SQL. This can be done with hints when using the Oracle cost-based optimiser, to specify which index/indexes to use or to specify which tables should be accessed by a full table scan, etc. (see and for a discussion on using full table scans and indexes).

When using the rule-based optimiser in Oracle, the query can only be tuned by physically changing the order of the tables lsited in the from clause. All other things being equal (eg indexes available), the Oracle optimiser will use the table listed last in the from clause as the driving table (ie. the table queried first, and the results from which are used to match with the other tables). Changing the where clause may also affect performance, if a more selective query can be used.

Performance Implications Of Joins

Click here for the rest of this article.

Read the next article on Oracle performance tuning in the series and learn how to optimise views.


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

© Copyright Smartsoft Computing Ltd 2001-2009. All rights reserved.

Search for: