How to Make Inserts, Updates, and Deletes Faster: Databases for Developers: Performance #8

Queries are only one part of SQL performance. Often you need to make writes faster too!
These have extra performance considerations over selects.
Watch this video to learn how about optimizing insert, update, and delete statements.
Need help with SQL?
Ask us over on AskTOM: asktom.oracle.com
Twitter: / chrisrsaxon
Daily SQL Twitter tips: / sqldaily
All Things SQL blog: blogs.oracle.com/sql/
Test your SQL Skills on the Oracle Dev Gym: devgym.oracle.com/
============================
The Magic of SQL with Chris Saxon
Copyright © 2020 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.

[@OmarAbdelaziz__47]

Intelligent Metaphor. Your examples are very awesome and intuitive.

[@TheMagicofSQL]

Thanks, glad you find this useful

[@mohammedbadah5537]

Great explanation

[@alisalem8755]

Thanks

[@masterprattu]

Amazing videos! thanks for making understanding everything so easily.

[@TheMagicofSQL]

You're welcome Prathamesh, glad you found this useful :)

[@KiranKumar-zt1us]

great eg...

[@KINJANBHAVSAR]

One doubt, what if for each record we want to update the record in database and also call a process to log an event to trigger some automatic action? Is there any efficient way to do that without loop?

[@TheMagicofSQL]

You want to update the rows and log which rows you updated? You can use RETURNING BULK COLLECT to capture values oracle-base.com/articles/misc/dml-returning-into-clause#collections

[@KINJANBHAVSAR]

The Magic of SQL No, I have json from which I extract data using loop and then I update them in the main table for which the data is and I call a common package process to log that change which inserts that into other table.

[@TheMagicofSQL]

I'm not sure exactly what you're doing. If you can put together a simple example (create table + inserts + your process) and post it on asktom.oracle.com we'll see what we can do to help

[@archananair3274]

Awesome concepts.but have a doubt - What will be the best approach to delete millions of records (historical transaction data say from year 2008 to 2018)from a table which has many child tables too (32) and in the child tables it has even more data (1 transaction itself can have 1000 records in the child table).And how can we process this in production environment without affecting the active transaction

[@TheMagicofSQL]

The ideal solution here is to use reference partitioning. This enables you to partition the parent table by date and copy this scheme to all the child tables. Dropping the parent partition removes all the associated rows from the child tables.
For details on this see the recording of my Ask TOM Office Hours session on data archival th-cam.com/video/WF25_n5knrw/w-d-xo.html