Oracle Difference between Delete and Truncate | Oracle Delete vs Truncate

oracle interview questions and answers - What is the Difference between Delete and Truncate

Complete list of questions and answers available at

1. Difference between delete and Truncate

[raju]

One of the best lectures that i have seen in youtube for Database developers....
Thank you very

[C.K.Arun.]

Hello Friends and Siva Sir, i am Preparing for Oracle Certification. For the past weeks I watched your video series on various topics in Oracle. It is very Helpful and Useful. Now your videos had helped me to get placed in a good MNC company also. I am keep on following your videos. Now I feel confident and motivated in my day to day tasks in office and continuing my Certification as well. Daily I wondered how much of your Time you spend Patiently to create this video. Awesome ! I really appreciate your hard work and Dedication towards yours to achieve this is not a simple Task. From the Bottom of my heart i am saying this "The way you explain a Topic is "Out of This World". God Bless You Siva Sir. Keep Rocking Always. Also Please Help us as usual by Posting New Video as per the Comments. Thanks this is your finishing dialogue at the end of all your videos which is this . ( And Thanks a Lot for reading this comment. Subscribe and Stay Tuned for more comments for me. )

[amanpandey]

Sir, you should write a book, really helps to get all this on book, if there is any pdf available pls suggest ways to get it.

[adityagopavajjula]

Thank you Siva sir for explaining the differences in detail. I haven't heard about the concept of High Water Mark till now. Also, your way of classifying the answer based on the experience level of the candidate is very helpful.

[rajeevramakrishnan]

Thank you Siva for your effort in creating these superb videos. I watched all your 79 videos and attended interview today. Most of the questions from here only. In the intermediate level. Very helpful. I got to know about this from my friend. He also got a job offer two months back.

[mohankrishnavurity]

This tutorial has helped me a lot in getting a good job offer. Very useful tutorial for those who are preparing for PLSQL interviews. Each and every concept is beautifully explained.

[mohammedfarhanm]

Hi Siva, Literally you have explained it in a way that even a person who is not interested will get interested in Oracle PL/SQL.

[elanchezhiyanst]

I have cleared all my technical interviews and got offer after watching all your videos... Credit goes to you @sivaacademy .. Thank you very much for the video.

[ravindrapatil]

Hi Siva, All your videos are very nice and neatly explained. Thanks for sharing such a useful information for interview preparation.
My request for you, can you please upload the videos on table partitioning and performance tuning. In most of the interviews the questions are asked based on these concepts.

[TienNguyen-frwe]

Dear Mr. Siva, just wanted to say that your YT channel is one of the best Oracle channel I have seen so far. Thank you so much for sharing with the Oracle community. BR

[sreenivasaraobovanagiri]

SQL/PLSQL Videos are very useful and one of the best videos that i have seen.Well narrative videos. Thanks you so much. I request you keep posting more videos.

[ShahulHameed-zxzy]

Happy to understand your valuable videos to get more database concepts, advanced in Oracle. Thanks so much sir

[juliegeorge]

Thank you very much! Your videos helped me to learn lots of concepts in a short time and helped me to clear interviews. Please keep up the good work.

[ganeshgalla]

Hi Siva,

DDL trigger will fire for truncate right ? can we say that point ?

[prajaktadeshpande]

This video's really helped me alot to get clear my technical rounds, I cleared technical rounds of all three companies where I have applied thanks alot sir🙏

[logeshg]

Hello siva, You are doing an excellent job for youngsters. keep inspiring us!..
Here i have one que? As you have mention above "no triggers will get invoked" in truncate field right!
Using DDL trigger we can attain it right?

CREATE or REPLACE TRIGGER <tri_name>
AFTER DDL ON SCHEMA
ENABLE
BEGIN
DBMS_OUTPUT.PUT_LINE('TABLE TRUNCATED');
END;
/

TRUNCATE TABLE <tab_name>;

Just i have shared my understanding here please advise me on this!

Thank You!

[priyanshupathak]

Hi Siva, I'm developer at Oracle and really love coding in PL/SQL. In one of the interviews I was asked to transpose rows to column without using PIVOT concept.
Question was

