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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.