How to optimize the oracle query for repeated table types?

advertisements

I have a database infrastructure where we are regularly (at least once a day) replicating the full content of tables from a source database to approximately 20 target databases. Due to the replication code in use (we have to use regular oracle queries, no control or direct access to source database) - this results in 20 full-table sorts of the source table.

Is there any way to optimize for this in the query? I'm looking for something that would basically tell oracle "I'm going to be repeatedly sorting this entire table"? MySQL had an option with myisamchk where you could tell it to sort a table and keep it in sorted order, but obviously that wouldn't apply here for multiple reasons.

Currently, there are also some intermediate tables involved (sync from A to B, then from B to C.) We do have control over the intermediate tables, so if there are tuning options there, that would be useful as well.

Generally, the queries are almost all of the very simplistic form:

select a, b, c, d, e, ... z from tbl1 order by a, b, c, d, e, ... z;

I'm aware of streams, but as described above, the primary source tables are outside of our control, so we won't be able to use streams there. (Additionally, those source tables are rebuilt completely from a snapshot daily, so streams wouldn't really work anyway.)


you could look into the multi-table INSERT feature. It should perform a single FULL SCAN and will insert into multiple tables. Consider (10gR2):

SQL> CREATE TABLE t1 (ID NUMBER);

Table created
SQL> CREATE TABLE t2 (ID NUMBER);

Table created

SQL> INSERT ALL
  2     INTO t1 VALUES (d_id)
  3     INTO t2 VALUES (d_id)
  4  /* your select goes here */
  5  SELECT ROWNUM d_id FROM dual d CONNECT BY LEVEL <= 5;

10 rows inserted
SQL> SELECT COUNT(*) FROM t1;

  COUNT(*)
----------
         5
SQL> SELECT COUNT(*) FROM t2;

  COUNT(*)
----------
         5

You will have to check if it works over database links.