DATA ACCESS METHODS IN ORACLE

An access method  or access path shows the way the data will be accesses from each table/index. This can be seen in the operation column tab in EXPLAIN PLAN.

Oracle Supports the below access methods.

1. Full Table SCAN (FTS)
2. Table Access by ROW-ID
3. Index Unique Scan
4. Index Range Scan
5. Index Skip Scan
6. Full Index Scan
7. Fast Full Index Scans
8. Index Joins
9. Hash Access
10. Cluster Access
11. Bit Map Index

Full Table SCAN (FTS) :

Every Row in the table is accessed and those rows are filtered which do not happen to meet selection criteria.Those blocks are read which are below HWM (A block has to be read to know whether it contains data.irrespective of the data residing on the block or not),Hence the number of logical reads depends on the number of blocks, not on the number of rows.

When a FTS  is performed, all the blocks are read sequentially as blocks are adjacent to one another.Based on the init parameter   DB_FILE_MULTIBLOCK_READ_COUNT,larger I/O calls can be made to read more blocks in one shot.
Large amount of data whenever accessed FTS are cheaper than index range scans which is because full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls.

It’s a very Costly operation based on the # of rows present in the table.In Many Cases this is a performance killer and mainly happens if indexes are not properly created or are unusable or large amount of data or parallelism or hints or when optimizer decides to go for FTS for whatever reason.

Table Access by ROWID:

Rowid of a row specifies datafile,data block within the file and location of row within that block.Oracle initially obtains the rowid’s either from a where clause predicate or through an index scan of one or more of table’s indexes.Once these  indexes are obtained,the required rows are selected based on the rowid’sand does a row by row access.

When the Optimizer Uses Rowids:
This is generally the second step after retrieving the rowid from an index. The table access might be required for any columns in the statement not present in the index.Access by rowid does not need to follow every index scan. If the index contains all the columns needed for the statement, then table access by rowid might not occur.

Index Unique Scan:

As the name says, it returns at most, a single rowid only.Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.

Index Range Scan:

It’s a pretty common operation for accessing the data which is Selective. This can be an in-bout or out-bound on both the ends. By algorithm the data is sorted in the ascending order of the indexed columns.

An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.

Index Range scan descending: It’s conceptually the same as an index range scan but used when it’s needed a situation alike of .. “order by descending”.

When the Optimizer Uses Index range scan :

The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:

1.     col1 = :b1
2.     col1 < :b1
3.     col1 > :b1

AND combination of the preceding conditions for leading columns in the index col1 like ‘ASD%’ wild-card searches should not be in a leading position otherwise the condition col1 like ‘%ASD’ does not result in a range scan.
Range scans can use unique or nonunique indexes. Range scans avoid sorting when index columns constitute the ORDER BY/GROUP BY clause.

Index Skip Scan:  

As we know ..Often, scanning index blocks is faster than scanning table data blocks.

Normally,in order  for an index to be used,the prefix of the index key(leading rdge of the index) would be referenced in the query.However, if all the other columns in an index are referenced in the query except the first (leading one)…. Oracle prefers index skip scan,i.e.. to skip the first column of the index and use the rest of it.

Simply put, Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped and the number of logical subindexes is determined by the number of distinct values in the initial column.

This can be advantageous if there are few distinct valuesin the leading column of the concatenated index and many distinct values in the non-leading key of the index.

Full Scans:
A full scan is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. A full scan is also available when there is no predicate, if both the following conditions are met:

1.     All of the columns in the table referenced in the query are included in the index.
2.     At least one of the index columns is not null.

A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. It reads the blocks singly.

Full Index Scan:

A full index scan does not read every block in the index structure,contrary to the name suggests. An index full scan processes all of the leaf blocks of an index,but only enough of the branch blocks to find the first leaf block.It is used when all of the columns necessary to satisfy the statement are inindex and it is cheaper than scanning than table.

It uses single block i/0 and may be used in any of the following situations.

1. An order by clause has all of the index columns in it and the order is the same as in the index.(This can also contain a subset of the columns in the index.
2. The query requires a sort-merge join and all the columns referenced in the query are in the index.
3. order of the columns referenced in the query matches the order of the leading index columns.
4. A group by clause is present in the query and the columns in the group by clause are present in the index.

Fast Full Index Scans

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

Index Joins:

It’s a join of several indexes on the same tablethat collectively contain all of the columns that are referenced in the query from that of the table. Whenevr Index Join is used no table access is required as all the relevant columns are retrieved from the joined indexes.

This can never be used to eliminate a sort operation.

Hash Access:

A hash scan is used to locate rows in a hash cluster, based on a hash value. In a hash cluster, all rows with the same hash value are stored in the same data block. To perform a hash scan, Oracle first obtains the hash value by applying a hash function to a cluster key value specified by the statement. Oracle then scans the data blocks containing rows with that hash value.

Cluster Access:
A cluster scan is used to retrieve, from a table stored in an indexed cluster, all rows that have the same cluster key value. In an indexed cluster, all rows with the same cluster key value are stored in the same data block. To perform a cluster scan, Oracle first obtains the rowid of one of the selected rows by scanning the cluster index. Oracle then locates the rows based on this rowid.