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