Re: Update a table with no logging?

"wayne" <no_at_email.please.com> wrote in message news:9dsd5q$fq6_at_dispatch.concentric.net...
> > Truncate table <table_name>
> > Won't log anything, and will commit automatically as it is DDL, not DML.
> > If you want to get rid of your data fast: perfect option.
>
> Just want to verify: TRUNCATE TABLE does not deallocate the blocks
 assigned
> to the table, right?

Yes, it does. Or, more precisely, it de-allocates all bar the INITIAL extent, by default. If you've got MINEXTENTS set to anything other than "1", however (which is the default), then it *will* deallocate all extents *bar that number*.

>I have used truncate and then re-analyzed the
> truncated table and found that there was still the same number of blocks
> allocated, even though there were no rows.
>

I've just tested this on 8.1.6 (NT4), so I'd be interested in your version and platform, because maybe it's a bug, if what you report is true.

The standard version goes like this: a TRUNCATE deletes all rows, and frees all extents bar the initial (or whatever is defined by MINEXTENTS). Actually, it doesn't physically delete the rows... it simply whacks the high water mark to point zero, which means effectively the same thing, since anything above the high water mark is unreadable, and effectively doesn't exist.

Hence, an 'analyze table blah compute statistics' should report zero rows after a truncate, because there can be no rows after a truncate. My test, on my platform, confirms that to be the case.

I inserted one row into a table with minextents of 2. dba_extents reported 2 extents; dba_tables (after an 'analyze table' reported 1 row). After the truncate, dba_extents reported 2 extents (the effects of the 'minextents' clause -no extents were released), dba_tables (after a re-analysis) reported zero rows (ie, all data had been [effectively] removed).

When I allocated a third extent BEFORE the truncate, dba_extents reported 3 extents (as you'd expect). dba_tables still said there was only one row (again, as you;d expect). After the truncate, dba_extents reported 2 extents (hence, 1 extent WAS freed). dba_tables said there were zero rows (because all rows had (as ever) been removed).

So a truncate DOES release blocks, and an analyze after a truncate SHOULD report zero rows and zero blocks. 8.1.6 on NT does precisely that, so if yours genuinely doesn't, something is profoundly wrong.

The fact that your statistics changed after a 'move table' doesn't surprise me: moving a table requires Oracle to read and re-insert all records, and hence re-pack all data. You'd expect a segment full of empty space, and hence taking up (say) 50 blocks to be re-inserted in a very compact form, and hence take up only (say) 25 rows after the move. That has nothing to do with the truncate, however, which (in theory) deletes ALL rows, and frees all bar the initial extent (or minextent if higher).

I can only think that you truncate command didn't succeed, or wasn't allowed to be processed, for whatever reason. In that case, an in-situ re-analyze would show no change (because your truncate command hadn't actually been accepted). Nevertheless, a 'move tablespace' *would* have shown a saving on space because of the compaction of data a move implies.

Have another go, and post the results.

Regards
HJR Received on Wed May 16 2001 - 07:53:23 CDT