Does tables indexes affect in SQL join query execution time?

Question

i have one stored procedure which include 4-5 table joins operation and that SP i execute on two different database(all tables definition are same) then in DB-1, SP take 20-25 seconds and in DB-2 SP take 1 sec to execute then after i rebuild DB-1 indexes, then SP of DB-1 works quick compare to before.

So any relation between join and table indexes? If yes, then how to solve this kind of issue?

Answer 1

I understand your question to ask whether the use of indexes will affect join performance. The answer is a resounding "Yes".

In general, joining to a table of which only a few rows are needed will use an index (if one exists).

Joining to a table where most of the table is needed will result in a "full-table scan" (Oracle terminology) in which the DBMS will construct a hash of the join key using this as an index to subsequently probe for the right values to use when joining.

See http://en.wikipedia.org/wiki/Join_%28SQL%29#Implementation and the articles it references on Join algorithms for a good overview on these.