Most efficient way to perform table operations (spec. drop) on tables having table names that match a certain criteria

Question

I am performing a schema dump of certain tables. This accompanied by dropping the tables that will be dumped. For example I need to drop all tables that start with, let's say, "SRC_". You guys think of a more efficient way of dropping those tables than the script below? Thanks.

begin
  for rec in (select TABLE_NAME
                from ALL_TABLES
               where TABLE_NAME like 'SRC|_%' escape '|'
                 and OWNER = 'SOME_SCHEMA')
  loop
    execute immediate 'drop table ' || rec.TABLE_NAME;
  end loop;
end;
/

Answer 1

Why does the code need to be more efficient? It seems unlikely that you would be dropping large numbers of tables frequently or that it would take enough time to drop tables that it would be worth optimizing.

What trade-offs are you willing to make to cause the code to be more efficient? And what do you mean by "efficient" in this context-- wall clock time? The resources consumed on the server? Something else? The code that you have written is very straightforward and easy to follow. You could complicate things by, for example, submitting multiple jobs that each drop a subset of the tables so that you can have multiple threads working simultaneously. But that would involve making the code much more complicated-- you'd need to assign work to different threads and then coordinate the responses from each thread to verify that all the tables were dropped successfully. It seems most unlikely that this would be a trade-off that you would really want to make though it would reduce the wall clock time needed to drop all the tables.

Answer 2

The only alteration I might make is this:

execute immediate 'drop table ' || rec.TABLE_NAME||' cascade constraints';

This will prevent the statement failing if the table in question is used to enforce a foreign key. The assumption here is that any child tables are also in scope of this exercise. If that assumption is wrong you would want to handle the ORA-02449 exception in other ways.

Otherwise, as Justin says, the performance of DROP TABLE is about as fast as it can be.

"will there be a performance issue when dropping and adding of table columns is needed per table using the approach above?"

This is a different matter. Two different matters in fact. Although if the table contains no data the time taken to add or drop a column is negligible. Whereas there are potential issues when the table contains data.

1. Adding a column. If the column is supposed to be mandatory you will not be able to add the NOT NULL constraint until all the rows are populated with data for the new column. You can do this with a DEFAULT clause when adding the column or as an UPDATE statement; either way it will take time proportional to the number of rows in the table.
2. Dropping a column This action also takes time when the table is populated. That's why Oracle provide SET UNUSED as well as DROP COLUMN. SET UNUSED is way quicker, because it is just a data dictionary change; however it doesn't free up any space. Find out more.
3. Other complications with dropping columns. Attempting to drop a column which is referenced by a foreign key hurls ORA-12992. And dropping columns on a partitioned table can be a total 'mare.

---

"A DEFAULT clause, at least in recent versions of Oracle, should make adding new non-NULL columns take more or less constant time since it's only populating the value in the data dictionary not in every row of the table."

Are you sure? It takes much longer to add a column with a default value when there are over 50000 rows compared to when there are two rows. Adding a column without a default clause is added for comparison.

SQL> select * from t1;

      COL1       COL2       COL3       COL4 C C
---------- ---------- ---------- ---------- - -
7.2000E+75 -7.200E-75                       Y N
1.2346E+14                                  Y N

SQL> set timing on
SQL> alter table t1 add col7 varchar2(1) default 'A'
  2  /

Table altered.

Elapsed: 00:00:00.06
SQL> insert into t1 (col1, col2, col3) select object_id, object_id*-1, rownum from all_objects 
  2  /

56481 rows created.

Elapsed: 00:00:11.35
SQL> alter table t1 add col8 varchar2(1) default 'X'
  2  /

Table altered.

Elapsed: 00:00:01.80
SQL> alter table t1 add col9 varchar2(1) ;

Table altered.

Elapsed: 00:00:00.08
SQL> 

For the record, I'm running almost the latest version....

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Elapsed: 00:00:00.06
SQL> 

Wallclock timings are notoriously unreliable, but the general variations in elapsed time seem pretty consistent.