2014-10-14

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);

No comments :

Post a Comment