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.
This mailing list archive is Copyright 1997-2019 by midrange.com 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