I have a fairly large Business Application related to Order Management, whose data is in Oracle Database.
All data can be related to Orders. Now, the data size is huge, millions of records in tables - thereby slowing down my application SQL Queries.
I am planning a replica schema of my main 'Order Schema', say 'Archive Order Schema'. And write SQL Queries to move (old order) data from main to archival schema, one (old) Order at a time.
But the SQL Queries are quite slow, and to move all data of a (old) Order (across so many tables), takes very long time.
Any design / approach / optimization inputs are welcome.
First, as the others have noted, a few million lines in Order Management tables is nothing. Even a few hundred million rows or billions of rows is not a challenge. We manage an EBS with larger Order Management tables without much effort. Make sure you are gathering schema statistics using the EBS Concurrent Request (not DBA tools); although you might check with your DBA on rebuilding your indexes. Also make sure you are patched up as Oracle EBS patches often include different indexes to improve performance from logged problems. Run some AWS stats or even a SQL trace to find your bottle necks and work with Oracle Support.
Next, DO NOT use SQL to archive seeded tables. You will have problems and Oracle will not help you then. You should instead do some research and use Oracle's built in and supported archive and purge processes. Start by reading Note 752322.1 on My Oracle Support, it will point you to managing your data in Order Management.