> 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
