RE: SQL index possible with keys from two tables?

MQTs are the holy grail of SQL performance tuning, aren't they? Imagine,
having your result set precreated already, then building index(es) over it
for better query selection performance. Yeap, it doesn't get better than
that.
Problem is that they're not really meant to be used in a transactional
database, but rather data warehouses.
I've seen them used by some customers with varied success. Query optimizer
is very picky about choosing them, so some customers code to them directly
(in the FROM clause), which then makes them no different than temporary work
tables.
You can of course drive the REFRESH TABLE command yourself and avoid the
'staleness' issues.

In any case, I think before you spend too much time experimenting with the
MQTs, try building perfect indexes over the tables based on your SQL
statement. Try selection keys with equal criteria with most selective keys
first, followed by the join keys.
If that doesn't improve the performance, try leading with join keys,
followed by selection keys with equal criteria, followed by non-equal
selection criteria keys.
You can learn more about the 'perfect' index by reading IBM's 'Indexing
strategy' whitepaper.

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: SQL index possible with keys from two tables?

Dan,

You're joining on customer number. An index on customer number on each

of the two tables should be all that's needed.

It does work -- I just want it to work faster. :) There are typically other
where clause conditions, and I have indexes to support them to. But the
indexes are on just the one table. It looks like a MQT with indexes over it
may be a perfect combination, except they i does not yet support Refresh
Immediate. For invoices, Refresh Defered will work just fine as new records
only get inserted in day-end processing. For open orders, the Refresh
Defered may lead to some data latency. But I think I can live with that.
Those orders are more the exception than the norm, so I use my current logic
over just the order / invoice files to find all the ones they get commission
on, and union the MQT restricted to those in territory but commissioned
elsewhere. So even if I can only perform a Refresh Table xxx a few times a
day -- or even once a day -- only those orders will be impacted and
everything else should remain subsecond.

And when Refresh Immediate gets supported, I won't even need that.

I may not even end up using a MQT -- I need more time trials etc -- but it
is a concept I wasn't even familiar with yet so it seems like a good
candidate to experiment and learn more.