Oracle SQL Tutorial -
How To Make Your SQL Lightning-Fast

Tip #6 - Optimise Joins in your SQL

Tip #6 in this Oracle sql tutorial explains how to optimise your joins for maximum performance. This is especially important for OLTP systems.

Unless you're using older releases of the Oracle database you're unlikely to be using the rule-based optimizer, as this has been replaced by the cost-based optimizer. Either way make sure you choose the driving table carefully to ensure the minimum number of rows possible are returned.

The driving table in the query is the first table read and is used to join to all the subsequent tables in the query. Failing to minimize the number of rows returned from the driving table, increases exponentially the amount of work the database has to do for the each successive join.

For example, consider a join of 3 tables - dept (department), emp (employee) and sal_hist (salary history).

Out of these, dept is likely to be the smallest, for our example let's say it has 10 rows. Emp is likely to be the next samllest, let's say it has 2500 rows. Let's say sal-hist has 100,000 rows.

If we start with dept, we only need to read 10 rows, to join to emp we need to perform 10*2500 reads, then to join to sal-hist we need to perform another 10*2500*100000.

On the other hand, if we'd started with sal_hist and joined this to emp and then to dept, the figures would be (100000*2500)+(100000*2500*10) - a significant difference.

When using the rule-based optimzer, all other things being equal, the driving table is the one listed LAST in the FROM clause of your SQL statements. Changing the order of the columns in the join condition does not change which table is used as the driving table.

When using the cost-based optimiser with Oracle databases, ensure that all the tables in the join have statistics on them and that for the small tables these statistics are accurate, otherwise you're likely to get get poor performance.

If the data is skewed (unevenly distributed) then ensure data histograms are generated to enable the optimizer to make a more-informed choice.

The most usual way to optimise SQL when using the cost-based optimiser is to use hints, which instruct the parser as to which indexes should or should not be used, or which tables should be scanned in full.

You can also use hints to change the order of the tables in the join. Usually the Oracle cost-based optimizer will choose the best-driving table automatically based on the statistics for the tables (which is why up-to-date statistics are essential for optimal performance) but if you believe that Oracle has not chosen the best table to start with then give it a hint.

Also make sure join columns are indexed and that the indexes are used by the query (see Oracle SQL tutorial tip #4 - Use Selective Indexes In Your SQL). Remember that indexes can still be used even if the where clause contains a "like" condition but not if there is a "not like" condition.

Use the correct syntax for outer joins, as follows:-

tab1.col1(+)= tab2.col1
or
tab1.col1 = tab2.col1(+)

The bracketed plus sign follows the column of the table which has/may have the missing row.

The final part of this Oracle SQL tutorial explains why you should name the columns required by your SQL queries.

To learn more about Oracle, contact Smartsoft Computing for Oracle training in the UK or Oracle training and consultancy in New Zealand.

Oracle SQL tutorial tip 7 - don't use select *