Oracle SQL Tutorial - A Few Hints And Tips On How To Optimize Your SQL

Initially this Oracle SQL tutorial was just a list of tips for tuning SQL statements, but that's not much use on its own so we've included the reasons why to turn this into a complete Oracle SQL tutorial.

Before we start, there is one important principle regarding Oracle that is important to remember when writing SQL and that is that Oracle caches the compiled SQL and is therefore able to reuse queries which are exactly the same (excluding the value of bind variable) as previously executed queries.

To take advantage of this feature the SQL has to be exactly the same - including case, number of spaces, new lines etc. and has to refer to the same objects (so if you had the same table in different schemas and ran the query as different users, it would have to be re-parsed because it would not be able to use the same object).

What's the benefit of being able to reuse queries ?

The answer is that this saves the time and resources required to parse the statement and determine the execution plan thereby making your Oracle database much more efficient.

Now, let's get started with this Oracle SQL tutorial.

Use Views

One way to ensure that queries are the same is to use views. Views are merely predefined queries. The source code of all views (i.e. the SQL queries on which they are based) is stored in the database. This means that by using views in all your programs, by definition you are using exactly the same queries and therefore eliminating the re-parsing overhead.

Materialized Views

In Oracle 8i and above there is a special type of view called a materialized view. This takes the concept of a view one stage further: - instead of just storing the SQL statement underlying the view, the results of the query are stored in a table, hence the view is said to be materialized

Materialized views are commonly used in data warehouses where information (e.g. sales data) is required at various levels and by various dimensions (e.g. by time: week, month, quarter, year to date etc.; by organization hierarchy: sales team, area, country, region) where it would take a long while and be wasteful to run the query every time somebody wanted it.

The advantage of using materialized views is that they can be managed automatically by the database, being refreshed automatically when any of the underlying tables change and they can be used without having to change a line of code. This is because the optimizer in Oracle will automatically rewrite a query to use a materialized view if it will improve performance (as long as the QUERY_REWRITE_ENABLED initialization parameter is set).

Object Views

Although not strictly useful for improving performance (which is the main subject of this Oracle SQL tutorial), it is worth noting that in Oracle8 and above is another type of view called object views which are used to wrap relational-database tables into an object-oriented database object so that languages like Java can manipulate them more easily.

The use of this type of view would not probably not help run-time performance - they are more useful for application developers to avoid having to map objects onto relational data. One potential disadvantage of object views is that they can only be updated via instead-of triggers (which fire instead of normal update, delete or insert operations - hence the name) or via direct calls to PL/SQL procedures.

The next part of this Oracle SQL tutorial looks at how you can use PL/SQL to improve performance.

Oracle SQL tutorial part 2: using stored procedures

Part3 examines why you should use bind variables in your queries.

Part 4 of this Oracle SQL tutorial examines why you should use only selective indexes in your queries.

Part 5 explains why sometimes you should not use an index in your queries

Part 6 explains how to optimise join queries for peak performance.

Part 7 explains why it is better to name the columns in your SQL queries.

Contact Smartsoft Computing for Oracle training in the UK or for Oracle training and consultancy in New Zealand.

For SQL tips for SQL Server database see SQL Server Pro

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to Oracle Tips and Tricks, you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.