17.05 - How the Optimizer Determines Index Utilization - Teradata Database

Teradata Vantageā„¢ - Geospatial Data Types

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1181-170K

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.