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 Tips by Burleson

Truncate: A One-Way Trip

So, how do I remove all the rows from a large table quickly?  The fastest way is to truncate the table.  The TRUNCATE command is also DML , so it also issues an implicit commit and cannot be rolled back.  The TRUNCATE command is so fast because it basically resets the table definition to be a new empty table. 

The database does not actually delete any rows, but because the table definition is now an empty table, the database has no way to actually retrieve the old rows.  As the table begins to grow, the space where the old rows were written gets overwritten with the new rows.  The TRUNCATE  command can get rid of the data in a very large table quickly and efficiently, as long as you do not need to be able to rollback the delete.

SQL> truncate table t1;
Table truncated. 

Because you cannot recover data from a truncated table, it should be used with caution.  You will normally want to delete the rows.

What we need to do now is update our script to drop the tables T1 and T2. 

-- Compute the Stores with above average sales. 

set pages 999 line 74 

-- Clean Up Old Tables
drop table t1;
drop table t2; 

-- create table t1
create table t1 as
select
  store_name,
  avg(quantity) qty
from
  store join sales using (store_key)
group by store_name; 

-- create table t2
create table t2 as
select
  avg(qty) Average_Sales
from t1; 

--  Create the Report 

set feedback off trimspool on 

select
  initcap (store_name) "Store Name"
from
 t1
where
  qty > (select average_sales from t2)
order by qty; 

set pages 999 line 74 feedback on 

Now, I run my script in SQL*Plus.

SQL> @avg_sales 

Table dropped.
Table dropped. 

Table created. 

Table created. 

Store Name
----------------------------------------
Wee Bee Books
Books For Dummies
Eaton Books
Hot Wet And Mushy Books
Borders

I did not turn feedback  off until right before the query ran, because I wanted to demonstrate that the tables were being dropped and recreated.  You can remove those notices by setting feedback off at the beginning of the script. 

There is overhead to creating tables to hold temporary data.  In our script, we create T1 and then query from it twice.  The more we reuse T1, the more efficient it is to create the table.  If you look at the listing below, you will see that most of the processing time was spent creating and dropping the T1 and T2 tables.

SQL> set timing on
SQL> @avg_sales 

Table dropped. 

Elapsed: 00:00:00.04

Table dropped.

Elapsed: 00:00:00.21

Table created.

Elapsed: 00:00:00.23

Table created.

Elapsed: 00:00:00.01

Store Name
----------------------------------------
Wee Bee Books
Books For Dummies
Eaton Books
Hot Wet And Mushy Books
Borders
Elapsed: 00:00:00.02

When you set timing ON, SQL*Plus shows the time spent executing each command.  This is database server time; time spent on the server executing the command.  Noticed that my script used 0.51 seconds to run, but that .49 of that time was spent creating and dropping the tables.  We can rewrite this query using subqueries in place of the tables.

SQL> select
  2    initcap (store_name) "Store Name"
  3  from
  4   (select
  5      store_name,
  6      avg(quantity) qty
  7    from
  8      store join sales using (store_key)
  9    group by store_name) a
 10  where qty > (select
 11                 avg(qty) Average_Sales
 12               from (select
 13                       store_name,
 14                       avg(quantity) qty
 15                     from
 16                       store join sales using (store_key)
 17                      group by store_name))
 18  order by qty; 

Store Name
----------------------------------------
Wee Bee Books
Books For Dummies
Eaton Books
Hot Wet And Mushy Books
Borders

5 rows selected.

Elapsed: 00:00:00.00

In the example above, I replaced each of the tables holding temporary data with the tables defining query.  What I was left with was a query with 3 subqueries, one of which was nested inside a subquery.  Looks inefficient, but when I ran the query, it executed faster that .01 seconds.  So, before making tables to hold temporary data, insure that you will reuse the data enough to overcome the overhead of dropping and recreating those tables. So far, we have seen two ways to get the same information, stores with above average sales.  In SQL, there are many paths to the same answer.  Here is another method using the HAVING  clause.

SQL> select
  2     store_name,
  3     sum(quantity) store_sales,
  4     (select sum(quantity)
  5      from sales)/(select count(*)
  6                   from store) avg_sales
  7  from
  8    store  s,
  9    sales  sl
 10   where
 11     s.store_key = sl.store_key
 12  having
 13     sum(quantity) > (select sum(quantity)
 14                      from sales)/(select count(*)
 15                                   from store)
 16  group by
 17     store_name
; 

STORE_NAME                               STORE_SALES  AVG_SALES
--------------------------------------- ----------- ----------
books for dummies                              13000      11095
borders                                        21860      11095
eaton books                                    12120      11095
hot wet and sexy books                         24700      11095
wee bee books                                  13700      11095

Elapsed: 00:00:00.00

Notice in the example above that the results are the same stores but in a different order.  Since we did not order the results, they are listed as the database found them as it accessed the tables.  Different access paths return results in different orders.

Finally there is another method that is available with ANSI SQL using the with clause.  The with clause comes at the beginning of the query and is used to create tables in memory that can be used in the query.  In Oracle you need to use a SQL hint to insure that the database creates these tables once and uses them over and over again.  The query look a bit confusing because you define the with clause first.  Basically the with clause replaces the CTAS.

with sumsale as
  (select /*+ materialize */
             sum(quantity) totalsales
   from
     sales) 

The statement above is the same as:

create table sumsale as
select
  sum(quantity) totalsales
from
  sales; 

You just don’t have the overhead of actually creating a physical table in the database. 

 SQL> with sumsale as
  2    (select /*+ materialize */
  3       sum(quantity) totalsales
  4     from sales)
  5  select
  6     store_name,
  7     sum(quantity) store_sales,
  8     (select totalsales from sumsale)/(select count(*)
  9                                       from store) avg_sales
 10  from
 11     store  natural join sales
 12  having
 13     sum(quantity) > (select totalsales
 14                      from sumsale)/(select count(*)
 15                                     from store)
 16  group by
 17     store_name
 18  ; 

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

Notice that we create the sumsale table in memory and queried from it twice.  The with clause is just one of the advanced SQL capabilities that Oracle implements.

Download your Oracle scripts now: www.oracle-script.com The definitive Oracle Script collection for every Oracle professional DBA