Oracle Consulting and Training
For Better, Faster, Smarter
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
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
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
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd. All rights reserved.