A Simpe Join Which Madden Quey Optimiser

[morsi]

Hello,
I'm looking a strange behaviour on my Sybase 12.5 when executing a simple query :

select T.*
from TABLE1 T, TABLE_JOB J
where J.SESSID = 0
and T.ELEMENTID = J.ELEMENTID

TABLE1 is a table of 80.000 rows, have an non-custered index on ELEMENTID
TABLE_JOB is a table of 1 to 100 rows, have non clustered index on SESSID.

Normally, TABLE_JOB must be scanned on first step before using the index of the TABLE1. But, the plan show on first a table scan of TABLE1 when TABLE_JOB have more than 10 rows and the right plan if TABLE_JOB have fewer rows or is empty !

I did :
1 - update statistics of two tables
2 - updating the two tables locks to datarows
3 - deleting indexes of TABLE_JOB

but, the same behaviour keep on.

Who had a similar problem, where the size of a table in a join can disturb the right execution plan ? The server is recenty installed, perhaps i forgot making the right value of a configuration parameter, which one ?

I can't imagine that this mad things are produced by a well-known DBMS like Sybase !! Without solution, I think of working in the future with Oracle and losting my fisrt investment in Sybase !

Thanks et sorry for my bad english.

 

[sybaseguru]

Interesting. Since table_job has < 100 rows then the optimiser will do a table scan of this table. However, try this first

delete statistics table_job
go
update statistics table_job
go

and try your query again and see how it goes

 

[morsi]

Good,
I have been always deleted the statistics' TABLE_JOB.
No result.
But when use set forceplan on, i have the good plan. I can't using set forceplan everywhere im my sql codes : it's size is 500 Ko !!!

 

[sybaseguru]

Ok, Have you tried

select T.*
from TABLE_JOB J, TABLE1 T
WHERE T.ELEMENTID = J.ELEMENTID
AND J.SESSID = 0