Results 1 to 5 of 5

Thread: MQT Refresh

  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: MQT Refresh

    Hi,

    I have just implemented some Refresh Deferred MQTS in version 8 and am having a few issues with the performance of the refresh command.
    From a base table(100million rows) i am refreshing an MQT to aggregate up to daily level. This seems to be ok.
    I am then refreshing an MQT to aggregate up to weekly level, this is where the problems start.
    If you execute the SQL in the create weekly MQT as a simple select the data is retrieved quickly as the select is redirected to the daily MQT.
    However as soon as you issue the weekly MQT refresh the refresh is performed against the base tables, hence taking 2 to 3 times longer.
    Does anyone know if this is the intended functionality or is there is something i can do to force it to redirect to the MQT.

    N.B. we are not in a position to use refresh immediate or staging tables to do an incremental refresh!

    Any help/ideas would be grately appriciated.
    Clive

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: MQT Refresh

    Did you do a RUNSTATS on MQT following the change in definition ?

    If you haven't, I suggest you do and test ...

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Re: MQT Refresh

    Originally posted by sathyaram_s
    Did you do a RUNSTATS on MQT following the change in definition ?

    If you haven't, I suggest you do and test ...

    Cheers
    Sathyaram
    Hi Guys,
    one question:
    if we have host variable in our sql statement can we create MQT?

    Thank you in advance for your answer.

  4. #4
    Join Date
    Jan 2004
    Posts
    3

    Re: MQT Refresh

    Originally posted by sathyaram_s
    Did you do a RUNSTATS on MQT following the change in definition ?

    If you haven't, I suggest you do and test ...

    Cheers
    Sathyaram
    I have done a run stats,
    Any other ideas.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: MQT Refresh

    Only your statistics on your MQT and base table, and your query can explain this behaviour

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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