How the Optimizer Determines Index Utilization

Teradata Vantageā„¢ Geospatial Data Types

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1181-162K

The Teradata Optimizer weighs the cost and benefit of using the alternative data paths to execute a query.

If a table has a secondary index, the Optimizer can use the index when executing a query against that table, rather than performing a more costly full table scan. By using the index, the Optimizer can reduce the number of rows that must be considered for predicate qualification, usually resulting in faster access to the necessary data.

Although use of the index can reduce the amount of data that needs to be scanned, there is a cost to using the index due to writing, reading, and manipulating an auxiliary Row ID spool.

The Optimizer uses factors such as the following to judge whether to use an available index:
  • The selectivity of the single-table predicate determines how many rows of the table are selected. It determines the cardinality of the Row ID spool. The Optimizer is more likely to use an index to satisfy a query that uses more selective single-table predicates.

    The accuracy with which the Optimizer can determine the selectivity of the predicate depends on whether statistics have been collected on the geospatial column, and whether the collected statistics reflect the current data demographics.

  • The size of the Row ID Spool row, which depends on the number of columns present in the selectivity list or WHERE-clause. This factor, together with the selectivity, determines the number of IO blocks present in the Row ID spool.
  • The size of the table rows. In general, larger rows make it more cost effective for the Optimizer to use the index.

For more information on how the Teradata Optimizer chooses execution paths for queries, see Teradata Vantageā„¢ SQL Request and Transaction Processing, B035-1142.