 |
|
Oracle Concepts by Burleson
Consulting |
Use Oracle Temporary Tables
Description
Here is an example of a working query against
the sample database that shows the names of all stores with
above-average sales.
set pages
999;
set timing
on
select
store_name,
sum(quantity)
store_sales,
(select sum(quantity) from sales)/(select count(*) from store)
avg_sales
from
store s,
sales sl
where
s.store_key = sl.store_key
having
sum(quantity) > (select sum(quantity) from sales)/(select count(*)
from store)
group by
store_name
;
While this query provides the correct answer,
it is difficult to read and complex to execute. Note that this
query is using multiple levels of aggregation with scalar subqueries.
Essentially, this query accesses the STORE and
SALES tables, comparing the sales for each store with the average
sales for all stores:
Procedure
Your challenge is to complete the following
steps:
Step 1 – Determine the execution plan for the
above query and gather execution timings for the queries.
Step 2 – Re-write this query to use temporary
tables instead of scalar subqueries.
* Create a table t1 to hold the total sales
for all stores.
Hint: create table t1 as select sum(quantity)
tot_sales from stores;
* Create a table t2 to hold the number of
stores. (select count(*) from stores)
* Create a table t3 to hold the store name and
the sum of sales for each store.
Then, write a fourth SQL statement that uses
tables T1, T2, and T3 to replicate the output from the original query.
Your final query will look like this:
select
store_name
from
t1,
t2,
t3
where
store_sales > (all_sales / nbr_stores)
;
Evaluation
Submit the listing from step 1 and step 2 to
your instructor. Also, provide a 200 word synopsis discussing
the merits of both queries along the dimensions of readability and
optimum execution plan. Include a short summary of your opinions
regarding the best way to solve this problem.
ANSWER
Step 1:
STORE_NAME
STORE_SALES AVG_SALES
---------------------------------------- ----------- ----------
books for dummies
13000 11055
borders
21860 11055
eaton books
12120 11055
hot wet and sexy books
24700 11055
wee bee books
13700 11055
Elapsed:
00:00:00.00
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=7 Card=1 Bytes=27)
1 0 FILTER
2 1 SORT (GROUP BY)
(Cost=7 Card=1 Bytes=27)
3 2 HASH JOIN
(Cost=3 Card=100 Bytes=2700)
4 3
TABLE ACCESS (FULL) OF 'STORE' (Cost=1 Card=10 Bytes=200)
5 3
TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=700)
6 1 SORT (AGGREGATE)
7 6 TABLE
ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=300)
8 1 SORT (AGGREGATE)
9 8 TABLE
ACCESS (FULL) OF 'STORE' (Cost=1 Card=10)
Elapsed
00:00:00.17
Step 2:
SQL> drop
table t1;
Table
dropped.
Elapsed:
00:00:00.00
SQL> create
table t1 as
2 select
3 sum(quantity) all_sales
4 from
5 sales;
Table
created.
Elapsed:
00:00:00.01
SQL>
SQL> drop table t2;
Table
dropped.
Elapsed:
00:00:00.00
SQL> create
table t2 as
2 select
3 count(*) nbr_stores
4 from
5 store;
Table
created.
Elapsed:
00:00:00.00
SQL>
SQL> drop table t3;
Table
dropped.
Elapsed:
00:00:00.00
SQL> create
table t3 as
2 select
3 store_name,
4 sum(quantity) store_sales
5 from
6 store,
7 sales
8 where
9 store.store_key = sales.store_key
10 group by
11 store_name;
Table
created.
Elapsed:
00:00:00.01
SQL>
SQL> select
2 store_name
3 from
4 t1,
5 t2,
6 t3
7 where
8 store_sales > (all_sales / nbr_stores)
9 ;
STORE_NAME
----------------------------------------
books for dummies
borders
eaton books
hot wet and sexy books
wee bee books
Elapsed: 00:00:00.00
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=1723 Card=3446 Bytes=210206)
1 0 NESTED LOOPS (Cost=1723 Card=3446
Bytes=210206)
2 1 MERGE JOIN
(CARTESIAN) (Cost=42 Card=1681 Bytes=80688)
3 2 TABLE
ACCESS (FULL) OF 'T1' (Cost=1 Card=41 Bytes=533)
4 2 BUFFER
(SORT) (Cost=41 Card=41 Bytes=1435)
5 4
TABLE ACCESS (FULL) OF 'T3' (Cost=1 Card=41 Bytes=1435)
6 1 TABLE ACCESS (FULL)
OF 'T2' (Cost=1 Card=41 Bytes=533)
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
|
|
