fbpx

Which of them is required?

None of these indices is mandatory. Nevertheless, in most cases, a primary index should be defined.

How many indices can be defined per type?

A table can have a maximum of one primary index, but up to 32 secondary indexes.

How many columns can the indexes consist of?

Both the primary index and the secondary index can consist of up to 64 columns.

What about uniqueness?

Both the primary index and the secondary index can be unique or non-unique. However, the distribution of rows for a non-unique secondary index (NUSI) is different (it is not distributed according to rowhash).

Can the definition of the index be changed?

You can drop a secondary index at any time and create it again with a different definition.
The column set of a primary index cannot be changed. However, you can change it from NUPI to UPI, for example:

ALTER TABLE Customer MODIFY UNIQUE PRIMARY INDEX (>NUPI columns>)

How about overhead for index maintenance?

The primary index deteriorates performance only if the distribution is skewed. No maintenance is needed.

A secondary index deteriorates performance because it is mapped in a sub table, which must be maintained with every UPDATE and INSERT statement. This means that a secondary index should only be created if it is also used!

How is a Teradata Index stored?

The primary index does not need its own data structure since it simply distributes the rows according to a hashing algorithm.
The secondary index is stored in a sub table.

What about Row-level Partitioning?

Row-level partitioning is only available for the primary index.

How are the rows on the AMPs sorted?

The rows are sorted by a rowhash of the primary index of an unpartitioned table. If the table is partitioned, it is sorted by rowhash after the rows have been assigned to the correct partitions.
For a non-unique secondary index, the rows can be sorted by the rowhash or by value (INTEGER datatype only). The unique secondary index can only be sorted by rowhash.

__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

>