{"id":362,"date":"2011-11-24T08:18:16","date_gmt":"2011-11-24T00:18:16","guid":{"rendered":"https:\/\/blog.rexdf.org\/?p=362"},"modified":"2011-11-24T08:18:16","modified_gmt":"2011-11-24T00:18:16","slug":"oracle-truncate-table-%e4%b8%8e-delete-tabel%e7%9a%84%e5%8c%ba%e5%88%ab","status":"publish","type":"post","link":"https:\/\/blog.rexdf.org\/en\/2011\/11\/oracle-truncate-table-%e4%b8%8e-delete-tabel%e7%9a%84%e5%8c%ba%e5%88%ab\/","title":{"rendered":"Oracle truncate table \u4e0e delete tabel\u7684\u533a\u522b"},"content":{"rendered":"
<\/div>\n
\n
\n

\u3000\u4e00\u3001<\/span><\/span><\/span><\/p>\n

1.delete\u4ea7\u751frollback\uff0c\u5982\u679c\u5220\u9664\u5927\u6570\u636e\u91cf\u7684\u8868\u901f\u5ea6\u4f1a\u5f88\u6162\uff0c\u540c\u65f6\u4f1a\u5360\u7528\u5f88\u591a\u7684rollback segments .truncate \u662fDDL\u64cd\u4f5c\uff0c\u4e0d\u4ea7\u751frollback\uff0c\u901f\u5ea6\u5feb\u4e00\u4e9b.
<\/span>\u3000\u3000
<\/span>\u3000\u3000Truncate table does not generate rollback information and redo records so it is much faster than delete.
<\/span>\u3000\u3000
<\/span>\u3000\u3000In default, it deallocates all space except the space allocated by MINEXTENTS unless you specify REUSE STORAGE clause.
<\/span>\u3000\u3000
<\/span>2.\u4e0d\u4ecetablespace\u4e2d\u817e\u51fa\u7a7a\u95f4,\u9700\u8981
<\/span>\u3000\u3000
<\/span>\u3000\u3000ALTER TABLESPACE AAA COALESCE; \u624d\u6709\u7a7a\u95f4
<\/span>\u3000\u3000
<\/span>3.truncate \u8c03\u6574high water mark \u800cdelete\u4e0d.truncate\u4e4b\u540e\uff0cTABLE\u7684HWM\u9000\u56de\u5230 INITIAL\u548cNEXT\u7684\u4f4d\u7f6e\uff08\u9ed8\u8ba4\uff09
<\/span>\u3000\u3000
<\/span>\u3000\u3000delete \u5219\u4e0d\u53ef\u4ee5\u3002
<\/span>\u3000\u3000
<\/span>4.truncate \u53ea\u80fd\u5bf9TABLE
<\/span>\u3000\u3000
<\/span>\u3000\u3000delete \u53ef\u4ee5\u662ftable,view,synonym
<\/span>\u3000\u3000
<\/span>5.TRUNCATE TABLE \u7684\u5bf9\u8c61\u5fc5\u987b\u662f\u672c\u6a21\u5f0f\u4e0b\u7684\uff0c\u6216\u8005\u6709drop any table\u7684\u6743\u9650 \u800c DELETE \u5219\u662f\u5bf9\u8c61\u5fc5\u987b\u662f\u672c\u6a21\u5f0f\u4e0b\u7684\uff0c\u6216\u88ab\u6388\u4e88 DELETE ON SCHEMA.TABLE \u6216DELETE ANY TABLE\u7684\u6743\u9650<\/span><\/span><\/span><\/span><\/p>\n

\n

\u4e8c\u3001 truncate\u662fDDL\u8a9e\u8a00.
<\/span>delete\u662fDML\u8a9e\u8a00

<\/span>DDL\u8a9e\u8a00\u662f\u81ea\u52d5\u63d0\u4ea4\u7684.
<\/span>\u547d\u4ee4\u5b8c\u6210\u5c31\u4e0d\u53ef\u56de\u6efe.

<\/span>truncate\u7684\u901f\u5ea6\u4e5f\u6bd4delete\u8981\u5feb\u5f97\u591a.<\/span><\/span><\/span><\/span><\/p>\n

\n

\u4e09\u3001 truncate \u4f1a\u628a highwatermark \u56de\u5f52\u81f3 0 … \u5f53\u4e0b\u4e00\u6b21\u518d\u63d2\u5165\u65b0\u8d44\u6599\u65f6\u5c31\u4f1a\u5feb\u4e00\u4e9b\u5566\u3002

<\/span>\u6240\u4ee5\u4e00\u822c\u90fd\u662f\u5728 temp table \u4e0a\u4f7f\u7528\u7684\uff0c\u4e0d\u8fc7\u8981\u6ce8\u610f\u5c31\u662f truncate \u4e0d\u80fd\u5728 pl\/sql \u4e0a\u4f7f\u7528\uff0c\u8981\u7528 dynamic SQL \u624d\u53ef\u4ee5\u3002<\/span><\/span><\/span><\/span><\/p>\n

\n

\n

\u56db\u3001<\/span><\/span><\/p>\n

\u5f53\u4f60\u4e0d\u518d\u9700\u8981\u8be5\u8868\u65f6\uff0c \u7528 drop;
<\/span>\u5f53\u4f60\u4ecd\u8981\u4fdd\u7559\u8be5\u8868\uff0c\u4f46\u8981\u5220\u9664\u6240\u6709\u8bb0\u5f55\u65f6\uff0c \u7528 truncate;
<\/span>\u5f53\u4f60\u8981\u5220\u9664\u90e8\u5206\u8bb0\u5f55\u65f6\uff08always with a WHERE clause), \u7528 delete.<\/span><\/span><\/span><\/span><\/p>\n

\n

\n

\u4e94\u3001<\/span><\/span><\/p>\n

1.TRUNCATE TABLE<\/span><\/span><\/p>\n

I<\/span><\/span>ndex\u4e5f\u6703\u522a\u6389\u4e0d\u662f\u6307drop index

<\/span>sys@DEMO> create index tom.idx_object_name on tom.t(object_name);

<\/span>\u5df2\u5efa\u7acb\u7d22\u5f15.

<\/span>sys@DEMO> select block_id,bytes from dba_extents where segment_name=’IDX_OBJECT_NAME’;

<\/span>BLOCK_ID BYTES
<\/span>———- ———-
<\/span>17321 65536
<\/span>17329 65536
<\/span>17337 65536
<\/span>17345 65536
<\/span>17353 65536
<\/span>17361 65536
<\/span>17369 65536
<\/span>17377 65536
<\/span>17385 65536
<\/span>17393 65536
<\/span>17401 65536
<\/span>17409 65536
<\/span>41865 65536
<\/span>41873 65536
<\/span>41897 65536
<\/span>41905 65536
<\/span>18953 1048576
<\/span>19081 1048576
<\/span>19209 1048576
<\/span>19337 1048576

<\/span>\u5df2\u9078\u53d6 20 \u500b\u8cc7\u6599\u5217.

<\/span>sys@DEMO> TRUNCATE TABLE TOM.T;

<\/span>\u8868\u683c\u88ab\u622a\u65b7.

<\/span>sys@DEMO> select block_id,bytes from dba_extents where segment_name=’IDX_OBJECT_NAME’;

<\/span>BLOCK_ID BYTES
<\/span>———- ———-
<\/span>17321 65536<\/span><\/span>
<\/span><\/p>\n

\n

\n

2.DELETE table<\/span><\/span><\/p>\n

sys@DEMO> insert into tom.t select * from all_objects;

<\/span>\u5df2\u5efa\u7acb 27747 \u500b\u8cc7\u6599\u5217.

<\/span>sys@DEMO> insert into tom.t select * from all_objects;

<\/span>\u5df2\u5efa\u7acb 27747 \u500b\u8cc7\u6599\u5217.

<\/span>sys@DEMO> commit;

<\/span>\u78ba\u8a8d\u5b8c\u6210.

<\/span>sys@DEMO> select block_id,bytes from dba_extents where segment_name=’IDX_OBJECT_NAME’;

<\/span>BLOCK_ID BYTES
<\/span>———- ———-
<\/span>17321 65536
<\/span>17201 65536
<\/span>17241 65536
<\/span>17265 65536
<\/span>17297 65536
<\/span>17353 65536
<\/span>17369 65536
<\/span>17377 65536
<\/span>17385 65536
<\/span>17393 65536
<\/span>17401 65536
<\/span>17409 65536
<\/span>41865 65536
<\/span>41873 65536
<\/span>41897 65536
<\/span>41905 65536
<\/span>17673 1048576
<\/span>17801 1048576
<\/span>18185 1048576
<\/span>18441 1048576

<\/span>\u5df2\u9078\u53d6 20 \u500b\u8cc7\u6599\u5217.

<\/span>sys@DEMO> delete from tom.t;

<\/span>\u5df2\u522a\u9664 55494 \u500b\u8cc7\u6599\u5217.

<\/span>sys@DEMO> select block_id,bytes from dba_extents where segment_name=’IDX_OBJECT_NAME’;

<\/span>BLOCK_ID BYTES
<\/span>———- ———-
<\/span>17321 65536
<\/span>17201 65536
<\/span>17241 65536
<\/span>17265 65536
<\/span>17297 65536
<\/span>17353 65536
<\/span>17369 65536
<\/span>17377 65536
<\/span>17385 65536
<\/span>17393 65536
<\/span>17401 65536
<\/span>17409 65536
<\/span>41865 65536
<\/span>41873 65536
<\/span>41897 65536
<\/span>41905 65536
<\/span>17673 1048576
<\/span>17801 1048576
<\/span>18185 1048576
<\/span>18441 1048576<\/span>
<\/span><\/span><\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\u3000\u4e00\u3001 1.delete\u4ea7\u751frollback\uff0c\u5982\u679c\u5220\u9664\u5927\u6570\u636e\u91cf\u7684\u8868\u901f\u5ea6\u4f1a\u5f88\u6162\uff0c\u540c\u65f6\u4f1a\u5360\u7528\u5f88\u591a\u7684rollback segments .truncate \u662fDDL\u64cd\u4f5c\uff0c\u4e0d\u4ea7\u751frollback\uff0c\u901f\u5ea6\u5feb\u4e00\u4e9b.\u3000\u3000\u3000\u3000Truncate table does not generate rollback information and redo records so it is much faster than delete.\u3000\u3000\u3000\u3000In default, it deallocates all space except the space allocated by MINEXTENTS unless you specify REUSE STORAGE clause.\u3000\u30002.\u4e0d\u4ecetablespace\u4e2d\u817e\u51fa\u7a7a\u95f4,\u9700\u8981\u3000\u3000\u3000\u3000ALTER TABLESPACE AAA … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[6],"tags":[50],"class_list":["post-362","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle-2"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/blog.rexdf.org\/en\/wp-json\/wp\/v2\/posts\/362","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.rexdf.org\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.rexdf.org\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.rexdf.org\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.rexdf.org\/en\/wp-json\/wp\/v2\/comments?post=362"}],"version-history":[{"count":0,"href":"https:\/\/blog.rexdf.org\/en\/wp-json\/wp\/v2\/posts\/362\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.rexdf.org\/en\/wp-json\/wp\/v2\/media?parent=362"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.rexdf.org\/en\/wp-json\/wp\/v2\/categories?post=362"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.rexdf.org\/en\/wp-json\/wp\/v2\/tags?post=362"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}