SQL Tutorial - Hints And Tips To OptimiseYour SQL (ctd)
4. Use Selective 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 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.
Also make sure that you're not disabling the use of an index by:-
using an operator on the column (eg. <indexed_col> + 1);
the use of hints, if you're running Oracle(only applies if you're using
the cost based optimiser);
using NULL and not equal checks. (eg. <indexed_col> <>
12345 ; or <indexed_col> IS NULL)
5. Use Full-Table Scans
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.
6. Optimise Joins
All other things being equal, the driving table is the one listed LAST
in the FROM clause, when using the 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 the cost-based optimiser, ensure that all the tables in
the join have been analysed (ask your dba), if they haven't, this may well
cause poor performance. 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. You can also experiment with the order of the tables
in the join.
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:
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.
An alternative is to use the ANSI standard outer join format which has the
advantge 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:
7. 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 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.
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.
This tutorial just scratches the surface of the subject, for more help see
our Advanced SQL Tutorial
---------------------------------------
Subscribe
to our Oracle ezine and sky-rocket your performance.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
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. UNIX is a registered trademark
of The Open Group in the United States and other countries.
