> No.1 Use the CONTAINING clause instead of LIKE ('% %') ?

Neither CONTAINING nor LIKE ('% %') can use indexes.

> No.2 In my program convert my search terms to uppercase and leave
> out one UPPER
> eg. UPPER(W.WPName) CONTAINING ('OEDER')

Ditto.

> You said : " W.CityVBSQ = 200 /* will use an index but no good
> if selectivity is bad */"
> This is the index on the foreign key how do I see if it is good or
> bad ?

Simply check how large a porportion of the records have CityVBSQ =
200. If it is this value that 1/3 of the records have (26867 out of
73205), then the index is no good (I don't know the exact treshold).
If there are lots of updates/deletes to this table, then it might also
be advantageous to add the PK to the end of the index (duplicates make
these operations slower).

> You said for WPName and WPDescription indexes will not help. Would
> indexes actually help if I changed the search criteria to:
>
> UPPER(W.WPName) LIKE ('OEDER%')
> or
> UPPER(W.WPName) LIKE ('% OEDER%')

If you create a field that is the uppercase equivalent of WPName, then
an index can be useful for UPPER_WPName LIKE ('OEDER%'). Note that it
will still be useless for UPPER_WPName LIKE (:ParameterName), since at
prepare time the optimizer cannot know whether :Parametername will
start with a constant or '%'. That's why I prefer using UPPER_WPName
STARTING 'OEDER'. Possibly UPPER(W.WpName) could use an index if you
use a case insensitive collation, but I've never used a case
insensitive collation myself.

HTH,
Set