8.3 How Best to Access Data in the Database

 < Day Day Up > 



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.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net