Oracle Difference between Delete and Truncate | Oracle Delete vs Truncate
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- oracle interview questions and answers - What is the Difference between Delete and Truncate
Complete list of questions and answers available at
easy-learning-...
1. Difference between delete and Truncate
One of the best lectures that i have seen in youtube for Database developers....
Thank you very much.....
Thanks for your comments Raju :-)
Hi developers, I am preparing for SQL Interviews Is it worthy to follow this playlist I am bit confuse because there is many Oracles playlist in this channel like 12C, 18C I am a nob person in this SQL or Oracle. Any guidance or suggestion would be Highly appreciable
Thanks !
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. )
Its my pleasure bro, thank you
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.
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.
You're most welcome
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.
Welcome bro, Best wishes 👍👍💐
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.
Best wishes 💐💐👍👍
@@SivaAcademy I need Oracle plsql developer course. Could you please provide course.
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.
Thank you bro
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.
Thank you, and wishing you all the best :-)
@@SivaAcademy thank you very much sir
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.
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
Its my pleasure, thank you so much bro
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.
Welcome bro 🙏, please stay tuned for more videos
Happy to understand your valuable videos to get more database concepts , advanced in Oracle. Thanks so much sir
Welcome bro
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.
Thank you 🙏
Hi Siva,
DDL trigger will fire for truncate right ? can we say that point ?
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🙏
Wow great, Best wishes
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
AFTER DDL ON SCHEMA
ENABLE
BEGIN
DBMS_OUTPUT.PUT_LINE('TABLE TRUNCATED');
END;
/
TRUNCATE TABLE ;
Just i have shared my understanding here please advise me on this!
Thank You!
Welcome bro, its covered under this video bro, thanks for your comments 💐💐🙏
th-cam.com/video/THeY59Xw2RI/w-d-xo.html
@@SivaAcademy Thanks for your time bro❤
My pleasure bro 😊💐🙏
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.
You can do this by using case
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
Thankyou so much for the very informative play lists. Very clear and brief. I could confidently attend an interview today. Please keep going.
Welcome bro, Best wishes 💐💐👍
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
Hi Sir, Can you pls explain about high level water mark, thankyou in advance.
As soon as possible, Please stay tuned
thank you so much Siva Ji by your videos I got two big offers, your videos are very crystal clear.
My pleasure, All the best 🌸🌸🍀🍀💐💐
@sharique can you disclose the name of company??
Hi Sir, Thank you so much for this information. Is very easy to understand. I have a concern on 5th point i.e. with triggers. I believe DDL trigger would be helpful to capture the truncate event. So eventually, triggers could get fired on both delete and truncate statements right. Kindly correct me if I'm wrong.
Keep up the good work 😊🤝.
You are right bro, please look into this video for more details
th-cam.com/video/THeY59Xw2RI/w-d-xo.html
@@SivaAcademy Thank you for the quick response 😊 and the provided link video was helpful.
Welcome bro
In the explanation of trigger being fired on delete - you used "delete from ;" without any where clause. So this delete statement will delete all records present in the table. So will the trigger be fired only once or it will be fired for each record being deleted?
Row level trigger get fired for each and every row, whereas statement level trigger gets fired once for statement execution, you can look into this video for further details
th-cam.com/video/PEfHuyc79zM/w-d-xo.html
Sir i got one question on index like if we create index on column of some data type (anything) but while accessing we are using some other data type, will index work.?
i tried to simulate the case.. created table of t with column as col number having records 1 to 5000 number
then created index on col
then queries the table using select st -- it made full table scan even though idx created
i have make 3000 records only for number 5 and fetched record 5 -- still index is not used -- it is using full table scan--
so i m failing to simulate the case
could you please help me the solution
Sir will you teach informatica/Talend also like this, any paid course exists or any youtube channel already there.? Please let me know
sir which book is better for reading sql and plsql for beginners.
Will reducing the table space of truncate will help in improving the performance while fetching the data...or how it will be helpful? Could you please explain that as well?
how to check water mark level?
suppose if we do delete purge it will delete
permanently so the water mark level will reset or not ??
Hi Siva sir. Can you please help me in a query.
For example
In a table we have data for a column value
Ram,shyam,Hari
Next i am updating record as
Ram,Hari
Then how we will get to know the record shyam has been deleted.
Need help
is all the comma separated values stored in single column? or each value in one row?
for eg "Ram,shyam,Hari" stored in single row? or in three different row?
hii siva sir..what is cataloge ? what is the relation between sql and cataloge
Your giving good explanation.the way of voic also good very important .
@ram, thanks bro 🙏🙏
Hi Siva can you reply me is plsql work on all databases
Thankyou so much Sir👏🏻Very well explained and Keep Doing the Great work Sir😊Easy to understand and helps very much in my learning Sir👍🏻
My pleasure, thank you 🙏💐
This was a very good video. I appreciate you making these. It helps a lot.
Thank you 🙏💐
Hi Siva i have noticed in Plsql collection series the multiset operator video you have uploaded twice video no 9 and video no 10 are same
Thank you for letting me know, removed the last duplicate
Sir I need more clarity on on delete cascade n ondelete ser null.these clauses already exists in previous version foreign key constraint,it seems.
Sure, its part of upcoming video list, please stay tuned
Siva, could not get the difference on delete trigger and truncate. We wrote triggers on delete, hence it is fired, we didn't use on truncate triggers. Hence it didn't. How it could be the difference? Could explain me of undo segment?
Covered with example here on this specific use case, please look into this video
th-cam.com/video/THeY59Xw2RI/w-d-xo.html
Hi siva, i want to joins in sql video...examples and interview question. pls share that link.
Very good explanation and useful. Please can you explain high level watermark?
Sure please stay tuned
Hi Sir,
All your videos are really helpful, could you please make videos on parititions
also can you please let us understand the below 2 questions
1. If index is not working then what should we do?
2. If inner doesnt work what is the best way to get the output -- i mentioned full outer join removing all nulls.. but interviewer asked any other best way? can you please let us know the ans for these 2 questions
Thank you 💐, will cover in upcoming videos
Hi . Can anyone tell me a query to get a function name ... I have the package name .. but I need to know which function uses that package
Hi sir , very good explanation .. I just have a small doubt .. we can use truncate key word in DDL triggers na .. I have seen it some where is this true ??
Yes, please look into this video
th-cam.com/video/THeY59Xw2RI/w-d-xo.html
Can you please explain difference between truncate and drop
I just know that both are DDL commands..
Thank you
Nice video.... But triggers do get fired for truncate also... You just have to create a DDL trigger.
Thanks, Yeah, That i covered in this video
th-cam.com/video/THeY59Xw2RI/w-d-xo.html
Hi Siva, It is very good explanation for all concepts you have posted to till date. can you help to post partition table concepts and materialized views concepts as well.
Sure, Please stay tuned
sure
Hi Siva sir, how to reclaim the space after deleting million records?
My requirement is I have 15 million records in one table . From that table I need to delete nearly 6 million records. I can do by creating a copy table with the required records and drop the table and rename the copy table to original table but this is risk because it contains constraints, triggers, grants, etc. so I prefer to go for delete. But I want to know is there any option so that post deletion can we reclaim the space?
alter table enable row movement;--> to move the records to make blocks more efficient
alter table shrink space command; u can try these....no guarantee...know that you posted long back...but if anyone can take help
Sir, You did greate job.👍
Really very useful and easy to understand for beginners. Thanks for your effort.
Welcome bro
Sum of sal each department in emp table by programing language. Interview question???
Nice covered in detailed difference b/w delete and truncate
Thank you
It is really good videos and nice explanation with example, it is helpful to all level of person Beginner to expert, keep going on
Thank you 🙏💐
Hi Siva, could you please make video for
> partitioning and types of partitioning.
> Facts and Dimensions
Regards,
Sujaa
Sure, Please stay tuned
Hi developers, I am preparing for SQL Interviews Is it worthy to follow this playlist I am bit confuse because there is many Oracles playlist in this channel like 12C, 18C I am a nob person in this SQL or Oracle. Any guidance or suggestion would be Highly appreciable
Thanks !
You can pick any video in interview questions list, however go through in the same order
Thank you so much for your videos and good explanations
Welcome 🙏🙏💐💐
Hi Sir, can you plz add videos related to all DBMS packages like DBMS_OUTPUT, DBMS_ERRLOG, etc... If already added plz share the link. And thanks a lot sir for these superb videos.
Welcome bro, sure please stay tuned
@@SivaAcademy Hi Sir, still waiting for the videos on DBMS package.
Awesome sir! You deserve accolades!
My pleasure 💐
very helpful information hatsoff sir
Hi Siva sir, Actually I am a M.com student But I am interesting to learn Oracle dba. As a m.com student oracle dba is ok for me.
As per me, qualification doesn't matter, as far as you have interest and can spend enough time for practice, you can always learn, all the best 👍💐
Best answer and explanation for interview question...Thanks a ton Siva sir.
It's my pleasure,Thank you so much :-)
please i have senior IT DEVELOPER(PL SQL) coming up. please what are likely tecnical and scenario question to be ask?
This video series has 60+ videos, and planning to add more, please stay tuned for more questions. Scenario based SQL questions are covered in another playlist
I immediately subscribed. Thanks for the great content.
Thank you
When there is ddl (before truncate) trigger when you perform truncate this trigger might fire right... Kindly correct me if I am wrong
@Ramaraju, Yes, your understanding is correct.
Hi siva, then triggers can be fired on both ddl and dml. It wouldn't be the difference here, am I right?
@Barathi, explained here th-cam.com/video/THeY59Xw2RI/w-d-xo.html
Please look into this video for details on ddl and DML trigger on delete VS truncate
Hi Sive, Can you please attach the example script location for all the videos.
sure, for upcoming videos
After Truncate the table size is not reducing. same size sowing as after insert. why is this showing same size???? please clear the doubt.
can you please analyze the table, and gather stats and try again
Can you explain how triggers are not invoked when using truncate??
You have created a DML Trigger and truncating the table, so no trigger got fired. if we create a DDL trigger, will it work?
yes, DDL trigger will work, Please look into this video th-cam.com/video/THeY59Xw2RI/w-d-xo.html
Please could u explain ,
How to separate negative numbers and positive numbers in SQL?
yes, you can use either sign function, or case statement in sql
Hi Siva, What is high level watermark in this context?
Hi Syed, the maximum the block up to which the data was inserted earlier in a table is the level called high level water mark. This wont get reset in case of delete, whereas it will be reset to initial block in case of truncate.will post a separate video on this topic soon, please stay tuned.
Sir , please make video on different between versions like 11g , 12c , 19c
Already the new feature videos are posted with respect to each version, seperate playlist for each version is available, however i try to post overall difference across the version, please stay tuned.
Hi Sir,
This video is good.
Can you please post a video on Highlevel Water Mark and can you please tell me why to update the data_dictionary table and use of it.
Thanks
Thanks for your comment,Sure, Please stay tuned. will post on HWM soon
what is hight level water mark,Can u pls explain
Sure, please stay tuned, will cover in upcoming videos
A samll update on the DDL Trigger concpet . DML Triggers will not be fiered on Truncate but DDL triggers on schema leavel would be fiered.
you are right bro
Sir,
Truncate can be rollbacked. Please Check ....
Hi bro. I am like a your brother. I am asking you ,Can you make MVIEW AND ALL REFRESH
Usage and also Partitions and types of particition and
@ram,sure I will post videos on mviews and partitions
Could you please make some videos for dynamic Plsql
Sure, please stay tuned
Sir plz make video on schema objects n whts its use
@Shaheen, sure, Please stay tuned.
Thank you very much for the playlist. All videos are very good.
@Prashanth, thanks for your comments bro.
Please give us more information on high level water mark
Sure , please stay tuned, will cover in upcoming videos
Can i know for which command the structure remains same??
for both truncate, and delete, the structure of the table remains the same.....Only Alter command can change the structure of table
Thank u somuch ........very helpful
Welcome
Very nice explanation sir👍👍
Welcome bro
Thanks a lot. This was really useful information
High Level Water mark means ???
Hi Sir, Please make a video on partitioning tables
Sure bro, please stay tuned
@@SivaAcademy one more request...please make video on hierarchal queries and REGEXP
Yes bro 👍👍
Please explain high water mark....
@Shumaila, Sure, will post a seperate video on HWM.
@@SivaAcademy HWM video please 😔
Please explain what is high water mark
@bharathi, yeah sure. I will post a video soon
I want to update salaries with different percentages (5%,10%....)at a time on salary column in different values like(
Hi Bro,
I think this can be done, using CASE in Set clause of Update.
I will try to achieve this and will let you know.
Hi Shiva teach us on Datastage and python going forward..
Yeah I am about to start python videos, please stay tuned 👍
Really superbbbbbb.. keep it up ... subscribed .. waiting for more videos..
Thanks Aravind, i have posted more than 16 questions in this series itself, will be posting more....you can let me know if you are looking for any specific question, i can add as part of upcoming video.
Thanks again for your comment. :-)
Brother I already asked u about bulk exception u promised me that u will provide it with in a week but it is already more than 10 days please provide bulk exception please 🙏🙏🙏🙏🙏🙏🙏🙏
Please provide it me bro
Really sorry for late, content is ready bro, will try to post in a day or two, please stay tuned
what is alternate way to update table a using two table without using co-related sub query sub-query
in oracle
in mssql we do in below format
create table tst_table1_a(id1 number , name varchar2(100))
create table tst_table1_b(id1 number , sal number)
UPDATE tst_table1_b a
SET b.sal = (b.sal + 1000)
FROM tst_table1_b a
INNER JOIN tst_table1_a b ON a.id1 = b.id1
and b.name='xxx'
You can use merge statement right ?
Well explained Sir ... Thanks a lot :-)
@Sudir, thanks for your comments bro 🙏
What is different between for and forall clause
sure, please stay tuned, will be posting soon
What is mean by High level watermark
Performance tuning in sql
Hi sir, need a file of these questions. Thanks in advance
Very good explanation. Thank you.
Thank you
Please make video SQL ,PL/SQL Performance tuning
@Sagar, Sure, working on the performance tuning content only, will start posting soon.....may be from next month onwards...Please stay tuned.
What is mean by high-level watermark ?
will be covering soon