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.

The more tables involved, the more rows have to be read and the less likely it is that these rows will be cached, thereby resulting in more expensive and slow physical reads from disk.
The more tables involved, the more sorting and filtering has to be performed. These are cpu and memory intensive operations and are likely to degrade performance considerably.
As the size of the result set (the number of rows and columns being returned) increases, the less likely it is that the sort can be performed in memory and the more likely it is that some of the data has to be written to disk to be sorted. Disk access is realtively very slow and is also cpu intensive.
In an interactive environment, the more tables that are involved and the longer the query takes to run, the more likely it is that somebody else is updating the rows of the tables being read, increasing the likelihood that rollback segments will have to be read to ensure a consistent view of the data.

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 www.smart-soft.co.uk/oracle-performance-tuning-part1.htm and www.smart-soft.co.uk/oracle-performance-tuning-part2.htm 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 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.