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 ?
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, 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.
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.
Subscribe to our ezine and sky-rocket your Oracle knowledge.
Smartsoft Computing Ltd
Tel: 0845 0031320
Oracle is a registered trademark of Oracle Corporation
and/or its affiliates.
© Copyright Smartsoft Computing Ltd 2001-2013. All rights reserved.