 |
|
Oracle Concepts by Burleson
Consulting |
SQL Query Re-Formulation
This exercise demonstrates how you can
re-formulate an Oracle query to make the execution plan more
efficient.
Your challenge is to write an SQL query that
replicates the output from the following query, replacing the outer
join with a non-correlated subquery. The intent of this query is
to display the names of all authors who have not yet written a book.
-- Find
authors without any books
select
a.author_key,
author_last_name
from
author a,
book_author ba
where
a.author_key = ba.author_key(+)
and
ba.author_key is null
;
Re-write the above query as a non-correlated
subquery and use the autotrace utility to show any differences in the
execution plans for the queries. Submit the listing to your instructor
and describe which form of this query is the most readable and will
execute fastest.
ANSWER
SQL> -- Find
authors without any books
SQL> select
2 a.author_key,
3 author_last_name
4 from
5 author
a,
6 book_author ba
7 where
8 a.author_key =
ba.author_key(+)
9 and
10 ba.author_key is null
11 ;
AUTHOR_KEY
AUTHOR_LAST_NAME
----------- ----------------------------------------
A108 mee
A107 clark
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=3 Card=25 Bytes=350)
1 0 FILTER
2 1 HASH JOIN (OUTER)
3 2 TABLE ACCESS (FULL) OF 'AUTHOR' (Cost=1
Card=10 Bytes=100)
4
2 TABLE ACCESS (FULL) OF 'BOOK_AUTHOR' (Cost=1 Card=25 Bytes=100)
SQL>
SQL> -- Find authors without any books
SQL> select
2 author_key,
3 author_last_name
4 from
5 author
6 where
7 author_key not in (select author_key from book_author);
AUTHOR_KEY AUTHOR_LAST_NAME
----------- ----------------------------------------
A107 clark
A108 mee
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=1 Card=1 Bytes=10)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'AUTHOR' (Cost=1
Card=1 Bytes=10)
3 1 TABLE ACCESS (FULL) OF 'BOOK_AUTHOR' (Cost=1
Card=1 Bytes=4)"pop up window, AJS]
The following questions are purely for you to
measure your level of understanding. After reading each of the
following questions, answer the question in your own words. Post
your answers on the discussion forum, and read other students’
answers.
1. What is the difference between a
declarative data access language and a navigational data access
language?
Answer: A navigational data language requires
knowledge of the internal tables and index structures. SQL is a
better database access language because the SQL optimizer takes care
of the internal navigation.
2. What decisions does the SQL optimizer need
to make when deciding upon the optimal execution plan?
Answer:
A) The order that the tables will be joined
together
B) The choice of indexes
C) The internal joins methods (sort merge,
hash, nested loops).
D) The method to sort the result set (use an
index or invoke a sort)
3. Why is data independence an important feature of relational
databases and SQL?
Answer:
Data independence means that related tables
can be joined at any time without having to pre-establish the
relationship. Hence, any two tables will a common column can be
joined together at runtime.
4. Why is the choice of the SQL optimizer goal
an important consideration when tuning an SQL statement?
Answer:
Different optimizer modes have different goals
and different internal weights. The all_rows goal favors
full-table scans and minimization of machine resources while the
first_rows optimizer goal favors index-scans and return data back to
the requestor as soon as possible.
5. Why is database design important to SQL
performance? How can databases be designed to make SQL
statements run faster?
Answer:
By introducing redundancy into the data model
(denormalization) expensive table joins can be avoided.
6. Why is searching for large-table full-table
scans critical to SQL tuning?
Answer:
If the optimizer gets confused or cannot find
an appropriate index that matches the WHERE clause, the optimizer will
read every row in the table. Hence large-table full-table scans
often indicate a missing index or a sub-optimal choice of optimizer
goal.
7. What is the relationship between indexes
and SQL performance?
Answer:
The sole purpose of indexes is to make SQL
queries run faster. If the optimizer detects an indexes that
matches part of the WHERE clause of the query, then the optimizer will
use the index to avoid having to read every row in the table.
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
|
|
