My goal is to clone a table from an Oracle database to a SQL Server database every midnight. I am using Talend Data Integration for this task.
The problem is that this table has around 70 million records (~100GB of data, including indexes), and I can't just drop it then load it again, because it is too resource intensive (especially because other tasks must be done).
The obvious solution would be an incremental update: I query the newly inserted records from Oracle and insert them into SQL Server.
So far, so good.
But the table I am querying from in Oracle has no ordering at all. Its unique key is composed of four fields that can receive any value, and I have no control over them. I must ensure that only "new" records are returned, but there is not a field (or set of fields) that I can use to sort the table and 'force' a sequential ordering. To be clear: none of the columns stores any kind of CreationDateTime datum.
I have only
SELECT permissions on the Oracle database, so I can't make any changes, for example to flag records I've already imported.
I can connect to both databases from the same computer.
Querying the destination database for each record is also very resource intensive. Network and disk overheads are a problem, but they are not my main concern here.
Has anyone faced this problem before? Any idea how to solve it? Maybe there is no solution for this problem, but I was expecting some sort of 'trick': some hidden Oracle column, internal procedure, or other dark magic...?
My last resort is to make some assumptions to import, like "assume these records are ordered and import, then check consistency of some well chosen samples, if inconsistent, run full import again". This is very far from ideal, but at least I will not run a full import every time.