Oracle Consulting and Training
For Better, Faster, Smarter
SQL Tutorial - Hints And Tips On Optimising
Your SQL (ctd)
This is the second part of our SQL tutorial on how to optimise your queries and provides tips as well as the
rationale behind the tip to help you understand and write better SQL in the future. For part 1 see here.
4. Use Selective B-Tree Indexes
Ensure that tables are accessed via selective indexes, unless the table is very small or very large, in which
case it may be better not to use the index(es) at all. (See tip
#6 - use full table scans).
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.
Also make sure that you're not unintentionally disabling
the use of an index by:-
using an operator on the column (eg.
<indexed_col> + 1) unless this is a function-
using hints specifying the wrong index(es) or full
using NULL and not equal checks. (eg. <indexed_col> <> 12345 ; or <indexed_col>
IS NULL) as null columns are not indexed and the inequality operator disables the use of an index
5. Use Bitmap Indexes For Columns With Low Cardinality In A
Bitmap indexes are useful in a data warehouse environment for columns that are not selective (i.e. there are
many occurrences of the same value) and where the columns are used as filters in queries. These are
unsuitable for OLTP systems as their maintenance overhead is very high - indexes of this type have to be
rebuilt completely whenever a column is updated or a row is inserted or deleted.
6. Use Full-Table Scans
An indexed-read of a very very large, depending on how many blocks are read, may remove everything
else from the buffer cache and degrade the performance of all other queries. Also if a large proportion of
the table is being read then using an index may double the number of reads required - one for the index
block and one for the data block. In these cases a full-table scan is better - only the last few blocks read are
kept in the buffer cache. (Also see tip #4 - use selective b-tree indexes).
Small tables can be pinned in the buffer cache to reduce read time.
7. Optimise Joins
Ensure that all the tables in the join have had statistics gathered for them recently (ask your dba), if they
haven't, this may well cause performance issues.
You should also experiment with the order of the tables in the join and using subqueries instead of joins
When joining (complex) views you may find it better to use the underlying tables instead but beware of
changes to the view not being reflected in your joins and the extra parsing required.
Indexes - these can still be used even if the where clause contains a "like" condition but not if there is a
"not like" condition.
Outer joins - the correct syntax for outer joins using Oracle syntax is:
or tab1.col1 = tab2.col1(+)
The bracketed plus sign follows the column of the table which has/may have the missing row.
An alternative is to use the ANSI standard outer join format which has the advantage that it enables you to
perform a full outer join in one statement:
tab1.col1 left outer join tab2.col1 (return all rows from tab1)
tab1.col1 right outer join tab2.col1 (return all rows from tab2)
tab1.col1 full outer join tab2.col1 (return all rows from tab1 and tab2)
The final tip for this short tutorial is:
8. 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 ...".
1. 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 or have in-line clob or blob columns (all 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.
2. The code is easier to understand, which means you need fewer comments!
3. 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 find.
This tutorial just scratches the surface, for more help see our Advanced SQL Tutorial. You might also be
interested in our other Oracle tutorials.
Looking to sky-rocket productivity, save time and reduce costs?
Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-
led training in Oracle and related technologies on or off site in cities across the UK as well as self-study
online training. See our scheduled Oracle training courses, or let us know your requirements.
Oracle tips and tricks
Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make
your systems better, faster and smarter.
Smartsoft Computing Ltd, Bristol, England
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry
trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd All rights reserved.