Project / Support Center
Welcome, Guest. Please login or register. September 29, 2021, 02:14: AM
Home Help Search Login Register
D-Web Web Site Creator D - Web Web Site Creator On-line HTML Editor No Programming knowledge required. Web Global Net PayPal-Cart Shopping Cart System PayPal - Kart Shopping Cart System for E-Commerce over the internet, that's easy to use. Web Global Net Newsletter Manager Newsletter Manager On-line Newsletter Creator with Email Subscriber Management.
Ring Central Discount
Web Global Net Web Application & Web Development Project Center  |  Technical Issues  |  Oracle Related  |  Topic: SQL - A Few Hints And Tips On Optimising Your SQL 0 Members and 1 Guest are viewing this topic. « previous next »
Pages: [1] Go Down Send this topic Print
Author Topic: SQL - A Few Hints And Tips On Optimising Your SQL  (Read 8488 times)
« on: August 07, 2007, 07:12: AM »

SQL - A Few Hints And Tips On Optimizing Your SQL

This was intended to be a list of hints and tips that you might find useful when using SQ,L but a mere list of tips would be of little benefit without the knowledge to make use of them, so we've expanded the list to include the explanations to increasing its usefulness and to make it a proper tutorial.

One important point 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. How can you do this ?

1. Use Views

Views are a good way to ensure the same query is re-used as much as possible.
Remember that even just changing the case and spacing of the words could prevent a query from being reused. A view is merely a pre-defined query, the text of which is stored in the database. Therefore by using views you are using exactly the same queries and eliminating the re-parsing overhead. As the load on the database increases this re-parsing overhead becomes more and more significant. Materialized views take the concept one stage further by actually running the query and storing the results in a table

2. Use Stored Procedures

Another way is to use stored procedures which are program units that contain both SQL and logic statements and are stored in the database. Oracle allows the use of PL/SQL and Java stored procedures. Stored procedures and views also have the advantage that the queries in the views/stored procedures have to be tuned only once, not in every place where they're used. Like views, stored procedures also eliminate the overhead of sending the queries from the client to the server as the queries are already on the server.

3. Use Bind Variables

The use of 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 other customer.

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 optimizer);
    * 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. Optimize Joins

    * All other things being equal, the driving table is the one listed LAST in the FROM clause, when using the rule-based optimizer. 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 optimizer, ensure that all the tables in the join have been analyzed (ask your dba), if they haven't, this may well cause poor performance. The most usual way to optimize queries when using the cost-based optimizer 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 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:

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 ...".

   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 understand.
Report to moderator   Logged
Pages: [1] Go Up Send this topic Print 
Web Global Net Web Application & Web Development Project Center  |  Technical Issues  |  Oracle Related  |  Topic: SQL - A Few Hints And Tips On Optimising Your SQL « previous next »
Jump to:  

Login with username, password and session length
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2013, Simple Machines Valid XHTML 1.0! Valid CSS!