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, May 01 '17 at 18:54
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, May 01 '17 at 19:09
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, May 01 '17 at 19:30
@unleashed: both queries are fetching data from local database and inserting into a remote database. The only difference is that the second one is fetching using "AS OF.." syntax to make use of Oracle Flashback query. — oradbanj, May 01 '17 at 19:50
@oradbanj Missed that, sorry. Then, I go to volume. How many rows are returned by each query? It does matter. — unleashed, May 01 '17 at 19:53
@WilliamRobertson : both queries are using DBLINK. The syntax is abbreviated but is correct ( select * from TABLE as of timestamp to_timestamp ( '05/01/2017 10:00:00','mm/dd/yyyy hh24:mi:ss' ) — oradbanj, May 01 '17 at 19:55
@WilliamRobertson Oracle will implement it using an implicit call to `TO_CHAR` like this `TO_TIMESTAMP( TO_CHAR( TO_TIMESTAMP( date ), NLS_TIMESTAMP_FORMAT ), NLS_TIMESTAMP_FORMAT )` so, while the second `TO_TIMESTAMP` is unnecessary it shouldn't break the query. — MT0, May 01 '17 at 20:17
"the number of records should not matter" Really? Well there goes twenty years plus of performance tuning practice. — APC, May 01 '17 at 21:13
The number of records will matter if between now and given date from the second query there was a bulk insert. — kpater87, May 01 '17 at 21:14
@oradbanj, I can see both queries are using a db link. I was asking what it had to do with the question. Also converting a date to a timestamp to a string to a timestamp is probably just fine. Good luck. — William Robertson, May 01 '17 at 21:25
@APC - what I meant was that both queries are equivalent, both are inserting data over database link. The amount of data fetched by both queries will be approximately equal (but not exactly same because records are being inserted into source at rapid rate). The only difference is that the second query is trying to construct a consistent image of data using FLASHBACK. So I am leaning towards investigating if USING FLASHBACK slows the process. — oradbanj, May 01 '17 at 21:57

atokpas · Answer 1 · 2017-05-03T23:29:57.953

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 Answers1