8 Teradata Data Access Paths Explained

0
1038
Teradata Data Access Paths

Teradata Data Access PathsThe Teradata Data Access Paths

Access Path
Advantages
Disadvantages
Full Table Scan
Touches each row only once and provides access for any combination of columns in the WHERE condition predicate. A good choice if all rows need to be touched.
It reads each data block and usually requires a spool table as large as the base table
Unique Primary Index (UPI)
It is the cheapest access path and used when the SQL statement contains the primary index columns in the WHERE condition predicate. Only requires one AMP. No spool file is created. Allows to use row hash locking, the most granular locking method available
None
Nonunique Primary Index (NUPI)
Provides efficient access and involves just one AMP. It allows rowhash locking, may or may not require a spool file, depending on thenumber of rows returned.
The NUPI may have bad INSERT performance for a SET tables (duplicate row check).
Unique Secondary Index (USI)
Provides an efficient access path if the request WHERE condition predicates contain the USI columns. Typically involves only two AMPs. No spool table is created
Causes additional overhead for INSERT, UPDATE, MERGE, and DELETE statements
Nonunique Secondary Index(NUSI)
Provides efficient access when the number of distinct values is low, although it requires all AMPS and may create a spool table
Causes overhead for INSERT, UPDATE, MERGE, and DELETE statements. It will not be used by the Optimizer if the number of data blocks accessed is high, as it’s an All-AMP acess and a full table scan often is cheaper
Single-Table Join Index (JI) and Hash Index
Ideal when restricting the index columns to often used columns as it can reduce the number of IOs. Can have a different PI than the base table
Causes overhead for INSERT, UPDATE, MERGE, and DELETE statements
Multitable Join Index (JI)
Eliminates the need to perform joins and aggregates, and may be able to satisfy a query without referencing the base table. It can have a different PI than the base table and can replace an NUSI or a USI
Causes overhead for INSERT, UPDATE, MERGE, and DELETE statements for any of the base tables being part of the Multitable Join Index. Bad choice for tables with a large number of changes.
Sparse join index (JI)
Limits the number of rows being stored compared with a normal join index.  This limits the overhead for INSERT, UPDATE, MERGE, and DELETE statements. If restricted to highly selective column values, it can be used by the Optimizer to use the join index to access less common values
Causes overhead for INSERT, UPDATE, MERGE, and DELETE statements

See also:
The Teradata Join Index Guide – We leave no Questions unanswered!

Our Reader Score
[Total: 6    Average: 5/5]
8 Teradata Data Access Paths Explained written by Roland Wenzlofsky on March 11, 2017 average rating 5/5 - 6 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here