Oracle: scan types

• Full Table Scan - Highest cost. This happens, if:
• No index exists.
• There is an index, but
• Without columns that the query is using, or
• Aggregate function is used, AND index allows nulls, or
• Multi-column index, and you're not using the first column in it.
• Resultset expected to be so big, that it is cheaper to read through the table than use the index. (Fewer large IO calls are cheaper than lot of tiny ones.)
• The table is too small. (Smaller than DB_FILE_MULTIBLOCK_READ_COUNT)
• Table parallelism too high. (Small partitions are easier to scan - see previous item)
• Table Access by rowid - Getting rowids through an index.
• An index returns with a set of rowid-s.
• There are columns specified that are not in the index.
• Table Access Sample - When using block sampling.
• Table Access Inmemory - Since 12cR1, on tables using inmemory parameter.
• Index Unique Scan - unique means, at most one rowid exists for a given index entry, so the search can stop after the first match.
• Unique Index exists and can be used, and
• Equality operator being in place.
• Index Range Scan (Descending) - ordered scan of values, even for ranges.
• Index leading columns with <,>,= operators, and
• AND combination of those (at least for leading columns).
• Can be forced by using BETWEEN.
• Descending: Order By has Desc parameter in it.
• Index Full Scan - Uses index order for Order By.
• Referencing an indexed column,
• Order By present, in a notnull column,
• All columns in Order By are indexed,
• Order of columns in Order By match the order of the leading index columns.
• Index Fast Full Scan - Full index scan, not accessing the table.
•  All columns required are in the index(es).
• Index Skip Scan
• Leading column in a composite index is not used,
• Leading column has only a few distinct values,
• Non-leading, referenced column has many distinct values.
• Hash Join (Index Join Scan)
• Hash join of multiple indexes returns all the required columns (without table access),
• Table access is more expensive than an index join.
• Table Access Cluster (Cluster Scan)
• Using an indexed cluster.
• Needs a simple Cluster used by all the tables.
• Table Access Hash (Hash Scan)
• In a hash cluster, rows are grouped into the same block by their hash value.
• Needs a Hash Cluster used by all the tables.

Leading column is the first column in a composite index.

CREATE INDEX ix_thisisanindex ON table42(leadingcolumn, notleading, stillnotleading);