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.
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.
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 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.
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.