For Better, Faster, Smarter Oracle Consulting and Training

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-based index • using hints specifying the wrong index(es) or full table scans • 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 Data Warehouse 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 where appropriate. 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:       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 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   Contact Us View our privacy policy 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 linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd  All rights reserved.