For Better, Faster, Smarter Oracle Consulting and Training

SQL Tutorial - A Few Hints And Tips On Optimising Your SQL This was intended to be a list of hints and tips that you might find useful when using SQL 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. Materialised views take the concept one stage further by actually running (materializing) 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 WHERE id = 12345; Change it to:- SELECT name,addr FROM custs WHERE id=<cust_id>;  where <cust_id> is a variable. 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 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.  --------------------------------------- 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 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.    Smartsoft  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.