1. Introduction
  2. Query Optimization
  3. Scan Methods

Scan Methods

The optimizer can select from multiple types of scan methods.

The most common scan methods are:

  • Full table scan: A full table scan examines every row in a table. Because it is typically the least efficient way to evaluate a query predicate, a full scan is only used when no other method is available.

  • Rowid lookup: A rowid is an address (a unique ID) of a physical tuple in a table to each row stored in a table. A rowid lookup is applicable if, for example, an application has previously selected a rowid and then uses a WHERE ROWID= clause to fetch that same row. Rowid lookups are faster than index lookups.

    However, since TimesTen Scaleout may be configured to have multiple copies, then each copy would have a different ROWID. Since ROWID is the identifier of a specific copy of a row, if that copy is not available, you cannot access the row by ROWID. In this case, you should access the row by primary key.

    See Understanding ROWID in Data Distribution in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

  • Range index scan (on either a permanent or temporary index): A range index scan uses a range index to access a table. Such a scan is applicable to exact match predicates such as t1.a=2 or to range predicates such as t1.a>2 and t1.a<10 as long as the column used in the predicate has a range index defined over it. If a range index is defined over multiple columns, it can be used for multiple column predicates. For example, the predicates t1.b=100 and t1.c>'ABC' result in a range index scan if a range index is defined over columns t1.b and t1.c. The index can be used if it is defined over more columns. For example, if a range index is defined over t1.b, t1.c and t1.d, the optimizer uses the index prefix over columns b and c and returns all the values for column d that match the stated predicate over columns b and c.

  • Hash index lookup (on either a permanent or temporary index): A hash index lookup uses a hash index to find rows with an exact match on one or more columns. Such lookups are applicable for equality searches over one or more specified columns.

TimesTen performs fast exact matches through hash indexes and rowid lookups. They perform range matches through range indexes. Optimizer hints can be used to allow or disallow the optimizer from considering certain scan methods when choosing a query plan.