Oracle High Performance Tuning for 9i and 10g

There are different ways of accessing data. Some are better than others depending on circumstances. Accessing a table through an index is usually but not always faster than accessing a table directly. Accessing a table directly will always result in a full table scan. A full table scan reads an entire table reading all the blocks in the table. This is no problem if the table is small. If a table is large reading the entire table can be a serious performance issue. There are four basic methods of accessing data:

• Full Table Scan.   Reading an entire table.

• Full Index Scan.   Reading an entire index and accessing table rows using ROWID pointers found in the index.

• Index Range Scan.   Traverses a subset of an index to search for a single value or a group of values across a range of index values. Table rows are accessed using ROWID pointers found in the index.

• Index Unique Scan or Hit.   An exact hit performs a minimal number of block reads on an index, usually three at the most, rapidly accessing a single row in the index. A ROWID pointer in the index is used to access a single row in a table.

Index unique scans are the fastest way to find data in an Oracle database. An index unique scan is always used when finding a single, uniquely identified row, using either the primary key or a unique alternate key. Range scans are a little slower since they potentially read more blocks in the index and pass more ROWID pointers to the table. A full index scan is faster than a full table scan because an index contains fewer columns than the table and thus occupies less physical space. Also an index, being fewer columns in size, is often a compressed form of a table and thus more index rows fit into a block than table blocks. Thus more indexes can be scanned in a single block read.