Truncate verse Delete command in oracle
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 deallocates all pages 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 and TRUNCATE TABLE make the space occupied by the deleted rows available for the storage of new data.
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.
Schema : It is the schema to contain the trigger. If you omit schema, Oracle creates the trigger in your own schema.
TABLE : 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.
When you truncate a table, Oracle also automatically deletes all data in the table's indexes.
SNAPSHOT LOG : specifies whether a snapshot log defined on the table 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 Oracle8 Replication.
PRESERVE : - specifies that any snapshot log should be preserved when the master table is truncated.
PURGE : - specifies that any snapshot log should be purged when the master table is truncated.
CLUSTER : - specifies the schema and name of the cluster to be truncated. You can only truncate an indexed cluster, not a has cluster.
when you truncate a cluster, oracle also automatically deletes all data in the cluster's tables' indexes.
DROP STORAGE :- deallocates the space from the deleted rows from the table or cluster. This space can subsequently be used by other objects in the tablespace. This is the default. The DROP STORAGE option deallocates all but the space specified by the table's MINEXTENTS parameter.
RESUE STORAGE : 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.
The DROP STORAGE and REUSE STORAGE options also apply to the space freed by the data deleted from associated indexes.
Deleting rows with the TRUNCATE command is also more convenient than dropping and re-creating a table because dropping and re-creating:
invalides the table's dependent objects, while truncating does not
requires you to regrant object privileges on the table, while truncating does not requires you to re-create the table's indexes, integrity constraints, and triggers and respecify its STORAGE parameters, while truncating does not
Note:
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.
Restrictions
when you truncate a table,NEXT is automatically reset to the last extend deleted.
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.
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.)
You cannot rollback a TRUNCATE statement.
Schema : It is the schema to contain the trigger. If you omit schema, Oracle creates the trigger in your own schema.
TABLE : 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.
When you truncate a table, Oracle also automatically deletes all data in the table's indexes.
SNAPSHOT LOG : specifies whether a snapshot log defined on the table 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 Oracle8 Replication.
PRESERVE : - specifies that any snapshot log should be preserved when the master table is truncated.
PURGE : - specifies that any snapshot log should be purged when the master table is truncated.
CLUSTER : - specifies the schema and name of the cluster to be truncated. You can only truncate an indexed cluster, not a has cluster.
when you truncate a cluster, oracle also automatically deletes all data in the cluster's tables' indexes.
DROP STORAGE :- deallocates the space from the deleted rows from the table or cluster. This space can subsequently be used by other objects in the tablespace. This is the default. The DROP STORAGE option deallocates all but the space specified by the table's MINEXTENTS parameter.
RESUE STORAGE : 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.
The DROP STORAGE and REUSE STORAGE options also apply to the space freed by the data deleted from associated indexes.
Deleting rows with the TRUNCATE command is also more convenient than dropping and re-creating a table because dropping and re-creating:
invalides the table's dependent objects, while truncating does not
requires you to regrant object privileges on the table, while truncating does not requires you to re-create the table's indexes, integrity constraints, and triggers and respecify its STORAGE parameters, while truncating does not
Note:
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.
Restrictions
when you truncate a table,NEXT is automatically reset to the last extend deleted.
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.
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.)
You cannot rollback a TRUNCATE statement.
Autonomous Transaction is a new feature in ORACLE. It allows setting up independent transactions that can be called from within other transactions. It lets you suspend the main transaction (without committing or rolling back), perform some DML operations, commit or rollback those operations (without any effect on the main transaction), and then return to the main transaction.
A pragma is a directive to the PL/SQL compiler. Pragmas pass information to the compiler, they are processed at compile time but do not execute.
A transaction is a Logical unit of work that compromises one or more SQL statements executed by a single User.
The SGA is a shared memory region allocated by the oracle that contains Data and control information for one oracle instance.
P/L SQL Tables / Arrays
PL/SQL tables are declared in the declaration portion of the block. A table is a composite data type in PL/SQL. PL/SQL tables can have one column and a primary key neither of which can be named.
The column can be any scalar type but primary key should be a BINARY_INTEGER data type.
Rules for PL/SQL Tables:
1. A loop must be used to insert values into a PL/SQL Table.
2. You cannot use the Delete command to delete the contents of PL/SQL Table. You must assign an empty table to the PL/SQL table being deleted.
2. You cannot use the Delete command to delete the contents of PL/SQL
No comments:
Post a Comment