indexes on temp tables inside procs - are they used?

mike_bike_kite · #1 (**permalink**) 12-07-07, 07:22

I always remember sybase would never use an index created on a temp table within a stored procedure. This seems to have changed recently but the online sybase docs still state that these indexes won' be used. I'm a bit confused. Has it changed recently? are the online sybase docs wrong? or are there certain conditions to be met before these indexes are used?

Mike

trvishi · #2 (**permalink**) 12-11-07, 12:36

I dont think it does. You can check it with the showplan. Also, try using two procs. creation in one and usage in another.

trvishi · #3 (**permalink**) 12-11-07, 12:37

Also, you can use dbcc traceon(302), 307 and 310 to get indepth optimiser decisions.

mike_bike_kite · #4 (**permalink**) 12-11-07, 13:01

The index is definitely being used - it says it is in the showplan and the performance indicates it's being used. My issue is simply that the docs clearly state that these indexes are not used and this was certainly the case before. I just wondered what had happened and why sybase weren't advertising the fact that these indexes are now used. Just seems odd.

Mike

trvishi · #5 (**permalink**) 12-11-07, 13:21

Check out the following solved case. Hope you have a support id.

http://search.sybase.com/kbx/solvedc...umber=11092625

Since I cant paste the content as it may be against copyright laws, basically it says indexes can be picked but its not guaranteed and its better to separate creation / usage and hence the docs wont be updated.

Now, it will be interesting to know in what cases that is infact true.