"seems to be faster" - sure the drop might be faster, but the truncate will take all of the time.

Here, in 9i - I took a 1,000,000 row table (copy of all_objects over and over with a new column ID that is sequential).... I get lots of dirty blocks in the cache, and then truncate it/drop it. Measure time and redo.

Do it all over again, but just drop it.


No major differences....

ops$tkyte%ORA9IR2> select count(*) from big_table;

  COUNT(*)
----------
   1000000

ops$tkyte%ORA9IR2> alter table big_table cache;
Table altered.

ops$tkyte%ORA9IR2> update big_table set id = id+1;
1000000 rows updated.

ops$tkyte%ORA9IR2> select b.object_name, a.dirty, count(*)
  2    from v$bh a,
  3         (select object_name, data_object_id
  4                from user_objects
  5                   where object_name in ('BIG_TABLE','BIG_TABLE_PK')) b
  6   where a.objd = b.data_object_id
  7   group by b.object_name, a.dirty;

OBJECT_NAME                    D   COUNT(*)
------------------------------ - ----------
BIG_TABLE                      N      17869
BIG_TABLE                      Y       2914
BIG_TABLE_PK                   N       1713
BIG_TABLE_PK                   Y        421

ops$tkyte%ORA9IR2> @mystat "redo size"
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size               475776740

ops$tkyte%ORA9IR2> set timing on
ops$tkyte%ORA9IR2> truncate table big_table;

Table truncated.

Elapsed: 00:00:40.13
ops$tkyte%ORA9IR2> set timing off
ops$tkyte%ORA9IR2> @mystat2
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size               476217144          440,404

ops$tkyte%ORA9IR2> @mystat "redo size"
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size               476217144

ops$tkyte%ORA9IR2> set timing on
ops$tkyte%ORA9IR2> drop table big_table;

Table dropped.

Elapsed: 00:00:00.06
ops$tkyte%ORA9IR2> set timing off
ops$tkyte%ORA9IR2> @mystat2
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size               476289972           72,828

ops$tkyte%ORA9IR2>

520k of redo, about 40 seconds...

ps$tkyte%ORA9IR2> select count(*) from big_table;

  COUNT(*)
----------
   1000000

ops$tkyte%ORA9IR2> alter table big_table cache;
Table altered.

ops$tkyte%ORA9IR2> update big_table set id = id+1;
1000000 rows updated.

ops$tkyte%ORA9IR2> select b.object_name, a.dirty, count(*)
  2    from v$bh a,
  3         (select object_name, data_object_id
  4                from user_objects
  5                   where object_name in ('BIG_TABLE','BIG_TABLE_PK')) b
  6   where a.objd = b.data_object_id
  7   group by b.object_name, a.dirty;

OBJECT_NAME                    D   COUNT(*)
------------------------------ - ----------
BIG_TABLE                      N      19183
BIG_TABLE                      Y       3610
BIG_TABLE_PK                   N       1593
BIG_TABLE_PK                   Y        525

ops$tkyte%ORA9IR2> @mystat "redo size"
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size               951603616

ops$tkyte%ORA9IR2> set timing on
ops$tkyte%ORA9IR2> drop table big_table;

Table dropped.

Elapsed: 00:00:44.95
ops$tkyte%ORA9IR2> set timing off
ops$tkyte%ORA9IR2> @mystat2
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size               951711824          108,208

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> spool off

Less overall redo, about the same time...


I'd just drop it.

Or truncate it, drop it...

doesn't really matter...