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 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.