Re: Searching across three files

Right, you can't put an index on a view. But if you do a SELECT on the view and have a WHERE clause, I hope the optimizer will apply that WHERE clause to all the parts of the UNION and take advantage of indexes you have.

I would try putting a single-field index on each one - or an LF, if you are more comfortable with that. Then run STRDBG with no options. Then try a SELECT with a WHERE clause that is specifying the field - and no expressions, just FIELD = VALUE. Your job log will tell you whether those indexes were used. If not, the log might give you suggestions for improving things.

HTH
Vern

Charles Wilt wrote:

On Thu, Oct 23, 2008 at 2:02 PM, James Lampert <jamesl@xxxxxxxxxxxxxxxxx> wrote:

One thing: how would I index this, for better performance if the user is
doing the look-up by part-number? Unfortunately, while I vaguely
remember SQL indexing from a class I took in Rochester, neither my
memory nor my copy of "SQL for Dummies" have a whole lot to say on the
subject.

You can't.

Best you can do is have an index over part-number for each of the
three physical tables.

The query engine will use those individual indexes when you query the view.

Charles