fbpx

Recently somebody asked me how the Optimizer employs multi-column statistics.

Here are the most important facts:

The Optimizer uses multi-column statistics if the query’s WHERE condition references all columns:

COLLECT STATISTICS ON DB.THE_TABLE COLUMN (first_column,second_column);
EXPLAIN
SELECT *  FROM DB.THE_TABLE WHERE first_column =’A’  AND second_column = ‘B’;

3) We do an all-AMPs RETRIEVE step from DB.THE_TABLE by way of
an all-rows scan with a condition of (“(DB.THE_TABLE.second_column =
‘B ‘) AND (DB.THE_TABLE.first_column = ‘A ‘)”) into Spool 1
(group_amps), which is built locally on the AMPs.  The size of
Spool 1 is estimated with low confidence to be 669,939 rows (
1,426,300,131 bytes).  The estimated time for this step is 9.58
seconds.

The above example relates to Teradata 13.10. The query was executed without collected Primary Index statistics. The Optimizer’s confidence is “low.” You can increase it to “high” by collecting Primary Index statistics. Primary index statistics give the Optimizer information about the table cardinality.

Teradata 14.10 wouldn’t need Primary Index statistics: Summary statistics hold the information about table cardinalities. They are available as soon as you collect any statistics.
A neat trick for Teradata 14.10 to add summary statistics without collecting statistics: Just execute the below statement, and you will achieve “high confidence.” For example:

HELP STATS DB.THE_TABLE; — Adds summary statistics for free…
Changing the statistic columns’ order doesn’t have any impact, neither on Teradata 13.10 nor Teradata 14.10.  The below example delivers the same result as the previous one:

DROP STATISTICS ON DB.THE_TABLE;
COLLECT STATISTICS ON DB.THE_TABLE COLUMN (second_column,first_column);

EXPLAIN
SELECT *  FROM DB.THE_TABLE WHERE first_column =’A’  AND second_column = ‘B’;

3) We do an all-AMPs RETRIEVE step from DB.THE_TABLE by way of
an all-rows scan with a condition of (“(DB.THE_TABLE.second_column =
‘B ‘) AND (DB.THE_TABLE.first_column = ‘A ‘)”) into Spool 1
(group_amps), which is built locally on the AMPs.  The size of
Spool 1 is estimated with low confidence to be 669,939 rows (
1,426,300,131 bytes).  The estimated time for this step is 9.58
seconds.

Can the Optimizer use multi-column statistics if a query only references some of the WHERE condition’s columns?

In Teradata 13.10, the Optimizer only can use a multi-column statistic if the query references all WHERE condition columns.

In our example below, the Optimizer’s heuristics will assume that 10% of the table rows will be retrieved:

COLLECT STATS ON DB.The_Table COLUMN (first_column,second_column);

EXPLAIN SELECT * FROM DB.The_Table WHERE first_column = ‘A’;

3) We do an all-AMPs RETRIEVE step from DB.The_Table by way of
an all-rows scan with a condition of (“DB.The_Table.first_column =
‘A ‘”) into Spool 1 (group_amps), which is built locally on the
AMPs.  The size of Spool 1 is estimated with no confidence to be
669,939 rows (1,426,300,131 bytes).  The estimated time for this
step is 9.58 seconds.

In Teradata 14.10, significant improvements are helping the Optimizer to make better decisions.  It uses multi-column statistics if some of the columns are referenced. But only if these columns are at the begin of the definition of the statistics:

COLLECT STATS ON DB.The_Table COLUMN (first_column,second_column);

EXPLAIN SELECT * FROM DB.The_Table WHERE first_column = ‘A’;

3) We do an all-AMPs RETRIEVE step from DB.The_Table by way of
an all-rows scan with a condition of (“DB.The_Table.first_column =
‘A ‘”) into Spool 1 (group_amps), which is built locally on the
AMPs.  The size of Spool 1 is estimated with low confidence to be
309,034 rows (333,447,686 bytes).  The estimated time for this
step is 0.09 seconds.
Here is an example where the Optimizer can’t use the multi-column statistics: The referenced column is not located at the beginning of the definition of the multi-column statistics:

COLLECT STATS ON DB.THE_TABLE COLUMN (second_column,first_column);

EXPLAIN SELECT * FROM DB.THE_TABLE WHERE first_column = ‘A’;

3) We do an all-AMPs RETRIEVE step from DB.THE_TABLE by way of
an all-rows scan with a condition of (“DB.THE_TABLE.first_column =
‘A ‘”) into Spool 1 (group_amps), which is built locally on the
AMPs.  The size of Spool 1 is estimated with no confidence to be
61,853 rows (66,739,387 bytes).  The estimated time for this step
is 0.05 seconds.

I hope this post clarified the usage of multi-column statistics. As always, feel free to ask if something is still unclear.

__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

Roland Wenzlofsky

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.

  • excellent explanation. much helpful

  • I see your point.

    The optimizer does know the summary statistics after HELP STATISTICS but SHOW SUMMARY STATISTICS VALUES… does not show them.

    If I issue a COLLECT SUMMARY both optimizer and SHOW SUMMARY STATISTICS VALUES know the statistics.

    Cheers.

    Carlos.

  • It should work. If you run the statement and there are no statistics defined on the table you will receive the error message:

    “HELP failed. 3624: There are no statistics defined for the table”

    Nevertheless, silently the summary statistics have been refreshed. You can prove this by explaining the SELECT * FROM TABLE before and after the HELP STATS statement.

    • Nope.

      If I run a Collect Statistics I get Summary Statistics too, but if I do some INSERTS and execute HELP STATISTICS (statistics presumed refreshed, as you say) and then SHOW SUMMARY STATISTICS VALUES it still shows the row count that existed BEFORE the INSERT was run. Then If I run COLLECT SUMMARY STATISTICS and then again SHOW SUMMARY STATISTICS VALUES it shows the real row count with the new rows inserted.

      This behavior happens in my TD 14.10.

      Cheers.

      • Strange. I tried it again and it works. If you run below SQL, what version/release to you have?

        SELECT * FROM DBC.DBCINFO

        VERSION 14.10.05.05
        RELEASE 14.10.05.05

  • >>”HELP STATS DB.THE_TABLE; — Adds summary statistics for free…”

    Are you sure about that? It doesn’t work this way in my TD 14.10.

    “COLLECT SUMMARY STATISTICS ON ” is what I use.

    Cheers.

    Carlos.

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

    You might also like

    >