SAP ORACLE SQL Table Splitter - speed it up?

[Former Member]

Hi SCN,

I'm a pretty long time use of the SAP ORACLE SQL Table splitter and I'm pretty sure anyone who's run an OS/DB migration out there with ORACLE has probably used it. However a common issue I find is this -- if you're planning to run this as a first step in system downtime it can take quite a while on cluster tables (as you cannot split on ROWID).

In my example here it's a table with over 1 billion rows and it takes 2-3 hours to split it.

Now I have two ideas for taking this out of the business downtime.

1 - split the table during uptime --- if the field (in this case CHANGENR on CDCLS) will not change and the last, or "end", split is just greater than (>=) an upper limit; then it should still get all the pieces without issue as I see it.

2 - parallelise the split -- now you can parallelise an index creation or stats run etc. - but has anyone parallelised a table split operation? It seems feasible to me and yet the base note for the program doesn't mention any such option so it may not be possible.

Any thoughts appreciated - my initial notes and reasoning suggest that option 1 should be entirely safe where a sequentially increasing unique numeric field is used --- though checks and made to ensure data integrity is maintained are obviously a good idea.

Regards, Jamie / doonan_79

[Former Member]

General offline consensus is that you can happily split during uptime (though not on ORACLE ROWID, but this splits very very quickly anyway in downtime). If you're doing then just do a logic-check on the field you're using and a data check on target/source after import --- and test on non-prods before you use on a production migration.