Thanks for the question, Karan.
Asked: May 10, 2017 - 2:04 am UTC
Last updated: May 11, 2017 - 3:22 am UTC
Version: Oracle 12c 12.1.0.2.0
Viewed 1000+ times
You Asked
We have partitioned tables (with indexes) with about 12 million records in each partition. We transfer data to non-partitioned (no indexes) table, perform calculations, and move data back to partitioned table when finished. These are all parallel INSERT/DELETE statements. Rather than inserting all 12 million records at once, we execute 24 parallel sql statements.
However, we sometimes we see object no longer found issue during INSERT/DELETE statement. Is there a way to determine what might be causing this issue? We have verified that we don't do DDL during these operations.
P.S. What's the best approach to move data between partitioned/non partitioned tables? Will exchange partition method might be better preference for this scenario?
Thanks.
Karan
and Connor said...
Definitely exchange partition is a good option, but that heavily depends on your indexes. If you have a lot of *global* indexes, then exchange is going to cost you a lot of index maintenance, which might outweigh any benefits.
In terms of the 'object does not exist', are you performing any truncate commands ?
See
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9509565700346771185 for an example of that.
And, in terms of scale, 12 million rows is not a lot of rows to insert, ie, it should only be seconds not minutes, even in serial. So perhaps re-assess the need to be using all those parallel sessions.