How To Make Your SQL Lightning-Fast
- 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.
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.
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.
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
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
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
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
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:-
plus sign follows the column of the table which has/may have the
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.
SQL tutorial tip 7 - don't use select *