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

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

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.