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