Thinking Out Loud

October 16, 2013

Renaming Table, Constraints, Indexes

Filed under: 11g,oracle — mdinh @ 5:39 am

So there I was, working on another task to create a data subset. This time, keep data for the current year only.

Here’s a similar post, How to rebuild table when you don’t have TOAD

My code has something like: create table tn_backup as select * from tn;

In a code review, a colleague suggested to rename the table since it would be faster.

Renaming the table is fast, but what about all the constraints and indexes on the table? They all will have to be renamed since duplicates are not allowed.

Since the table was relatively simple and did not have any Foreign Key, I thought I would entertain the idea.

Here is the test case Renaming tables test case

The table used in the test case has Foreign Key to it, but just imagine it didn’t.

Another options is to rename the table and drop all the constraints and indexes.

Option 1:
Rename table. Drop all constraints and indexes on the renamed table. Create new table.

Option 2:
Create backup using CTAS, Drop table. Create new table.

Which one would you chose?


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at

%d bloggers like this: