Are Materialied Views Good in OLTP

Hi,

Is it ok to have materialized views in an OLTP system.

Please advice.

Thanks

usually for OLAP/Data Warehousing

Part of "Materialized Views" concepts is also the area that was previously known as "Snaphots". Snapshots as local replica of data that is othervise mantained on some remote database is widely used in OLTP also.

The reason i am asking is because I have a table with 4 million rows. I need to do a sum on one of the columns based on a condition and it takes a lot of time. If I use materialized view then its fast enough.

I was just wondering whether to use it or not as it is a OLTP system .

Please advice.

Thanks

To My understanding from the above posts, You are seeing a slow response . That means your query is slow, why don't you try how your query is waiting ... like checking v$session_wait and if possible you can use function based indexes, if necessary.. and also if this query is getting executed frequently( if this one is a literal SQL) , PIN it.. there will be no reparsing ..


Personally , if i want to have more refined data which will be queried later by a front end application , and i would go for a MView too

Its a simple query inside a cursor in a stored procedure.

It is executed about 1600 times inside a cursor

the query is

select max(price_date) into v_last_date from stock_price where company_id=v_company_id and exchange_id=v_exchange_id;


and all 3 columns are indexed.

What are the disadvantages of a MV in an OLTP environment.

Please advice.

Thanks

Assuming that , you have already seen the TKPROF output and the Indexes created on the coulms are getting used.

You can try few more things...

1. Index fragmentaton, if so Rebuild all of them.
2. Anayze the STOCK_PRICE table
3. Since you have an aggregate function, sorting operation will take place , So you have to check to see if you are prefroming disk sorts. It is a bit hard to fins which SQL is consuming how much sort area, so you can try this when there is almost no or very less activity on DB.

4. To eliminate the disk sorts , you may have to increase the SORT_AREA_SIZE...

5. And also if you have 1600 executions of the same SQL within a loop , it would be nice to keep SORT_AREA_RETAINED_SIZE equals to SORT_AREA_SIZE, this can be done at the session level too.

Hope this helps towards your goal...

Quote:

---

Originally posted by mrvajrala
Assuming that , you have already seen the TKPROF output and the Indexes created on the coulms are getting used.

You can try few more things...

1. Index fragmentaton, if so Rebuild all of them.
2. Anayze the STOCK_PRICE table
3. Since you have an aggregate function, sorting operation will take place , So you have to check to see if you are prefroming disk sorts. It is a bit hard to fins which SQL is consuming how much sort area, so you can try this when there is almost no or very less activity on DB.

4. To eliminate the disk sorts , you may have to increase the SORT_AREA_SIZE...

5. And also if you have 1600 executions of the same SQL within a loop , it would be nice to keep SORT_AREA_RETAINED_SIZE equals to SORT_AREA_SIZE, this can be done at the session level too.

Hope this helps towards your goal...

---

Yes I have done all the above mentioned things.

But my question is still the same. What are the disadvantages of MV's in an OLTP system. I am asking this because by using a MV my query has become much much faster.

Also to give a more detailed insight about my operations the stock price table is batch loaded nightly hence the MV will have to refresh only once every night. The resr if the database is OLTP.

Thanks

I dont know where this discussion is deviating.

If Material Views is working, then JUST USE IT. You should not have any problems.

4 Million rows and a Summary Query, sounds like a good candidate for a MTV.

Someone wanted a Query Result Online over internet on a table with 6 mil rows. How hard can I push ? I just used a procedure in a job that puts my result in a separate table once every 4 hours.

If your sum operation is frequent and taking too long time, You probably use MV with refresh fast on demand option. It will not slow down your online transactions.