TRUNCATE TABLE

[CHALAVADI]

I have a table with 500,000 rows. I need to cleanup this table every quarter and i have load fresh data approx 400,000 rows. I am planning to use Dynamic sql inside a stored procedure like
EXECUTE IMMEDIATE 'TRUNCATE TABLE table_name' ;
Can someone please suggest me is this a good idea or i need to build a cursor and delete rows.

[rcaballe]

I guess you are right.
Or you can use DBMS_SQL (for old versions)
If you want to maintain history you can use partitions.

[dmitriy]

Hi CHALAVADI,

You do not need to create a cursor or use dynamic SQL to truncate the table. If you are the owner of the table, you can use just ‘TRUNCATE TABLE ’ syntax.
Or if you truncate the table every time before you load data into it using SQLLoader, you can just add TRUNCATE keyword in the SQLLoader control file. In this case SQLLoader would truncate the table for you just before it loads data.

Hope it helps