Are the indexed columns part of join predicates?
For example, the following join predicate defines the join between the emp and dept tables on the deptno column:
emp.deptno = dept.deptno
If the indexed columns ARE part of join predicates, then: What type of join method is used to implement the join when the query executes?
- Hash / Sort Merge Join: With Hash joins and Sort Merge joins, information from the outer table is not available at join time to enable row look ups on the inner table; rather both tables are accessed separately and then the resultant data is joined. The inner table of a Hash or Sort Merge cannot be probed solely using an index based on the join columns . This is an inherent limitation of the implementation mechanisms used by these join types. Nested Loops joins are different in as much as they allow index lookups on the join columns.
- Nested Loops Join: Nested loop joins work by reading the outer table and then using the information gathered to probe the inner table. This algorithm allows index lookups to occur on the inner table.
Only a Nested loops join can allow index lookups on the inner table that are based solely on the join column(s).
Does the first point mean when we join the emp and dept table(both columns having index) and join method being used is Hash / Sort Merge Join then no index will b used?