Link to home
Create AccountLog in
Avatar of sam15
sam15

asked on

MaterialViewtoStore_MainMenuSummaryStats

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)?


Query:

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.
 
 
 
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.
Avatar of sam15

ASKER

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.

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.
Avatar of sam15

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer