Greetings, I have been trying to optimize some of our queries, and
am having limited success.  Can some Sybase gurus help out here?

I am doing a join-less selection on a pretty large table (about
2 mil. rows.)  It turns out that most of our queries are on only
a few columns in the table; perfect for indexing.  However, the
query optimizer doesn't seem to like the mixture of the likes that
we are using.

If I have two non-clustered indexes on two different columns, each
of which I would like to select from with like statements.  
Unfortunately, this prompts Sybase to table-scan... with poor
performance.

select * from TABLE where LOC1 like 'ABC%' or LOC2 like 'ABC%'

However, if I restructure the query to be a union of two selects,
Sybase uses the indexes.

select * from TABLE where LOC1 like 'ABC%'
union
select * from TABLE where LOC2 like 'ABC%'

This is not the end of the world, but is there any way to force
an index's use.  It just so happens that we have a lot of fields
like LOC1 and LOC2 above and I would prefer some kind of index
that encompassed them all.  Is this possible, or should I have
a non-clustered index for each?

Thank you,

-sean.

: Greetings, I have been trying to optimize some of our queries, and
: am having limited success.  Can some Sybase gurus help out here?

: I am doing a join-less selection on a pretty large table (about
: 2 mil. rows.)  It turns out that most of our queries are on only
: a few columns in the table; perfect for indexing.  However, the
: query optimizer doesn't seem to like the mixture of the likes that
: we are using.

<snip> (see previous message)

For a concrete example, I've made 13 non-clustered indexes on differing
columns and expanded my original query to something quite obnoxious...

I don't exactly want to make every query as mal-formed as this, but I do
want Sybase to use those indexes...  For comparison, the original query
(shown below) takes 430 seconds versus the new 34 seconds...  If you can
think of anything I can try to get back to the original readability of
the original and keep some of the speedup of the new query, I would be
most appreciative!  No need to be exactly right, just point me in the
right direction.  Thanks.

Original:

select * from CNPC1 WHERE ((iof1 like 'N%' or iof1a like 'N%' or iof2 like 'N%'
or iof2a like 'N%' or iof3 like 'N%' or iof3a like 'N%' or iof4 like 'N%' or
iof4a like 'N%') and (loca like 'SLSPMDSS%' or locb like 'SLSPMDSS%' or locc
like 'SLSPMDSS%' or locd like 'SLSPMDSS%' or locz like 'SLSPMDSS%' ))

select * from CNPC1 where iof1 like 'N%' and loca like 'SLSPMDSS%'
union select * from CNPC1 where iof1 like 'N%' and locb like 'SLSPMDSS%'
union select * from CNPC1 where iof1 like 'N%' and locc like 'SLSPMDSS%'
union select * from CNPC1 where iof1 like 'N%' and locd like 'SLSPMDSS%'
union select * from CNPC1 where iof1 like 'N%' and locz like 'SLSPMDSS%'
union select * from CNPC1 where iof2 like 'N%' and loca like 'SLSPMDSS%'
union select * from CNPC1 where iof2 like 'N%' and locb like 'SLSPMDSS%'
union select * from CNPC1 where iof2 like 'N%' and locc like 'SLSPMDSS%'
union select * from CNPC1 where iof2 like 'N%' and locd like 'SLSPMDSS%'
union select * from CNPC1 where iof2 like 'N%' and locz like 'SLSPMDSS%'
union select * from CNPC1 where iof3 like 'N%' and loca like 'SLSPMDSS%'
union select * from CNPC1 where iof3 like 'N%' and locb like 'SLSPMDSS%'
union select * from CNPC1 where iof3 like 'N%' and locc like 'SLSPMDSS%'
union select * from CNPC1 where iof3 like 'N%' and locd like 'SLSPMDSS%'
union select * from CNPC1 where iof3 like 'N%' and locz like 'SLSPMDSS%'
union select * from CNPC1 where iof4 like 'N%' and loca like 'SLSPMDSS%'
union select * from CNPC1 where iof4 like 'N%' and locb like 'SLSPMDSS%'
union select * from CNPC1 where iof4 like 'N%' and locc like 'SLSPMDSS%'
union select * from CNPC1 where iof4 like 'N%' and locd like 'SLSPMDSS%'
union select * from CNPC1 where iof4 like 'N%' and locz like 'SLSPMDSS%'
union select * from CNPC1 where iof1a like 'N%' and loca like 'SLSPMDSS%'
union select * from CNPC1 where iof1a like 'N%' and locb like 'SLSPMDSS%'
union select * from CNPC1 where iof1a like 'N%' and locc like 'SLSPMDSS%'
union select * from CNPC1 where iof1a like 'N%' and locd like 'SLSPMDSS%'
union select * from CNPC1 where iof1a like 'N%' and locz like 'SLSPMDSS%'
union select * from CNPC1 where iof2a like 'N%' and loca like 'SLSPMDSS%'
union select * from CNPC1 where iof2a like 'N%' and locb like 'SLSPMDSS%'
union select * from CNPC1 where iof2a like 'N%' and locc like 'SLSPMDSS%'
union select * from CNPC1 where iof2a like 'N%' and locd like 'SLSPMDSS%'
union select * from CNPC1 where iof2a like 'N%' and locz like 'SLSPMDSS%'
union select * from CNPC1 where iof3a like 'N%' and loca like 'SLSPMDSS%'
union select * from CNPC1 where iof3a like 'N%' and locb like 'SLSPMDSS%'
union select * from CNPC1 where iof3a like 'N%' and locc like 'SLSPMDSS%'
union select * from CNPC1 where iof3a like 'N%' and locd like 'SLSPMDSS%'
union select * from CNPC1 where iof3a like 'N%' and locz like 'SLSPMDSS%'
union select * from CNPC1 where iof4a like 'N%' and loca like 'SLSPMDSS%'
union select * from CNPC1 where iof4a like 'N%' and locb like 'SLSPMDSS%'
union select * from CNPC1 where iof4a like 'N%' and locc like 'SLSPMDSS%'
union select * from CNPC1 where iof4a like 'N%' and locd like 'SLSPMDSS%'
union select * from CNPC1 where iof4a like 'N%' and locz like 'SLSPMDSS%'

(aaarrrrgh!)

-sean.

This is certainly not desirable.  What you should try is to create
compound indexes on the table i.e. an index that contains both (all) the
columns you use in your select statenent.  This will couse the optimizer
to use a technique called "index covering" which means that the select
statement can be performed without even accessing the table (all
required info can be gotten from the index).

Hope this helps,
Stefan Mahs