Access Paths in Oracle

In this Blog we will discuss about various table access methods in oracle.

Access paths are the methods by which Oracle can get at our data
There are three broad categories of access paths
1.The infamous full scan
2.Index accesses
3.Directed access, by hash or ROWID

Table Full scans

Reads every block in a given segment that was used at some point or other (up to High Water Mark)
Can be most efficient access path for “small” tables
Are a very efficient method to read a lot of data, since it employs multi block read. i.e. can read multiple blocks in a single I/O
Can cause significant physical I/O, particularly on larger tables
Continue Reading

What block size should I use when creating a database? 

To begin with, the answer to that question depends entirely on the answer to another: are you using buffered I/O or not?

If you are buffering your I/O (that is, your file system has its own buffering mechanism, as most do by default), then your Oracle block size should match your file system’s buffer size exactly. For ext3, for example, that would mean 4K blocks by default, whilst for Tru64, it would mean 8K blocks -because those are the buffer sizes for the respective file systems. NTFS, by contrast, is a little unusual for mainstream file systems in that it does not have its own buffer, so reads from and writes to a volume formatted with NTFS are done directly -and at that point you can pretty much pick any Oracle block size you like.
Continue Reading