Delete & Truncate Table

{\rtf1\ansi\ansicpg1252\deff0\deflang16393{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fswiss\fprq2\fcharset0 Helvetica;}{\f2\fnil\fcharset0 Calibri;}}
{\colortbl ;\red0\green0\blue255;}
{\stylesheet{ Normal;}{\s1 heading 1;}}
{\*\generator Msftedit 5.41.21.2509;}\viewkind4\uc1\pard\li720\lang1033\f0\fs22 If you want to delete all the rows in a table, TRUNCATE TABLE is faster than DELETE. DELETE physically removes rows one at a time and records each deleted row in the transaction log. TRUNCATE TABLE de allocates all pages \line associated with the table. For this reason, TRUNCATE TABLE is faster and requires less transaction log space than DELETE. TRUNCATE TABLE is functionally equivalent to DELETE with no WHERE clause, but TRUNCATE TABLE cannot be used with tables referenced by foreign keys. Both DELETE \line and TRUNCATE TABLE make the space occupied by the deleted rows available for the storage of new data.\par
Truncate just resets the high-water mark. It does not delete any rows. If you delete rows, then whatever you have specified regarding the referential integrity will be done (ON DELETE SET NULL, or ON DELETE CASCADE, or the default which is to return ORA-02292 if the referential integrity constraint would be violated) whereas TRUNCATE just returns ORA-02266 if you have any enabled foreign key constraints referencing the table, even if the tables are empty. \par
\par
\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl\i schema\i0 \~\cell is the schema to contain the trigger. If you omit \i schema\i0 , Oracle creates the trigger in your own schema. \~\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl TABLE \~\cell specifies the schema and name of the table to be truncated. You can truncate index-organized tables. This table cannot be part of a cluster. \~\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl\~\cell When you truncate a table, Oracle also automatically deletes all data in the table's indexes. \~\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl SNAPSHOT LOG \~\cell specifies whether a snapshot log defined on the table is to be preserved or purged when the table is truncated. This clause allows snapshot master tables to be reorganized through export/import without affecting the ability of primary-key snapshots defined on the master to be fast refreshed. To support continued fast refresh of primary-key snapshots the snapshot log must record primary-key information. For more information about snapshot logs and the TRUNCATE command, see {\field{\*\fldinst{HYPERLINK "D:\\\\ORA-HELP\\\\DOC\\\\database.804\\\\a58245\\\\toc.htm" }}{\fldrslt{\cf1\ul\i Oracle8 Replication}}}\cf0\ulnone\i0\f0\fs22 . \~\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx3084\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl\~\cell PRESERVE \~\cell specifies that any snapshot log should be preserved when the master table is truncated. This is the default. \~\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx3084\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl\~\cell PURGE \~\cell specifies that any snapshot log should be purged when the master table is truncated. \~\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl CLUSTER \~\cell specifies the schema and name of the cluster to be truncated. You can only truncate an indexed cluster, not a hash cluster. \~\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl\~\cell When you truncate a cluster, Oracle also automatically deletes all data in the cluster's tables' indexes. \~\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl DROP STORAGE \~\cell De allocates the space from the deleted rows from the table or cluster. This space can subsequently be used by other objects in the tabelspace. This is the default. \~The DROP STORAGE option de allocates all but the space specified by the table's MINEXTENTS parameter.\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl REUSE STORAGE \~\cell retains the space from the deleted rows allocated to the table or cluster. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from inserts or updates. \~\cell\row\trowd\trgaph45\trleft-60\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx1322\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx8700\pard\intbl\~\cell The DROP STORAGE and REUSE STORAGE options also apply to the space freed by the data deleted from associated indexes. \~\cell\row\pard\par
Deleting rows with the TRUNCATE command is also more convenient than dropping and re-creating a table because dropping and re-creating: \par
\pard\fi-360\li720\tx720 invalidates the table's dependent objects, while truncating does not \par
requires you to re grant object privileges on the table, while truncating does not \par
requires you to re-create the table's indexes, integrity constraints, and triggers and re-specify its STORAGE parameters, while truncating does not \par
\pard\li360\b\f1 Note:\b0\f0 \par
When you truncate a table, the storage parameter NEXT is changed to be the size of the last extent deleted from the segment in the process of truncation. \par
\~\par
\pard\keepn\s1\li360\b Restrictions\par
\pard\li360\b0 When you truncate a table, NEXT is automatically reset to the last extent deleted. \par
You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table. \par
You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.) \par
You cannot roll back a TRUNCATE statement. \par
\pard\sa200\sl276\slmult1\lang9\f2\par
}