Note: DELETE said here is the same point of the delete statement that does not take WHERE clauses. DELETE and DELETE without WHERE clauses, and DROP will delete the data in the table: 1. Truncate and Delete only delete data do not delete the table Structure (defined) DROP statement will delete the structure of the table is dependent on the constraint, the trigger (TRIGGER), index (index), depending on the stored procedure / function of the table, but becomes the Invalid status. 2 The .delete statement is DML, this operation will be put in Rollback Segment, which takes effect after transaction; if there is a corresponding TRIGGER, it will be triggered when executed. Truncate, DROP is DDL, the operation takes effect immediately, the original data does not put it in Rollback In segment, you can't roll back. Operation does not trigger Trigger. 3. DELETE statement does not affect the EXTENT occupied by the table, the high watermark maintains the original position. It is clear that the DROP statement will complete all the space occupied by the Truncate statement. Save the space released to Mineltnts EXTENT unless you use Reuse Storage; Truncate will reset high water lines (return to the start). 4. Speed, in general: Drop> Truncate> Delete 5. Security: Carefully use DROP and TRUNCATE, especially when there is no backup. Otherwise, cry is not used to use, want to delete some data rows to use Delete, pay attention to bring the WHERE clause. Want to delete the table, of course, if you want to keep the table with DROP Delete all data. If you don't turn it with your transaction. If you are related to the transaction, or want to trigger Trigger, or use delete. If you are the delement inside, you can keep up with Reuse Stroage with Truncate, retroduce / insert Data personal summary, welcome everyone to add [Post Reply] [View forum original] [Add to Favorites] [Close]
Tangsuilx Reply to: 2004-02-04 11:24:11 High, ask, I am in an application transformation, the original program is for Informix, when all data is deleted, use Drop Create. When it is changed to for Oracle 9i, because DROP and CREATE are DDL statements, automatic communcate is automatically changed, but do not automatically Commit, whether it is not a DDL statement. These tables have tens of millions of records, and Delete is definitely not.
ORAIX Reply to: 2004-02-04 13: 37: 22truncate is DDL
Fenng Reply to: 2004-02-04 14:30:55 [Quote: 3F920216B3] 4. Speed, in general: Drop> Truncate> Delete [/ quote: 3F920216B3] What do you say? SQL > create table test as select * from all_objects where rownum <10000; Table created Elapsed:. 00: 00: 01.38 SQL> drop table test; Table dropped [b: 3f920216b3]. Elapsed: 00: 00: 00.11 [/ b: 3f920216b3 ] SQL> create table test as select * from all_objects where rownum <10000; Table created Elapsed:. 00: 00: 01.39 SQL> truncate table test; Table truncated [b:. 3f920216b3] Elapsed: 00: 00: 00.09 [/ b : 3F920216B3] SQL>
TXFY Reply to: 2004-02-04 14:42:53 So little data is not more worth, too little time, can't see the effect, how can you get a few hundred m data! TANGLOW Reply to: 2004-02-04 16:16:53 The functionality of three commands in the Sybase database system is basically like this.
Troyzeng Reply to: 2004-02-04 17:21:20 We need this summary and discussion! well! Unfortunately, I am a newbie, learning, or talk about my own ideas.
Rman9i Reply to: 2004-02-04 23:26:26 In practical applications, the difference between the three is clear. When you no longer need the table, use Drop; when you still have to keep the table, you want to remove all records, use truncate; when you want to delete some record (Always with a where clause, use Delete.
Tangsuilx Reply to: 2004-02-05 09:49:19 [Quote: F87E40D343 = "Oraix"] Truncate is DDL [/ quote: f87e40d343] But the DDL statement in the transaction will cause commun, I am using Truncate in the app. After deleting the data of a table, load other data, after the later processing error, can return to the original data. Please explain, or I am big.
ORAIX Reply to: 2004-02-05 09:50:00 Summary is not enough, it is a throwing brick, and there is a mistake, you will welcome everyone to discuss. I hope everyone can do something similar, and improve these. I think these It is also one of you to come to the forum.
TXFY Reply to: 2004-02-05 10:00:03 [Quote: 94ac1c9c53 = "tangsuilx"] But the DDL statement in the transaction will cause commit, I use TRUNCATE to delete the data of a table after application. In addition to other data, after the later processing error, you can return to the original data. Please explain, or I am big. [/ quote: 94ac1c9c53] I can't roll back! Create or Replace Procedure Testtruncate Is Begin Execute Immediate 'Truncate Table Testtrun'; Insert INTO Testtrun Values ('3'); Rollback; Commit; End; ----------------- -------------------------------------------------- ----- SQL> Select * from testtrun; i --- 2 1 SQL> EXEC TESTTRUNCATE; PL / SQL process has been successfully completed. SQL> Select * from testtrun; Unselected line can be seen, although there is no commun but still can't roll back, and later inserting 3 because there is no submission being rolled back.
Rman9i Reply to: 2004-02-05 10:08:32 [quote: 724d0056b3 = "tangsuilx"] But the DDL statement in the transaction will cause commit, I use Truncate to delete the data of a table after application, In addition to other data, after the later processing error, you can return to the original data. Please explain, or I am big. [/ quote: 724d0056b3] Truncate is indeed DDL.
