Tutorial - A Few Hints And Tips On How To Optimize Your SQL
Oracle SQL tutorial started as a list of SQL tuning tips but that's of
limited use on its own so we've expanded the tips to explain how and
why they work to make this a more complete Oracle SQL tutorial.
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 statements
which are exactly the same as previously executed
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).
benefit of being able to reuse queries? This saves the time and
resources required to parse the statement
and determine the execution plan thereby making your Oracle database
much more efficient, able to handle greater loads and enabling your
applications to scale to support more users.
started with this Oracle SQL tutorial.
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 on which
they are based) is stored in the database in the data dictionary. This
means that by
using views in all your programs, by definition you are using exactly
the same queries every time and therefore eliminating the re-parsing
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, hence the view is said to be materialized. This doesn't
just eliminate the relatively small parsing overhead it eliminates
completely the overhead from running the query as well.
& Tricks to
SKYROCKET Your Career!
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
views are commonly used in data warehouses where information (e.g.
sales data) is required at various levels and by various
(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
The advantage of
using materialized views is that they can be managed completely by
the database, being refreshed automatically when any of the underlying
tables change or on pre-defined schedule 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). See the Oracle
Database Concepts manual for more details.
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
SQL tutorial part 2: using stored procedures
examines why you should use bind variables in
4 of this Oracle SQL tutorial examines why you should use
only selective indexes in your queries.
5 explains why sometimes you should not
use an index in your queries
6 explains how to optimise join queries for peak performance.
7 explains why it is better to name the columns in your SQL
To learn more
about Oracle, see our Oracle training
page for information about formal training either on-line or
For SQL tips
for SQL Server database see SQL
Analysis-The REAL WORLD presents eBooks on Business Analysis,
based on real world experiences, covering specific topics of
documenting business processes and literally walks a Business Analyst
through each step of an IT project to gather and document business