4

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.

  • 2
    Do you have the active cooperation of someone that controls the Oracle database? Even if changing the table is disallowed, perhaps they would be willing to enable a change data capture subscription for the table or publish changes via Streams that you could subscribe to (though that would likely require a constantly running subscriber process or at least something that could buffer the changes for the full day). – Justin Cave May 11 '16 at 1:32
  • @JustinCave, I must check, but I would prefer to not rely with this. – Diego Queiroz May 11 '16 at 2:48
  • 2
    If there is nothing in the data that can be used, you're not realistically going to be able to do an incremental load without some sort of assistance from the folks that own the source database. You could potentially compute a hash for each row, compare that hash to the hash stored in the local copy, and build an incremental load off that. But that's going to mean scanning 100 GB of source data and computing the hash for every row every time. The owners of the source system should be anxious to help you avoid doing that. – Justin Cave May 11 '16 at 3:05
3

You can use ORA_ROWSCN pseudocolumn to find rows that have been changed since last update. By default it tracks changes by database block, not by row, but it can be used to limit the amount of data transferred between databases.

I would suggest a following procedure:

  1. Find the current Oracle SCN (System Change Number)

    select dbms_flashback.get_system_change_number from dual;

  2. Get rows that have been changed since last update

    select * from [source_table] where ora_rowscn > [last scn] and ora_rowscn <= [current scn]

  3. Resolve possible duplicates in your target database

  4. Store the [current scn] to target database so it can be used as [last scn] during the next iteration
  • Step #3 would be critical as that pseudocolumn ALWAYS tracks chages by database block, not by row, so if just one row in the block is changed, you will get back all rows in that block. – Mark Stewart May 11 '16 at 17:04
  • You are my hero! But why do I need to query the current SCN? Why not just select * from [source_table] where ora_rowscn > [last scn]? – Diego Queiroz May 12 '16 at 4:02
  • You need to query the current scn to be used as [last scn] during the next iteration; it's better to get this before reading the rows because if you get it after, it might already have been incremented (and possibly cause some rows to be skipped during next time) – sjk May 13 '16 at 5:44
  • It's possible to track changes by row by setting ROWDEPENDENCIES option for the table. – sjk May 13 '16 at 5:47
1

You might be able to get a performance improvement by using OPENQUERY from the SQL Server side to the Oracle database set up as a linked server. Then you could use the SQL Server MERGE function to merge in any new rows from Oracle to SQL Server, that is, any rows that do not match, column for column, with what you already have in SQL Server.

Something like this:

MERGE INTO sql_table S
USING (
    SELECT col1, col2, col3
    FROM OPENQUERY(ORACLELINKEDSERVER, 'select col1, col2, col3 from ora_table')
    ) O
    ON S.col1 = O.col1
        AND S.col2 = O.col2
            AND S.col3 = O.col3
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (
        col1,
        col2,
        col3)
        VALUES (
        col1,
        col2,
        col3)

I can only tell you from experience I have moved to this solution myself from what used to be a simple 'truncate SQL table and rebuild it' using an SSIS integration script for circa 1 million rows. In real time I have an improvement of 32mins (34 minutes for SSIS led truncate, rebuild package vs. 2 mins for Linked Server / MERGE statement)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.