procedures and plans

I'm trying to figure out how/when procedures figure out what indexes to use. What happens in the following scenario:

1. I have a table (tbla) with an index (idxa) ; I also have another index on the same table which is fairly similar (idxb); I analyzed the table after the indexes are created
2. I create and compile a stored procedure which when I look at the plan goes against table tbla using index idxa
3. I then drop index idxa

what happens the next time the proc is executed ? I assume since the proc is compiled, that it does not usually create a new plan. Does it automatically recompile ? Does it try to use idxb ? We had a problem like the above and the procedure did not have a new recompile date or become mark invalid....but it also did not use indxb....looked like it was doing a table scan. Does any of the above change depending if the procedure has a variable in the where clause versus being hard coded ? While we are on the subject, how does the cost based optimizer determine which index to use at compile time if the where clause has a variable which could change at run time anyhow ?

fairly verbose question but thanks for any help.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 12 2000 - 09:42:17 CDT