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 a particular issue for OLTP systems.

Unless you're using older releases of the Oracle database you will be using the cost-based optimiser, but these tips also apply if you're using the rule-based optimiser.  

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 minimise 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 smallest, 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 optimiser, 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 being joined 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 optimiser 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 optimiser 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.

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer.Subscribe now and ignite your career.

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

Want to learn more about Oracle? Contact Smartsoft Computing forOracle training and consultancy in the UK

Return to Oracle SQL Tutorial part 1