transpose below

a 10 40
b 20 50
c 23 null

into--->

a b c
10 20 23
40 50 null

I tried my best searching online using decode or case statement, but no success .
Please provide solution for this, it will help many like me.
Big fat thumbs up for the effort you are putting in making us achieve our dreams.

[vivektarab]

Start - 0:34 -

- 0:30 - Difference b/w Delete and Truncate

- Delete is a DML command, white Truncate is a DDL command

- For Delete we need to Explicitly do a Commit, otherwise the information will not be committed, so that we can Rollback, If we don't want the change to be committed we can have the option of Rollback in terms of Delete

- In Truncate we do not have option to Rollback or Delete because it is an Auto commit Statement and once the statement is issued all the rows will be removed and automatically getting committed.

1:11 - With Delete we can use the Where condition, however with Truncate we cannot use Where condition. All the records of the table will get removed.

1:21 - Delete is bit Slower, because once a informations are deleted there is a option that we may rollback the informations also. So that Oracle will retain the deleted information in the Rollback Segment.

So in case if we do a Rollback it will replace the information or it will bring back the information from the Rollback

So Delete is bit Slower however Truncate is very fast and incase if you want to delete and insert a huge amount of informations, then better you create and new table, take of backup of the record then just do a Truncate, then you just bring the information from your backup table, so by that it will be very faster also.

2:01 - Difference from Triggers

2:23 - On Delete Trigger

3:29 - On Delete Cascade

4:54 - Demo

6:10 - One more Example

[sunithailapakurthy]

Thankyou so much for the very informative play lists. Very clear and brief. I could confidently attend an interview today. Please keep going.

[vivektarab]

Start - 0:34 -

- 0:30 - Difference b/w Delete and Truncate

0:53 - So we can go and Rollback if we do not want the change to be committed

- Delete is a DML command, white Truncate is a DDL command

0:59

- For Delete we need to Explicitly do a Commit, otherwise the information will not be committed, so that we can Rollback, If we don't want the change to be committed we can have the option of Rollback in terms of Delete

- In Truncate we do not have option to Rollback or Delete because it is an Auto commit Statement and once the statement is issued all the rows will be removed and automatically getting committed.

1:11 - With Delete we can use the Where condition, however with Truncate we cannot use Where condition. All the records of the table will get removed.

1:21 - Delete is bit Slower, because once a informations are deleted there is a option that we may rollback the informations also. So that Oracle will retain the deleted information in the Rollback Segment.

So in case if we do a Rollback it will replace the information or it will bring back the information from the Rollback

So Delete is bit Slower however Truncate is very fast and incase if you want to delete and insert a huge amount of informations, then better you create and new table, take of backup of the record then just do a Truncate, then you just bring the information from your backup table, so by that it will be very faster also.

1:32 - Oracle will retain

1:47 - In Case if you want to Delete and Insert

2:01 - Difference from Triggers

2:22 - If there is a On Delete Trigger - Whenever we do a delete Statement this Trigger will get fired

However Truncate will not fire any Triggers

Delete will not reclaim the space, once the data from the table is deleted and if you and check the size of the table, there will not be any difference

However in case of Truncate once the informations are removed from the table, and when you check the size of the Table, it would have got reduced.

There is concept of High level watermark

High level watermark will be reset in case of Truncate where as in case of Delete High level water mark will not be resetted,

3:29 - On Delete Cascade - A new feature has been introduced from 12c onwards where we can give the " On Delete Cascade Keyword " as part of the table definition.

So when we will give the On Delete cascade keyword as part of Table as part of table emersion - This is mainly for the Parent Child Relationship - Foreign key relationsp, So whenever we go and delete the record from parent table automatically the child records will get deleted in case it is set as " On Delete cascade "

The same is applicable for Truncate, we can Truncate the parent table and we can explicitly say the keyword called CASCADE, so that the Child tables will get automatically truncated provided the table is created with the On Delete Cascade Keyword

4:54 - Demo

6:10 - One more Example