dkeith2 wrote:

> Alexandre - Thanks for the advice. I've followed your advice and
> separated Links from Filters, using ANSI SQL Join syntax. I've also
> created Expression Indexes for the table columns that the data in NDC9
> comes from. I've changed my use of UPPER(...) LIKE '%'||UPPER(...)...
> to CONTAINING (case insensitive search) which can't be indexed but
> seems to be a little bit faster.
>
> Sometimes it seems like these are the kinds of issues that the
> optimizer should be sorting out, kind of like SQL Server does... guess
> I just got spoiled.
>

Don't know how SQL server would use an index for like '%Something%'...

> In testing following your advice I also tested the WHERE clause to use
> LIKE :var1||'%', but it didn't cause the optimizer to choose the index.
>

Well... I did not adviced to use

LIKE :var1||'%'


I told you to use

LIKE 'constant value'||'%'

or just

LIKE 'constant value%'


The optimizer has no way to predict if the value passed to :var1 would contain a wildcard on the first letter, so it could not use an index.

you could try
STARTS WITH :var1

no need for the wild card

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br