Deleting Large No. of Rows

[edsperez]

Guys,

I need to delete about 1M+ rows of data everyday from a single table, using the 'delete from table where.....' statement will take some time, and the truncate command will delete all the rows in the table.

Can you give me suggestion on how to implement this using the lease possible time. Is there a 'truncate from table where.....' command.


Ed

[nirasha]

From my knowledge there is no way to use a truncate "where"

Good luck,
Nirasha

[marist89]

If this is an ongoing issue, you may want to look at partitioned tables. You can truncate a single partition of a table.

[gpsingh]

PArtition by a day , lot of mantainence,and if u have dependent tables on this table , a lot more work,
Why not write a procedure and delete.

[edsperez]

Deleting 1M+ rows whether by procedure or through any other application will consume a considerable amount of time.

Using truncate will delete all rows in the table, and I don't want to do that.

Here is the scenaro.
Daily I am receiving 1M+ of inserts in the table. I just need to keep 1 months worth of data, so I need to do daily delete on that table

Got any other idea?

Thanks,

Ed

[scotta]

Well if you have 8i and are not worrying about recovery
you can use the "NO LOGGING" option. I use this all the
time and it increases delete time substantially
scotta

[aracnid]

you can goo for exp imp (if your version is 8*) using the QUERY -option, (select clause used to export a subset of a table)...so you may import the table with the subset of records you want (or may have e test in table in import on the end of it rename to the old name)

[jmodic]

Originally posted by edsperez
Here is the scenaro.
Daily I am receiving 1M+ of inserts in the table. I just need to keep 1 months worth of data, so I need to do daily delete on that table

This is the perfect scenario for using partitions, as Jeff allready mentioned.

NOLOGGING doesn't help with delets at all.

[Fiona]

As well as NOLOGGING, I use DELETE_COMMIT (while logged in as SYS) with COMMIT interval set at 100,000 (rows) or so. Here's an example:

EXEC DELETE_COMMIT('delete /*+ (tablename index)*/ from tablename where column=constraint',interval number);

This then will perform COMMIT at set interval, which will prevent Rollback segments going loopy, as well as saving some time.

Fiona

[tamilselvan]

It is better to change the design. Add a column, deleted_flag char(1) in the table. Daily update this column with 'Y' for the rows you want to delete. On Sunday / when there is low activity on the database, you delete 7M rows after changing the table to nologging. Please also remember that the indexes associated with the table also need to rebuild.