|
|
|
Oracle:
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
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.
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
|
|
|
|
|
|
