Hi folks,
I am having a weird problem in Sybase and being a sybase dba, it makes
me feel worse that I don't know what's causing it. Please help me.

We are using sybase 11.9.2 on linux.

The following query(actual code)

select distinct a.clone_id,0,f.cluster
from clone a,collection b,library c,location d,
sequence e,master_xref f,master_xref g
where a.collection_id = b.collection_id
and a.library_id = c.source_lib_id
and a.clone_id = d.clone_id
and a.clone_id = e.clone_id
and e.accession = f.id
and f.cluster = g.cluster
and f.type="ACCESSION"
and g.type="ACCESSION"
and b.short_collection_type='cDNA'
and b.is_public = 1
and a.active = 1
and a.no_sale = 0
and g.id = @queryParam

works fine and is very fast considering the tables sequence, location
and clone to be atleast 15 million each and the master_xref table
being 25 million. The rest are smaller tables. In the showplan, the
each table uses the best index, the optimizer feels good about.

Now, I need to store the store the output of the above query in a
table(say a temp table).

So I add a insert into tab1 in front of the query and it looks like:-

insert into temp_tab
select distinct a.clone_id,0,f.cluster
from clone a,collection b,library c,location d,
sequence e,master_xref f,master_xref g
where a.collection_id = b.collection_id
and a.library_id = c.source_lib_id
and a.clone_id = d.clone_id
and a.clone_id = e.clone_id
and e.accession = f.id
and f.cluster = g.cluster
and f.type="ACCESSION"
and g.type="ACCESSION"
and b.short_collection_type='cDNA'
and b.is_public = 1
and a.active = 1
and a.no_sale = 0
and g.id = @queryParam
As soon as I do this, the query won't work. The showplan changes too
and I understand the change. But now, the clone table does a table
scan instead of using the index. I have run update statistics on each
of the table involved in the query but the performance has not
improved. I have had problems maintaining complex queries in the past
when working with 11.9.2 with regards to updating statistics all the
time.

Has anyone experienced anything similar??? Please help.

Regards

subhas