Any fastest way to refresh From Read Only DB

Hi all,
I have 3 databases. First one is my production database and second db is my standby database which is in read only mode. The third one is my another application database which need to refresh the data from my Standby database.

Any one has bright idea on what is the fastest way to refresh data (This contains the join tables to make up a new table, of course all those base tables are getting from the standby database)? My current solution like following, Create table abc as select a,b from tab1@dblink, tab2@dblink where tab1.key = tab2.key; This is work.But I found this method is quite slow.

My initial idea is to create the materialized view just apply logs to the destination, but due to the read only mode standby database, the database seems not allow me to do that. So I need to create table rather MV.

Any suggestions are very much appreciated.

Thanks

You would need a logical standby database (9i Rel2) to create additional MVs on standby site.

Your problem with the current sollution is probably the fact that join is performed on the local server, although both joined remote tables are on the same remote database. So no matter which type of join is used, all the records from both remote tables are transfered to your third application database where they are joined. Try forcing the optimizer to perform join on the remote site and then transfer only the final resultset to you. Try the following method and see if it is any faster that the current one:

SELECT /*+ NO_MERGE(v)*/ * FROM
(SELECT a, b FROM tab1@dblink t1, tab2@dblink t2
WHERE t1.key = t2.key) v;

Create a view in which 2 tables joined both in Prod and Standby DBs.

From the 3rd Server, extract rows from the view. I have not tested this method. But try it in your site. This may work faster than joining 2 tables using dblinks.

Quote:

---

Originally posted by tamilselvan
Create a view in which 2 tables joined both in Prod and Standby DBs.

From the 3rd Server, extract rows from the view. I have not tested this method. But try it in your site. This may work faster than joining 2 tables using dblinks.

---

This is exactly the same as the method I suggested, with the only exception that I used inline view instead of hardcoded one. With my method there is no need to have (and maintain) additional view on two remote databases (primary and standby), where it is obviously not needed at all for their operations.