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

Home
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

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

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

 
 
 

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.