 |
|
Oracle Concepts by Burleson
Consulting |
Writing Efficient SQL
This tutorial is concerned with the techniques
for ensuring that your SQL executes efficiently. We will examine
the problem of multiple solutions to SQL queries and shows the
guidelines used for writing efficient SQL. In order to evaluate
the efficiency of an SQL statement, the student must learn how to view
the execution plan for the SQL statement, and understand how to
interpret the output from an execution plan.
The student must also understand the tkprof
utility and understand how tkprof is used to display the amount of
time spent parsing, execution and fetching for the SQL statement.
The writing of efficient SQL also includes a
full understanding of Oracle’s automatic query re-write facility, and
those conditions where Oracle dynamically restructures Oracle queries
to improve execution plans.
The student will also understand Oracle
materialized views and see how the creation of a materialized view can
dramatically improve SQL performance. The student will also be
exposed to the materialized view re-write options including EXACT and
SIMILAR.
As a Oracle database administrator, the
student must also be able to review the SQL from the developer and
end-user community and become proficient in evaluating and
restructuring the query to improve performance.
This chapter will also examine efficient
PL/SQL coding techniques.
Learning Outcomes
Upon completion of this tutorial, you will be
able to:
* Use temporary tables to improve SQL
performance
* Learn to implement query re-write within an
Oracle database
* Describe and use Materialized views
* Learn Oracle index structures
* Implement techniques for indexing strategies
Readings
* Donald K. Burleson – Oracle High-performance
SQL Tuning, Osborne McGraw-Hill, 2002
o Chapter 18: This chapter discusses tuning
aggregation queries with temporary tables and how temporary tables
improve SQL execution speed.
o Chapter 20: This chapter deals with finding
indexing opportunities and the sequence for extracting SQL Statements,
adding indexes and verifying performance improvements. We also
cover the various types of indexes, including bitmap indexes,
function-based indexes and reverse key indexes.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
