Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Concepts by Burleson Consulting

Use Oracle Temporary Tables


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

   sum(quantity)                                                store_sales,
   (select sum(quantity) from sales)/(select count(*) from store) avg_sales
   store  s,
   sales  sl
   s.store_key = sl.store_key
   sum(quantity) > (select sum(quantity) from sales)/(select count(*) from store)
group by

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:


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:

   store_sales > (all_sales / nbr_stores)


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.


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> 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> 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_na

Table created.

Elapsed: 00:00:00.01
SQL> select
  2  store_name
  3  from
  4  t1,
  5  t2,
  6  t3
  7  where
  8  store_sales > (all_sales / nbr_stores)
  9  ;

books for dummies                                                              
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:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.