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.
midrange.com
