Oracle Insert using FLASHBACK Query

Question

I have two identical schemas, on different databases connected via Database Link.

Schema_1: Source Schema. **Rows being inserted at rapid rate.**
Schema_2: Target Schema.

Rows being inserted into Schema_1 (source schema) at rapid rate.

I am running a SQL in Source Schema as follows:

Insert into Table_1@DB_LINK select * from Table_1

This statement takes several minutes.

Now I change the statement as follows ( using flashback query )

Insert into Table_1@DB_LINK select * Table_1 as of timestamp to_timestamp ( to_timestamp ( date ));

This query completes in few seconds.

Why such huge difference ?

Did you check how many records are returned by the queries: select * from Table_1 and select * Table_1 as of timestamp to_timestamp ( to_timestamp ( date ));? — kpater87
There is a lot different with these. The flashback query is the simplest. It will be looking at flashback data to be returning the data from the local database. But, the insert across the link will have to (1) read all the data from the table via full table scan (2) transport the data to some remote system across some unspecified network (3) be written to the remote table with appropriate transaction control / logging / redo / etc. To me, it is a no-brainer that the flashback query is faster. — unleashed
How does the db link affect the performance difference here? Also, is it really to_timestamp(to_timestamp(? That looks wrong to me... — William Robertson
@oradbanj Missed that, sorry. Then, I go to volume. How many rows are returned by each query? It does matter. — unleashed
"the number of records should not matter" Really? Well there goes twenty years plus of performance tuning practice. — APC

atokpas atokpas · Accepted Answer · 2017-05-02T05:22:10

In your flashback query you are selecting the rows which were inserted exactly on 05/01/2017 10:00:00. But in your non-flashback query you are selecting all the rows inserted in the table.

Simple demonstration:

SQL> create table t1(id number, name varchar2(20));

Table created.

SQL> insert into t1 values(1, 'joe');

1 row created.

SQL>  insert into t1 values(2, 'jay');

1 row created.

SQL> commit;

Commit complete.

SQL>  insert into t1 values(3, 'john');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 joe
         2 jay
         3 john

SQL> select * from t1 as of timestamp to_timestamp('02-MAY-17 11:00','DD-MON-RR HH24:MI');

        ID NAME
---------- --------------------
         1 joe

My first query, select * from t1;, is identical to your non-flashback query which selects all the rows from the table.

And my second query, select * from t1 as of timestamp to_timestamp('02-MAY-17 11:00','DD-MON-RR HH24:MI'); is similar to your flashback query, which selects only one rows.

Of course inserting one row is faster than inserting three rows.

Oracle Insert using FLASHBACK Query

1 Answers