Re: Bypass logging mechanism

rrenzo_at_my-deja.com wrote in message <87pgjs$o2r$1_at_nnrp1.deja.com>...
>
>I was afraid of getting this answer.
>This will not help me out if only 90 percent of the table has to be
>truncated...
>Can you still help out?

You can not delete selected data without transaction logging in any half decent database system that supports database integrity. What happens in the delete is halfway thru the delete code for row 1435 and the power trips? Without database transaction recovery, the database will be in a very inconsistent state. Quite likely you will have data corruption. The price to be paid for database integrity is the overhead in transaction logging.

The only way to bypass that when deleting data, is to truncate the entire table. In this case no transaction logging is required as you want that table empty. Period. And Oracle oblige.

However, when you only want to delete 90% of the data and retain 10%... that is a problem.

An alternative is not to delete 90% of the data, but save the 10% of data you need.

Example:
CREATE TABLE foobar_new
UNRECOVERABLE AS
SELECT FROM foobar
WHERE (give me 10% of the data)

TRUNCATE TABLE foobar

RENAME foobar_new TO foobar

This is not using any transaction logging. The CREATE TABLE is run as unrecoverable - so either the table is created successfully with all the data specified, or not at all. If this is successful (you must check!), only then TRUNCATE the old table, DROP it and change the name of the new table to that of the old table.

regards,
Billy Received on Wed Feb 09 2000 - 03:41:18 CST