> In my case ( I have more than 500,000,000 rows) I had to > 'select * into new_big_table from big_table' > it was faster and didn't kill server. > As a bonus, you could 'CLUSTER' your big table if add > 'order by somekey'; > > After that, dont' forget to recreate indices and then you could > drop big_table and 'alter table new_big_table rename to big_table'.
That's OK under certain circumstances. If you have lots of dependencies (views, rules, triggers, etc.) it can turn into a royal pain. For example if you have big_table_view which refers to big_table you might expect to be able to: alter table big_table rename to big_table_old; create table big_table as select * from big_table_old; drop table big_table_old; But you would be wrong. Instead you will find that big_table_view now refers to big_table_old so you can't drop the old table till you drop the view. You will then have to recreate the view. Of course there can be numerous views referring to the table and other views referring to some of those views and pretty soon the whole thing can become a terrible mess. Cheers, Steve ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
