Tutorial - A Few Hints And Tips On How To Optimize Your SQL
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.
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.
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 ?
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.
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.
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
& 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 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).
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
Oracle 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, contact Smartsoft Computing for Oracle
training in the UK.
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