Note: Deletes perform
normal DML. That is, they take locks on rows, they generate redo (lots of it), and they
require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If
a mistake is made a rollback can be issued to restore the records prior to a commit. A
delete does not relinquish segment space thus a table in which all records have been
deleted retains all of its original blocks.
Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the
table back to zero. No row-level locks are taken, no redo or rollback is generated.
All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to
anything other than 1, then that number of extents is retained rather than just the
initial). By re-positioning the high water mark, they prevent reading of any table data,
so they have the same effect as a delete, but without all the overhead. Just one slight
problem: a truncate is a DDL command, so you can't roll it back if you decide you made a
mistake. (It's also true that you can't selectively truncate -no "WHERE" clause
is permitted, unlike with deletes, of course).
By resetting the High Water Mark, the truncate prevents reading of any table's data, so
they it has the same effect as a delete, but without the overhead. There is, however, one
aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a
COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is
possible. |
| |
| Truncate Table |
| Simple Truncate |
TRUNCATE TABLE <table_name>
[<PRESERVE | PURGE>] [MATERIALIZED VIEW LOG]
[<DROP | REUSE> STORAGE];
By default drops storage even if DROP STORAGE is not specified.
The PURGE option is for truncating materialized views and purges the
log. |
CREATE TABLE test AS
SELECT * FROM all_objects;
SELECT COUNT(*) FROM test;
TRUNCATE TABLE test;
or more explicitly
TRUNCATE TABLE test
DROP STORAGE;
SELECT COUNT(*) FROM test; |
| Empty Table And Move Highwater Mark |
CREATE TABLE test (
testcol VARCHAR2(20));
BEGIN
FOR i IN 1..10000
LOOP
INSERT INTO test
(testcol)
VALUES
('ABCDEFGHIJKLMNOPQRST');
END LOOP;
COMMIT;
END;
/
exec dbms_stats.gather_table_stats(OWNNAME=>'UWCLASS', TABNAME=>'TEST');
SELECT SUM(blocks)
FROM user_extents
WHERE segment_name = 'TEST';
TRUNCATE TABLE test;
SELECT SUM(blocks)
FROM user_extents
WHERE segment_name = 'TEST'; |
| Empty Table And Do Not Move Highwater Mark |
TRUNCATE TABLE <table_name>
REUSE STORAGE; |
BEGIN
FOR i IN 1..10000
LOOP
INSERT INTO test
(testcol)
VALUES
('ABCDEFGHIJKLMNOPQRST');
END LOOP;
COMMIT;
END;
/
exec dbms_stats.gather_table_stats(OWNNAME=>'UWCLASS', TABNAME=>'TEST');
SELECT SUM(blocks)
FROM user_extents
WHERE segment_name = 'TEST';
TRUNCATE TABLE test REUSE STORAGE;
SELECT SUM(blocks)
FROM user_extents
WHERE segment_name = 'TEST'; |
| |
| Truncate Partition |
| Truncate A Single Partition In A Partitioned Table |
ALTER TABLE <table_name>
TRUNCATE PARTITION <partition_name>; |
CREATE TABLE parttab (
state VARCHAR2(2),
sales NUMBER(10,2))
PARTITION BY LIST (state) (
PARTITION northwest VALUES ('OR', 'WA') TABLESPACE uwdata,
PARTITION southwest VALUES ('AZ', 'CA') TABLESPACE uwdata);
INSERT INTO parttab VALUES ('OR', 100000);
INSERT INTO parttab VALUES ('WA', 200000);
INSERT INTO parttab VALUES ('AZ', 300000);
INSERT INTO parttab VALUES ('CA', 400000);
COMMIT;
SELECT * FROM parttab PARTITION(northwest);
SELECT * FROM parttab PARTITION(southwest);
ALTER TABLE parttab
TRUNCATE PARTITION southwest;
SELECT * FROM parttab PARTITION(northwest);
SELECT * FROM parttab PARTITION(southwest); |