I have one query in main menu that uses DB link to computert stats and slowing down the navigation to main menu.

I am thinking of creating an MV for main menu and storing derived numbers there and judt do simple select without
running all calculations real time.

WOuld this be a complex MV that needs to be refreshed on demand (i.e nightly)?


SELECT count(*) into l_title_cnt from books@bkp_link
 where (status_flag='W' and contract_no is not null and batch_date is not null) or (status_flag-'S' and shipment_status='F');
 I am also thinking of running a job whenever a shipment takes place so that the count stored in MV gets updated.
 The reason i want to schedule a job is that sometime you cant update the number stored in the MV by adding or subtracting to it and I had to complete refresh or create a second OVERRIDE table to select the updated number from.
Who is Participating?
slightwv (䄆 Netminder) Commented:
Yes. Refresh on commit is like replication.

MV's create a database job ust like you would manually but they both create jobs so the really aren't 'different'.

MV's just create the job for you but after that, a job is just a job.
slightwv (䄆 Netminder) Commented:
You will need to refresh as often as you want up-to-date info.

If the system can handle it, why not refresh on commit?

What version of Oracle are you using?  11g has a new feature called result caching that might work for you.  Never tried it over a dblink though.  You will need to see if that works.
sam15Author Commented:
i am using 9iR2 now.

Not sure what you mean by refresh on commit. you mean instead of submitting a job to refresh MV, you refresh as part of the shipment transaction? I Can do that but i am worried the link might be slow and that would slow the user transaction. so it is better to complete transaction and schedule the refresh via a job instead.

slightwv (䄆 Netminder) Commented:
Not sure what version it showed up in but fast refresh MV's can refresh when transactions are comitted in the base tables.

The docs will talk about this.

Like I mentioned, not sure about base tables over a link.

Now that I rethink this, if it is just a count, why use a MV.  Just create a local count table with a refresh job?  Should be about the same.
sam15Author Commented:
oh, you mean REAL TIME. when someone updates the master table and commites the MV will be refreshed using the logs. That becomes like multimaster replication I guess.

yes, i can do a regular data table and refresh via a job but i thought MV will automate this thing.

so instead of creating a table, a procedure for the SQL, and a job that calls the procedure, one MV does it all.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.