Solved

CopyiingRemoteTables

Posted on 2011-03-23
11
342 Views
Last Modified: 2012-05-11
I have an oracle 9i web application with a slow main menu (3-5 seconds) page. The menu design is such that the page is about 20 KB in size because it builds dynamically list of vendors to view/edit data for and list of contacts, etc. Also, all the application links are listed on the main menu.
It also has a few derived numbers that are calculated real time (i.e open orders). so every time
user navigates to main menu we run the sql again.

I did a SQL trace and checked the tkprof report and found that two SQL commands that use a dblink to lookup an organzation and contact table cause the delay sometimes. It could be a network or machine issue.

The tables in the remote db which is on same machine do not get updated that often. I am also
looking up a few columns in those tables.

i am thinking of copying those remote tables locally to speed things up and refresh the tables
nightly from remote tables.

Do you agree? and how would you implement this.

WOuld you

a) create a local table with same structure and procedure and job to DELETE data and the INSERT new
data from remote table.
b  Run DDL (CREATE TABLE AS SELECT * FROM TABLE@LINK) in local database to create the local table
from remote table every night..
c) WOuld you create Materialized view for local table and schedule to refresh nightly from remote
table..

Can you advise?

0
Comment
Question by:sam15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 6

Expert Comment

by:Javier Morales
ID: 35205455
The best way to ensure data consistency is using materialized views refreshed nightly over the dblink.

Make it fast refreshable using materialized view logs, and it will run quite fast :)

Kind regards,
0
 

Author Comment

by:sam15
ID: 35206729
Data hardly changes in those tables but still i need to copy it nightly. I only use a few columns so i was wondering if i copy a aubset of the table.

But why cant you DELETE and INSERT into a table too.

I guess if i went with MV solution i would still need a job to refresh it nightly.
0
 
LVL 6

Expert Comment

by:Javier Morales
ID: 35206816
you can set up the materialized view to refresh each night (sysdate+1). A refresh group will be created automatically for this task (implicit job). Don't need to create an aditional job.

Of course, you can also delete and insert (manually or using a job), but this way you have the two "tables" consistently related, and you will not need to run anything to sync them.

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 35

Expert Comment

by:johnsone
ID: 35207048
Why do manually what the database can do for you automatically?  As suggested, use the materialized view approach as suggested.  With a log on the master table it will track every changed row and only move what needs to be moved at the time of refresh.

Materialized views can be build with a subset of data.  Be sure that check the rules for fast refresh to ensure that you are still able to do a fast refresh.  That way only changes need to be propogated.
0
 

Author Comment

by:sam15
ID: 35207545
I normally use this format MV for complex sql and refresh via job nightly.
 
  CREATE MATERIALIZED VIEW MV_SUMMARY_REPORT
  build immediate  refresh on demand
  as
   SQL Query
   
   
  For this case to copy remote tables shall i make READ only view
 
  CREATE MATERIALIZED VIEW hr.contacts FOR UPDATE AS
    SELECT * FROM hr.contact@hr_link;
 
How often this will refresh though if i only want it nightly?
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 35208088
It sounds like the materialized view would be read only.  In the most basic form, this would be what you need:

On the master:

CREATE MATERIALIZED VIEW LOG ON <tab> TABLESPACE <tblspace>;

On the remote site:

CREATE MATERIALIZED VIEW <mv_name>
TABLESPACE <tblspace>
BUILD IMMEDIATE
REFRESH FAST START WITH SYSDATE NEXT TRUNC(SYSDATE) + (25/24)
AS SELECT * FROM <tab>@<dblink>;

That should build a materialized view that is refreshed daily at 01:00.
0
 

Author Comment

by:sam15
ID: 35208531
Great. so i dont need a job to refresh nighly using the log approach instead of ON DEMAND.

How can i tell that the MV is getting refreshed though. Can i add another timestamp column to the VIEW to check if it is getting refreshed and has up to date data nightly?

just in case DB was down or MV refresh failed .


0
 
LVL 35

Expert Comment

by:johnsone
ID: 35208715
The log is not what generates the job.  The specifications of START WITH and NEXT set up the job for you.

You can check DBA_MVIEW_REFRESH_TIMES to see when the last time a view was refreshed.
0
 

Author Comment

by:sam15
ID: 35209134
yes, that view has it. I guess i dont need to add a TIMESTAMP column in the MV_table itself.

In case the remote DB was down for days, would the MV delete the data it has or it keeps it until remote DB comes back up.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 35209187
If the master database was down or not accessible for days, the view would not be refreshed.  Any data in the view would remain.  It would not be deleted.

This may not be true of a complete refresh, however since you are setting up a fast refresh, it should not be an issue.
0
 

Author Comment

by:sam15
ID: 35212161
Excellent answer.
I have another question on MV but i will post separately. it is for storing a derive value so it may need diff implementation than this. I will post it now.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trigger to update a specific column in Table B after insert or update record from Table A 13 73
scheduler notification 9 80
Dbms_job.change procedure 16 37
Oracle Date add 9 34
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
The viewer will learn how to count occurrences of each item in an array.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question