Understanding oracle database data access mechanisms

From another question:

Full table scans, index range scans, fast full index scans, nested loops, merge join, hash joins etc. are simply access mechanisms that must be understood by the analyst

Can someone explain each one 开发者_运维知识库and also provide where I can do some further reading on them?

For more on the joins, I would recommend reading some articles from Jonathan Lewis:

• Nested Loop Join
• Hash Join
• Merge Join

For more info on reading Execution Plans and interpreting what they mean I would recommend the book:

Troubleshooting Oracle Performance by Christian Antognini

http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#i21299

Chapter 11

Section 11.3 Overview of Joins

For starters I recommend the relevant section of Oracle Database Concepts.

If you want more detail, you can refer to the Performance Tuning Guide as Robert indicated.

Great questions. I found these articles quite helpful for understanding these specific topics.

Oracle HASH Joins:

Oracle INDEX-MERGE Joins: