|
|
|
Oracle:
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 B
ytes=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=1
0)
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
Byte
s=4)”
|
|
|
|
|
|
