You're joining on customer number. An index on customer number on each
of the two tables should be all that's needed. You may need to union the
records selected from the invoices that have territory in them with the
invoices with territory from the customer master.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas Handy
Sent: Tuesday, February 03, 2009 3:10 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL index possible with keys from two tables?
Walden,
Can you explain the use-case? When would you want an index with fields
from multiple files?
Just hoping to increase select performance. I have indexes over the
base files now. Here is the scenario.
Customers are assigned to sales territories, and for a variety of
reasons may get reassigned to a different territory from time to time.
Orders and invoices are coded with the sales rep of who gets commissions
for it, but a sales rep does not necessarily qualify to get commissions
on everything in their territory (again for a variety of reasons not
germane to the discussion).
I can very quickly select things they get commissions on, because that
field is in the order / invoice records. We also need to select all the
orders or invoices within a given territory (even if they do not get
commissions on it), but the territory assigned to the customer is not
part of the order / invoice records. So it has to join the order /
invoice to the customer record to find the currently assigned territory.
If I could create an indexes including both the customer master
territory value and order fields typically used to subset the where
clause, then I figured the query optimizer may be able to perform the
record selection faster. In the past, we only did selects based on the
rep getting commissions, and response is subsecond because of the
indexes I could build.
When they decided to have an alternate view with everything in the
territory (even if not commissioned), the join slows it down. Not
really bad considering what it is doing -- but not subsecond either.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
