Oracle database on chevre.

[Lenny Teytelman]

Guys,

If you want to get rid of all the records in a table and don't care about
rolling back in case of error, DO NOT use the 'delete from ...' command as
is.  The delete command will automatically log every single deletion and
will take forever on a large dataset.

Instead, use the 'truncate table_name' or 'drop table table_name' command.
'Drop table' instantaneously removes everything without logging.  Truncate
works similarly to 'delete', but does not remove the table definition.

Now, suppose you want to delete only certain records rather than
everything.  There is no way to use the drop or truncate commands in this
case.  However, even here you can avoid the delete command.  You  can
simply create a new table with only those records that you want to keep,
and then drop the original table.

So instead of 'delete from tbl_name where id>5000;' do,
'create table tbl_name2 as select * from tbl_name where id<=5000;'
'drop table tbl_name;'
'alter table tbl_name2 rename to tbl_name;'

Of course, you want to use your head in this case.  If you have 10 million
records and want to delete 5, it's better to use the delete command.

Also, it may be possible to turn off logging in a table on case-by-case
basis.

Lenny

> Xiaokang,
> 
> Is it better to delete a whole table or to drop it and re-create it?
> Wei and I have heard conflicting advice on this.
> 
> -Steve
> 
> 
> Xiaokang Pan wrote:
> 
> >  Doreen,
> >
> >       Actually, the database problem happened on Wednesday. When somebody
> >  tried to insert or delete a very large data sets, he/she thought that it
> >  took too long time to finish the job and then quit before the Oracle
> >  system committed the work. As a result, the system had to roll back
> >  the work slowly, which may significantly slow down the database. After
> >  that several people tried to create and drop tables in the database, they
> >  found that it took too long time to finish the jobs, so, they quit again.
> >  The system had to do more roll back work and it slowed down further. I
> >  was trying to shut down the database in normal option so that the system
> >  can be quickly recovered but I couldn't shut down it normally.
> >
> >       I had been trying to fixed this problem since Wednesday. Last night,
> >  I found a method to fix it.
> >
> >       Xiaokang
> >
> > On Sun, 14 Oct 2001, Doreen Ware wrote:
> >
> > > Xiaokang,
> > >
> > > Thanks for taking care of the problem.
> > >
> > > Can you explain to me what happened on Monday.
> > >
> > > Thanks,
> > >
> > > doreen
> > >
> > >
> > >
> > >
> > > >  Ok. The Oracle database on chevre has been fixed. It should be no
> > > >problems when you create, drop tables, or update data in any account.
> > > >Please try it.
> > > >
> > > >     Have a nice weekend!
> > > >
> > > >     Xiaokang
> > >
> > >
> > > --
> > > ======================================================================
> > > Doreen Ware
> > > Cold Spring Harbor Laboratory                 voice: (516) 367-6979
> > > Freeman Bldg.                                 fax: (516) 367-8389
> > > 1 Bungtown Rd.                                        email: ware at cshl.org
> > > Cold Spring Harbor, NY. 11724                 url: www.gramene.org
> > > ======================================================================
> > >
> 
> --
> Steven Schmidt
> snp.cshl.org     www.gramene.org
> Cold Spring Harbor Laboratory
> 516-367-6977
> 
> 
> 
>