Santosh Kangane

Recently, I was working on an Archival process for one of my client, where this process suppose to move data from Live OLTP system to Archived database. This process is going little slow, so one of my DBA has advice me following approach, In delete part instead of using single delete statement with Join, open cursor and loop it through. Then fir delete statement for each row by row in a loop. I was wondering how oracle would go about it ? and will it improve the things? So, h ere is my small investigation on this ,      1.    If you have db_file_multiblock_read_count = 8 and Tablespace Block size = 8KB . So, in single IO            read request oracle would read 64KB of data.      2. If you fir a delete statements in a loop , oracle will have to instantiate IO request for each delete statement           and out of that it will pick up just one row or few rows if there is one to many relation.      3. For subsequent delete statement if the data is in the cache, then it will be