Learn How To Optimise Your SQL With This Brief Oracle SQL Tutorial

Introduction

This was originally intended to be a list of hints and tips that you might find useful when using SQL, but as I started writing, I realised that a list of tips would be less useful then a complete sql tutorial, so here it is.

One thing to always to remember is that Oracle caches the compiled form of SQL and is therefore able to re-use queries which are the same as previously executed queries. This saves the time and resources required to parse the statement and determine the execution plan.

Use Views

One way to ensure that queries are the same (including the case and spacing), is by using views which are merely pre-defined queries, the text of which is stored in the database, so by using views in all your programs, by definition you are using exactly
the same queries, thereby eliminating the re-parsing overehead.

Use Oracle Stored Procedures

Another way of ensuring queries are reused is to use stored procedures, which are merely program units that contains both SQL and logic and are stored in the database. Using stored procedures and views has the added advantage that the queries in them have to be tuned only once, not in every place where they're used.

Use Bind Variables

Using bind variables in queries, makes them generic and therefore re-usable. For example, instead of writing a query like :-

SELECT name,addr FROM custs WHERE id = 12345;

Change it to:-

SELECT name,addr FROM custs WHERE id = <cust_id>;

The first query will only be re-used when you request the details for customer number 12345, whereas the second query will be re-used for any customer

Use Selective Indexes

Choose a column that has few duplicate values, columns with just 2 different values (such as 'Y', 'N' or 'M', 'F') are poor choices for a b-tree index (but are suitable for bit-map indexes).

Also make sure that you're not disabling the use of an index by:-

using an operator on the column eg. <indexed_col> + 1, unless it's a function-based index
the use of hints, if you're using the cost based optimiser
using NULL and not equal checks. eg. <indexed_col> <> 12345 ; or <indexed_col> IS NULL

Use Full-Table Scans

If the table is very small or very large, it may be better not to use indexes.

If the table were very small it could be cached completely, or all the columns could be indexed which means only the index would have to be read to satisfy any query.

If the table is very large, depending on how many blocks are read, using an index may remove everything else from the buffer cache and degrade the performance of all other queries. In which case a full-table scan is better - only the last few blocks read are kept in the buffer cache.

Optimise Joins

1. All other things being equal, the driving table is the one listed LAST in the FROM clause, when using Oracle's rule-based optimiser. Changing the order of the columns in the join condition does not change which table is used as the driving table.

Choose the driving table carefully to ensure the minimum number of rows are returned.

When using Oracle's cost-based optimiser, ensure that all the tables in the join have been analysed (ask your dba).

The most usual way to optimise queries 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.

2. Indexes - these can still be used even if the where clause contains a "like" condition but not if there is a "not like" condition.

3. Outer joins - the correct syntax for outer joins is

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.

Name The Columns In A Query

There are three good reasons why it is better to name the columns in a query rather than to use "select * from ...".

Network traffic is reduced. This can have a significant impact on performance if the table has a large number of columns, or the table has a long or long raw column (both of which can be up to 2 Gigabytes in length). These types of columns will take a long time to transfer over the network and so they should not be fetched from the database unless they are specifically required.
The code is easier to understand, which means you need fewer comments!
It could save the need for changes in the future. If you are using views, not only might columns be added to or removed from the view, but the order of the columns could well change - in which case using "SELECT *" at best would
fetch the wrong data and at worst would fail with an Oracle error which might take a long while to understand.

Other Resources

Other valuable Oracle resources and suggestions for further reading are available here

An Advanced Oracle SQL Tutorial is available here

Or for details of our low cost, high quality instructor-led Oracle training, just click here.

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

Need help with your Oracle systems ?

Discover how to develop your systems better, faster and smarter than ever before. Click here and complete the form to have somebody contact you, or call free 0117 942 2508 to talk to one of our consultants with no obligation .

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

Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.

Smartsoft Computing Ltd

Tel: 0845 0031320
Contact Us

Click here to view our privacy policy .

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

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.