Re: How to delete hugh amount of data?

create table tablename as select ......

Make sure you give appropriate storage options etc..

Carsten Jacobs <carsten.jacobs_at_tool42.com> wrote in message news:39772813.601C6D86_at_tool42.com...
> Thank you for your answer, Brian!
>
> What is actually meant by copy the "wanted" data to a temporary table?
> Is it just
> insert into newtable
> select ... from oldtable;
> Or is there something else for this task which I don't know?
>
>
>
> Brian Peasland schrieb:
> >
> > Carsten,
> >
> > Is there a index on the "mydate" column? If so, you might want to use a
> > hint to suppress the index. Maybe a better solution would be to drop all
> > indexes on that table, delete the data, and then recreate the indexes.
> > Or you might want to try to copy the "wanted" data to a temporary table,
> > truncate the table, and then copy the "wanted" data back into the table.
> > You'll have to experiment to find a solution that gives you the fastest
> > times.
> >
> > HTH,
> > Brian
> >
> > Carsten Jacobs wrote:
> > >
> > > Hi,
> > >
> > > I want to
> > > delete from mytable
> > > where mydate < to_date('2000-07-10 00:00:00','YYYY-MM-DD
 HH24:MI:SS');
> > >
> > > this is about half of the table rows (about 1,200,000).
> > > This takes a long time. Does anybody know a faster way to do this
> > > delete?
> > >
> > > Unfortunetly the truncate command has no where clause, because I'm
 sure
> > > to delete this rows and don't need a rollback.
> >
> > --
> > ========================================
> > Brian Peasland
> > Raytheons Systems at
> > USGS EROS Data Center
> > These opinions are my own and do not
> > necessarily reflect the opinions of my
> > company!
> > ========================================
>
> --
>
> The information transmitted is intended only for the person or entity to
> which it is addressed and may contain confidential and/or privileged
> material. Any review, retransmission, dissemination or other use of, or
> taking of any action in reliance upon, this information by persons or
> entities other than the intended recipient is
> prohibited. If you received this in error, please contact the sender and
> delete the material from any computer.
Received on Mon Jul 24 2000 - 00:00:00 CDT