Re: Searching across three files

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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
>