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.
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)
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.