I have a query like this:
select * from table1,table2 where table2.table1id = table1.id;
table2.table1id is a foreign key that points to a corresponding entry in table1. table1 and table2 have primary keys, both called id.
table2 is also a secondary index, called myindex. Here is my problem. During tests,
Microsoft SQL Server wants to use the primary key of table2 (id) and is scanning the table1 table for the matching id to internally perform the join, instead of using the “table1id” index directly.
I know I can formulate my query using a hint to tell SQL to use the secondary key directly.
Here are my questions:
Is this necessary or are their ways to set up the database indexes so that hints are unnecessary.
This is currently written in straight SQL embedded in my Java program. If I move to hibernate, does hibernate automatically generate the hints for me?