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 ?
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
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.
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.
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. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2009. All rights reserved.