Oracle training, tutorials and consulting

 

For Better, Faster, Smarter, Oracle Solutions

 

How To Optimise Joins To Ensure Your Oracle Database Is Operating At Peak Performance (ctd)

Performance Implications Of Joins

The most important thing to remember when tuning joins in your Oracle application is that you should start with the smallest result set possible. Therefore if you know that one of the tables is a lot smaller than the others, or that only a few rows will be selected from one table, then

 

 

you need to make sure that that table is last in the from clause when using the rule based optimiser. If you're using the Oracle cost-based optimiser, the database should, in theory, automatically pick the best table to start with, however if the query is very complex, you may need to supply hints to say which indexes should or should not be used.

To illustrate what a difference this can make, let's use a document production system as an example containing the following tables:

Let's also assume that to produce a document we need to to join all these tables and that all the columns used in the join conditions are indexed.

An initial attempt might produce the following query:

SELECT doc.name
      ,doc.description
      ,fld.business_name
      ,fld.description
FROM document doc
    ,clause cl
    ,mandatory_document_clauses mdc
    ,field fld
    ,clause_fields cf
WHERE doc.document_id = mdc.document_id
AND   mdc.name        = cl.name
AND   cl.clause_id    = cf.clause_id
AND   cf.field_id     = fld.field_id

In this case, assuming that we're using the rule-based optimiser, CLAUSE_FIELDS would be the driving table as it is listed lasted in the "FROM" clause. As there are no limiting conditions, a full table scan would be made on this table to return 9500 rows. This would require 9500 logical reads.


Learn how to develop your Oracle systems better, faster and smarter with high quality Oracle training from our top consultants . See the Oracle training page for more info or click here for details of our scheduled Oracle courses .

Next, as a join produces the product of the two tables not the union, then (9500*no. of rows in FIELD) searches of the index and 9500 table reads would be made to find matching rows in FIELD. Thus simply joining FIELD and CLAUSE_FIELDS would require (9500 + 2*(9500*3000)) = 57009500 logical reads.

The next step would be to join this result set to CLAUSE which would require 2*95000*3000*1400 logical reads. This result set would then be joined to MANDATORY_DOCUMENT_CLAUSES which would require 2*9500*3000*1400*2000 logical reads. Finally, this result set would be joined to DOCUMENT resulting in 2*9500*3000*1400*2000*725 logical reads.

As you can see the number of reads required for each extra table increases very rapidly. This query as it stands would be extremely slow, so how do we optimise it?

First we need to check to see if there are any tables that can be missed out. There may not be a direct relationship between two tables, but the required column might already exist on another table, or it might be possible to add it. Next we need to find out the sizes of all the tables and put the smallest one last in the from clause (only necessary when using the rule-based optimiser).

Then we need to check that all the join columns are indexed, and make sure that there are no functions or operations (such as "UPPER" or "+ 0") being performed on the columns that would prevent the use of an index (or that function-based indexes are used instead).

Finally, we need to obtain the plan of execution for the query, to make sure the indexes are being used and that the smallest table is read first. Remember that in an explain plan listing you read from inside out and top to bottom. Once we have the execution plan, we can try changing the order of the tables in the "FROM" clause, comparing the execution plan for each and the run time. Small differences in run time can be ignored but differences in orders of magnitude are significant. Obviously if there are other users of the database this will also affect the run time, so you need to be aware of what else is running on the database.

Changing the query above to use document as the driving table would achieve significant performance improvements. The first table access would require less than 1500 reads as opposed to 19000 in the original. Joining document to mandatory_document_clauses would require 1500*2000 logical reads (original: 19000*3000*1400*2000*725). The next join to clause would require 1500*2000*1400 logical reads. Thus a join between three tables starting with the smallest would require just 4,200,000,000 logical reads compared with 79,800,000,000 for the original - 2 orders of magnitude smaller.

SELECT doc.name
      ,doc.description
      ,fld.business_name
      ,fld.description
FROM field fld
    ,clause_fields cf
    ,clause cl
    ,mandatory_document_clauses mdc
    ,document doc
WHERE doc.document_id = mdc.document_id
AND   mdc.name        = cl.name
AND   cl.clause_id    = cf.clause_id
AND   cf.field_id     = fld.field_id

Summary

In summary, the best way to optimise a join is to ensure that each join involves only a few tables. If your Oracle database has a large number of queries requiring 4 or 5 or more tables to be joined than it suggest that your database design should be revisited to minimise the number of joins required.

After that, make sure that the smallest result set is created first (either use hints or rearrange the order of the tables in the join clause). There are usually several ways in which a query may be written, and it is therefore best to try several alternatives particularly for complex queries before deciding on the preferred one.

Also ensure that the join columns are indexed and that the query does not inadvertently prevent the use of indexes by using functions such as "upper" or making it part of an expression eg. where <indexed_field> + 1 = <expression>. In Oracle 8i and above, you can have function-based indexes but these have to be specifically created for each function. You must also be aware that the creation of new indexes may affect the performance of unrelated queries and that de-normalising tables is a trade off between faster queries and slower updates (and reduced flexibility).

Read the next article on Oracle performance tuning in the series and learn how to optimise views.

---------------------------------------

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
Bristol, England

Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses woopra.com to gather statistical information about our visitors. View woopra privacy policy .

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.

Search for: