Are Materialied Views Good in OLTP
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Are Materialied Views Good in OLTP

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

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

    Please advice.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    usually for OLAP/Data Warehousing
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  5. #5
    Join Date
    Feb 2001
    Posts
    290
    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

    Madhu Reddy
    xdollor@yahoo.com

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  7. #7
    Join Date
    Feb 2001
    Posts
    290
    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...
    Madhu Reddy
    xdollor@yahoo.com

  8. #8
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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

    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  9. #9
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    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.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width