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
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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-WF25_n5knrw.html
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?
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
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.
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
