fbpx

Teradata Tutorial – Indexing

By DWH Pro Admin

February 6, 2020


The Teradata Tutorial to support the selection of indexes

How much space does the Teradata index occupy?

An index can take up surprisingly much space, especially if the Teradata system does not automatically apply Block Level Compression to the index. A NUSI can then take up many times the base table’s space because multivalue compression is not applied to a NUSI!

On what kind of table is the index needed?

Depending on the type of table, different considerations are necessary regarding how the index should be used.
For example, a USI can be used on a SET table to prevent the duplicate row check, making no sense on a MULTISET table.
If it is a staging table, it is necessary to consider the extent to which the index is compatible with the loading tool. For example, when using the Multiload, there may not be a USI on the target table, but there may be a NUSI.

How many rows are in the table?

The size of the table influences the space needed for the index.

Is Fallback Protection activated?

Depending on the type of index, it is also Fallback Protected. This should be taken into account when calculating the required space. It also means that each index row for DML statements must be processed twice.

How often are INSERT, UPDATE, or DELETE statements executed?

For each DML statement, the index rows must be maintained. This doubles the number of IOs.

Are strategic or tactical queries performed?

Which type of index is used and how it depends on the workload. For example, a USI is best suited for tactical workloads. Row-level partitioning is again ideal for strategic queries in which ranges of data (e.g., the days of a year) are processed.

Which columns are used in WHERE conditions?

Since the advantage of indexes is mostly the direct access via hashing, it is important to consider which columns are particularly suitable for this purpose, since they are often used in WHERE conditions.

Should I use a Teradata Multi-column or single-column index?

The more columns a secondary index contains, the more specific it is, and the fewer queries can use it. On the other hand, a multi-column index can better identify correlations between columns.

What are the maximum rows and typical rows per value?

These ratios help to determine the selectivity of the index.

How many distinct values do the index columns contain?

This key figure helps estimate, for example, in NUSI, whether the optimizer will use the index.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

DWH Pro Admin

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>