I have this update query which I am trying to execute in Sybase and it takes a hell lot of time.

update open_positions
set security_id = p.security_id
from positions op, product p
where p.security_id like op. security_id + '%'
and vendor = 'I'
and inactive_d is null
and op.match='Y'
and op.segment is null

The product table is a massive table and has a non-clustered index on security_id

After I observed the Query plan, I saw the ‘product’ table was getting Table scanned.
Please let me know why the index is not getting used here.

Is it because
1. I am using a like on the join clause?
2. The where clause filter has other columns which don’t have indexes on them?

Please help!
Thanks

It is possible to use an index with like provided the wild card is not in the beginning
So should be OK in your case
You have 3 tables but only join 2
I don't see a join to open_positions

Sorry, my bad. It was a typo.

update open_positions
set security_id = p.security_id
from open_positions op, product p
where p.security_id like op. security_id + '%'
and vendor = 'I'
and inactive_d is null
and op.match='Y'
and op.segment is null

and the fun part is - even if I remove the other filtering conditions in the where clause like (on columns vendor & inactive_d), I still get a Table scan.

..Is Sybase [INTERNALLY] doing something while trying to optimize the query for performance?

Sybase will look at what you've provided then decide if your criteria restrict the search to approx 10-20% of the table and, if not, it will table scan. In this particulr case your doing an operation on the field ( field + "%" ) so it will most likely give up guessing and just do a table scan anyway.

I think you'd do best adding a new field to the table to contain the smaller security_id field and doing an exact match on these fields. It would also be better naming your fields so you don't have two fields called security_id - one that contains the whole field and one that's just a part of the field. If you're only pulling data on one security id then you could add the "%" to the field in your program.

If this doesn't work then I think you should provide the create table and index statements for the tables involved and also give us an idea of what the data security_id field looks like.

Also, optdiag output for that column on that table would help.