An MQT is just a TABLE, but with special tracking. It refreshes from the defined SQL statement. Any other I/O is the domain of the updater. Thus if the desire is to have something there after every refresh, then that I/O has to be implemented as post-refresh activity.

Personally I would avoid an MQT to do something easily done, w/out having the database having to track the object for its refresh definition. I would either encapsulate the create [ignore already exists], clear, insert-select-from in a routine, or just dynamically get the data when it is needed. Consider, there may be little to no justification, to make and keep a /temporary/ copy, when the SELECT already encapsulates all of the logic to obtain the list to work against for the given scenario. Depending on how temporary, QTEMP might suffice; GLOBAL TEMPORARY TABLE versus MQT works there.

The real benefit of the MQT is /summarization/ of data and then the use by the optimizer to rewrite the query to use that MQT instead of dynamic retrieval of the set. The example below is a detail select, not a summary, and its result is excluded from use by the optimizer. So I am suggesting that having the database track the SELECT instead of a named routine [program\procedure] seems unnecessary; daft even, if a program will [and probably] be defined to effect the REFRESH anyhow. If the code really wants to search the active list of source files, the original suggestion to create and insert should probably instead just be a VIEW to obtain the here-and-now; run against the logical set of 'S' files, rather than ever make a copy of the set to run against.

Regards, Chuck

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].