Topic: The index will not be involved - I ask to help with reason search
Already very much for a long time did not deal with a DBMS, probably forgot any basic thing, I ask to prompt.
There is a request of such type:
select flt.id, bt_child.value1, bt_child.value2, bt.fieldbt
from BigTable bt//the table approximately on 3-4 million records with clustered index on id
inner join #SmallTable flt//the temporary table approximately on 10-20 records,
on flt.id = bt.id_contract//the field id_contract tables bt - is indexed
inner join BigTable_Child bt_child//the table approximately on 300 million records with clustered index on id
on bt_child.id_subcontract = bt.id//the field id_subcontract tables bt - is indexed
where bt_child.stringfield in (' string1 ', ' string2 ')//the field stringfield - is not indexed.
The request is fulfilled long (tens minutes).
The request plan shows that that the index across the field bt_child.id_subcontract is not involved, instead of it clustered index BigTable_Child.id is used, and this operation gives 95 % of cost request. Recommends index creation across the field bt_child.stringfield.
But it it would not would be desirable to do, as for the table insertion time is critical.
I thought that an index across the field bt_child.id_subcontract will be enough. What did I do not consider? In what there can be a problem?