Indexing temp tables
Posted on 2003-02-20
create table #Sample as (...)
create index #Sample_SI1 on #Sample (Col1)
from Tab1 T, #Sample S
where T.Col1 = S.Col1
If this is a part of a procedure, can the optimizer take advantage of the index? If you try to force index you will get a message that the index can not be found and that the optimizer will make an alternate decision (obvious because at compile time there is no index). Never the less, it sometime seems as if the optimizer in fact uses the index, because of faster execution (not because of cached data).
1. Are we wrong about this or does Sybase in fact use the index at runtime?
2. What if the index was declared as unique? Will Sybase check against duplicates allthough the index wasn't created when the procedure was compiled?
It seems as if some checks and/or optimizations takes place during runtime - could this explain the first question as well?