SQL Tutorial - A Few Hints And Tips On Optimising Your SQL

This started out as 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 ?

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 overehead. As the load on the database increases this re-parsing overhead becomes more and more significant. Materialised views take the concept one stage further by actually running the query and storing the results in a table

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, Java and .NET stored procedures. Stored procedures and views also have the advantage that the queries in them have to be tuned only once, instead of in every place 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.

Use Bind Variables

The use of bind variables in queries makes them generic and therefore re-usable which means they are parsed only once and executed mulyiple times, thereby increasing application sclability.

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.

How else can you optimise your sql ? Continue this SQL tutorial

This tutorial just scratches the surface, for more help see our Advanced SQL Tutorial. You might also be interested in our other Oracle tutorials.

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

Subscribe to our ezine and sky-rocket your Oracle knowledge.

Smartsoft Computing Ltd
Bristol
England
Tel: 0845 0031320
Contact Us

Click here to view our privacy policy .

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

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