Given what it's going to do to the data layout inside of the blocks. You
may want to consider actually migrating the data into another table that
doesn't have the column. It's always nice, assuming you have the time, to
cleanup the datablocks when you have the chance or the need.
--
Robert Fazio
Senior Technical Analyst
dbabob_at_yahoo.com
"Mark D Powell" <mark.powell_at_eds.com> wrote in message
news:178d2795.0108100500.16a38c71_at_posting.google.com...
> shahcm_at_yahoo.com (Chirag) wrote in message
news:<732c49b9.0108100014.55eaa5e_at_posting.google.com>...
> > Dear Choi,
> >
> > It would be second option bcoz while updating column with null values
> > means it won't carry space in the block. And after, then droping
> > column is to make the things as permanent.
> >
> > Chirag Shah
> > Oracle Administrator
> >
> > "Konghun Choi" <ashydays_at_shinbiro.com> wrote in message
news:<dYHc7.15375$2k2.709858_at_news.bora.net>...
> > > I have to drop column from table which have about 2,000,000 rows.
> > >
> > > Which is the less time consuming ?
> > > the one is just 'alter table <table> drop column ...'
> > > the other is 'update <table> set <column to drop> = NULL ,... '
> > > and after 'alter table <table> drop column ...'
> > >
> > > Thanks in advance.
>
> Dropping a column involves updating every row in the table. The
> second example will update every row twice since the length byte still
> exists in the row after you set the column null and then you remove it
> in a second pass. I think you would be better off to perform both
> operations in one pass.
>
> If you want to save the work until a maintenance window is available
> then just mark the column as unused. It will no longer show but the
> column data will still exist until you drop it. This puts off the
> work until a convienent time, but I am of the opinion that unused data
> should be removed from the system.
>
> -- Mark D Powell --
Received on Fri Aug 10 2001 - 17:05:42 CDT
