DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Any fastest way to refresh From Read Only DB

  1. #1
    Join Date
    Oct 2000
    Posts
    250
    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width