Understanding oracle database data access mechanisms

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?

score 4 · Accepted Answer · answered Mar 22 '11 at 13:45

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

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

Troubleshooting Oracle Performance by Christian Antognini

score 2 · Answer 2 · answered Mar 22 '11 at 13:31

2

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

Chapter 11

Section 11.3 Overview of Joins

answered Mar 22 '11 at 13:31

Rob van Laarhoven

8,737
2
31
49

score 1 · Answer 3 · answered Mar 22 '11 at 13:52

1

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.

answered Mar 22 '11 at 13:52

Peter G.

14,786
7
57
75

score 0 · Answer 4 · edited May 24 '13 at 21:14

0

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

Oracle HASH Joins:

Oracle INDEX-MERGE Joins:

edited May 24 '13 at 21:14

Anoop

23,044
10
62
76

answered May 24 '13 at 20:42

user2418453

1

it would be best if you can provide relevant links for the question (for full table scans, index range scans, etc). The site that you linked has a lot of content other than Joins, it is difficult navigating all of those and finding out which ones are related to the question. – Subhas May 24 '13 at 21:11

Understanding oracle database data access mechanisms

4 Answers4