Monday 29 July 2013

Can we truncate some of the rows from the table instead of truncating the full table?

You can truncate few rows from a table if the table is partitioned. You can truncate a single partition and keep remaining.

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;

ALTER TABLE parttab
TRUNCATE PARTITION southwest;

SELECT * FROM parttab;

No comments:

Post a Comment