Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2017
    Posts
    3

    Unanswered: Doubts While creating MQT

    Hi Team.

    Need some clarification to create MQT

    my table1 size is around 7TB

    1. Whether it will increase in Size compared to actual table?
    2. While creating MQT it will impact actual table in performance whether need downtime?
    3. While Refreshing MQT immediate refresh it will update only the changed and new records or it will take more time by checking all the records in actual table
    4. whether it will increase performance in below scenario
    Table1 having columns like this (ID-Index created,name,age,place,joindate,creationdate)
    while fetching records from this it takes only 20 minutes for example based on ID column which
    index created already but giving condition for non index column it take more and more time.

    Now Am creating MQT Table1_MQT based on ID and Joiningdate , creationdate with index
    (joiningdate and creationdate)

    Now joining two tables as below

    select a.id,b.joiningdate from table1 a, table1_MQT b where a.id=b.id and joining date between
    '1-jan11' and '31 dec11'

    This will increase the query performance?

    Thanks in advance

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Many factors determine the answers including facts you omit from your question such as what indexes exist, whether the table is compressed, whether it is range partitioned or distributed or both, and what type of runstats exist, and the DB2 version/platform .

    A practical suggestion is to determine the access plan on the production environment (for the current query). Then find a different non-production environment with the same database DDL (with reasonable data volumes in the relevant tables) and determine the access plan for the same query on the non-production environment. If the access plans match , experiment with the MQT on the non-prod environment to assess the impact on the access-plan(s).

  3. #3
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    ragursru1983,
    You opened three separate threads for your single problem. Plus posted the question in other sites. As Mor has told you in all three of your threads here, you do not provide enough info for anyone to give you any suggestions. From what I get from all of your postings that I saw yesterday, you need to create an index on the table as for its impact, how about creating it on a test system, that will answer most of your questions and then you can come back with more specific questions for us and then see about scheduling when you want to create that new index in your production environment.
    Also, 7TB is pretty large for a single table, sure you're not looking at your tablespace size? If it really is your table, then I'd tend to think that you may want to relook at the design.

Posting Permissions

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