hi,

I am presently working on Sybase ASE 15.5 64 bits engine
my operating system is windows vista 64 bit.

I have loaded the database of nearly 100 GB on sybase.

the optimization goal is set to the allrows_dss.

I have a problem that the optimizer is always using the table scan it never uses the clustered index scan.

I have checked all the statistics ,indexes are present in my database. also the indexes are clustered.

even if the i run the query.

select count(*) from A
where A.b <= 10


{where A is a very large table containing more than 200million rows.}

in the above query hardly 1% of totals rows comes as output, Sybase does table scan for it. I have checked for many queries Sybase never used the clustered index scan. while the same queries I used with MS-SQL where it always used clustered index scan.

I didn't understand what the problem is why sybase is not using the clustered index scan with even very low selectivity.

Is there some configuration changes have to make to allow sybase also use index scan.

please provide me the solution of this problem.

thank you.

1.

First take a look if the db-table is fragmented or not :
Use function derived_stat for this to determine if your index/data is fragmented or not.

select derived_stat("<table_name>", "<clus_indexname>", "dpcr")
Value between 0 and 1. Nearby 1 not fragmented. Near 0 then fragmented.
If fragmented then do rebuild the index. ( drop and then create ).
Beware, lots of, takes time and resources to rebuild a clustered index having a table that 'big'.

2. Force the index to be used...

select count(*)
from A ( index <indexname> )
where b <= 10

Better to not use index-forcing.. This I personnally find a very cheap method.

For more methods and for further reading about these methods I will ask
you to read the Sybase ASE performance and tuning manuals which are available at the Sybase website(s)

gr,

Jan

can you supply the create index statements?
and preferably the create table statement?
Mike