Tag Archive

Tag Archives for " collect statistics "
4

What are the Goals of SQL Tuning?

The goal of Teradata SQL tuning is to cut resource usage. Two measures we have to attend:

  • Disk IOs
  • CPU seconds

These are absolute values. They are not influenced by any concurrent system activity and stay stable for a particular execution plan.

Don’t use execution times as your optimization target. Many irrelevant factors will affect run times:

Session blocking, workload delays, heavy concurrent workload, etc.

The most expensive task for any RDBMS is to move data from the mass storage devices to memory.

Many of the techniques & ideas described below reduce the number of transferred data blocks (from disk to memory and vice versa). Some of them help to cut CPU consumption.

9 Teradata Tuning Tips to increase performance by at least 50%

1. Ensure Completeness and Correctness of Teradata Statistics

The most important optimization task is to aid the Teradata Optimizer with complete and correct statistics.

We have to pay particular attention to 3 basic situations and always collect full statistics:

  1. Non-indexed columns used in predicates:

    Missing statistics force the Optimizer to do a very inaccurate heuristic estimation.
  2. Skewed indexed columns:

    Random AMP sampling on skewed index results in wrong estimates and execution plans.
  3. Small tables:

    Estimations for small tables suck when there are fewer table rows than AMPs.  In this case,  most AMPs will not contain rows. A random-AMP sample taken from an AMP without or just a few rows will give a wrong estimation.

By following these three rules, many problems related to missing statistics will vanish.

The goal of our Teradata SQL tuning activities is to find the right balance between good query plans and the time needed to make sure useful statistical information.

Discovering  Missing Statistics

The most simple way to discover missing statistics is by turning on diagnostics:

DIAGNOSTIC HELPSTATS ON FOR SESSION;

The above statement adds statistics recommendation  at the end of each explained statement:

EXPLAIN
SELECT * FROM  WHERE Column = 'value';

Test each recommendation separately. Don’t add all recommendations at once, but wisely chose the ones which improve query performance.

Ensure that no plan step only has “no confidence”.  Steps with “no confidence” are a sure sign for heuristics on non-indexed columns. Something must avoid at all costs!

Detection of Stale Statistics

The Optimizer is doing an excellent job of detecting stale statistics and extrapolating.

Before release 14.10, Teradata detected table growth by comparing two random AMP samples. One taken during statistics collection, the other during query execution.

Since Teradata Release 14.10, deleted and inserted rows can be tracked (the UDI counts). For extrapolation, the Optimizer will prefer UDI counts over random AMP sample comparison.

Still, the best way to ensure up to date statistics is to collect them often.

If we need to find stale statistics, we have to compare estimations against the real row count. There are two places where we can use this information:

  • The Execution Plan (by placing the EXPLAIN modifier in front of our query)
  • The Statistics Histogram (by executing the SHOW STATISTICS VALUES ON <TABLE> statement)

The statistics histograms give us the same information the Optimizer has. It’s used to create the execution plan:

  • The timestamp of the last statistics collection
  • Collected & Random-AMP Sampling  row counts
  • Inserted and deleted rows since the last statistics collection
  • Maximum column values
  • Minimum column values
  • Biased column values
  • Break up the SQL into smaller pieces (using volatile tables)
  • Get rid of joins on expressions

Why did I say “almost the same information”? Statistics improve each time the Optimizer gets new insight. They can come from earlier join steps, single table predicates, and aggregations.

Furthermore, different methods of extrapolation will adjust estimations shown in the histograms. Furthermore, various methods of extrapolation will correct estimates shown in the histograms.

If you need the estimations after extrapolation you can use the following statement:

SHOW CURRENT STATISTICS VALUES ON <Table>;

Here is a “single table predicate” example. It demonstrates the usage of derived estimations:

SELECT * FROM <Table1> t01 INNER JOIn <Table2> t02 ON t01.Key = t02.Key WHERE t01.Column IN (1,2,3);

The retrieve step estimation for <Table1> is 3 distinct values. This information flows into the joining step. The retrieve step estimation for <Table1> is 3 distinct values. This information flows into the joining step. If the average number of rows per value for both tables is about 1 (unique data), the resulting spool for the join is three rows.  

A straightforward approach to detect stale statistics is this:

  • Decompose the query  into single retrieve and join steps
  • Figure out why step estimations and real numbers don’t match
    Statistics could be outdated, or they could be missing. It might even be that the estimation is a result of the way the optimizer works.
    Here is an example:
SELECT * FROM  WHERE Column = 1;

Value 1 is not available in <Table>As value=1 is not available in <Column> the Optimizer will estimate the result set to be the average rows per value. I guess many of you expect zero rows?

More information is available here: Teradata Statistics Basics

My last piece of advice:

Keep a copy of both the execution plans – before and after the change – to see the impact of your change.

2. The Primary Index Choice

When it comes to Primary Index choice, we must create a fair balance, weighing opposing requirements against each other:

Even data distribution and join performance.

Two tables can only be joined if they have the same primary index. In other words: The rows of both tables need to be on the same AMP.

Design your queries in a way, that the Primary Index is used for joining as much as possible, as this is the cheapest way of joining.

AMP-local joining also is possible if the join condition includes columns that are not in the primary index. But if the join condition is not including all primary index columns, the rows of one or both tables have to be relocated (and maybe sorted).

If you need a different primary index to improve performance, you can use volatile tables or temporary tables. Create them with the same structure and content as the original tables but with the needed primary index. The use of temporary tables is particularly useful if your query consists of many join steps.

3. Teradata Indexing & Partitioning

Indexes give the Optimizer more data access paths. They are improving highly selective retrieve actions. Unfortunately, indexes consume permanent disk space, and they require maintenance when the underlying base table changes.

Recommendation:

If indexes are not used by the Optimizer, and not useful in the PDM design, drop them immediately. They only will waste space and resources.

Unique secondary index (USI) allows for direct row access (like the primary index). The non-unique secondary index (NUSI) requires a full index subtable scan on all AMPs.

The NUSI can be an advantage over the base table access if the index subtable is smaller than the base table. Covering NUSI’s are more useful than non-covering ones:  There are base table lookups needed, and no costs created.

The difference between an index and partitioning is that indexes are sub-tables. Partitioning is another way of structuring the base table. Partitioning allows the Optimizer to limit access to the data blocks of a partition. The advantage of partitioning is that partition elimination always is used. Index usage has preconditions. For example, the NUSI will not be used without statistics collected on the index columns.

Still, there is a significant advantage of indexing: We create the index; we check if it’s used. We drop it if it’s not used. Have you ever partitioned a 200 Terabyte table for test reasons? I guess this is not what we like to do.

Another disadvantage of partitioning comes to my mind:

Join performance worsens if partitions of both tables don’t match or one of the tables is not partitioned.

Whenever working with partitioning, you have to keep the data warehouse architecture in mind. Decide if your solution fits into it. Partitioned tables use different join techniques. If tables have different partitions, this has an adverse impact on join performance!

Conclusion: There is no one-size-fits-all method. You have to check what works best for you!

4. Query Rewriting

Often queries performance improves when the query is rewritten. Here are some ideas:

  • DISTINCT instead of GROUP BY depending on the number of different values
  • UNION ALL instead of UNION (getting rid of a sort step)
  • Splitting a skewed query into two parts. One for handling the skewed values, the other to deal with the rest
  • Adding WHERE conditions to allow for partition elimination
  • Removing of unreferenced columns and expression from the select list (could help to cut joins)
  • Converting outer joins into inner joins

Query rewriting allows improving performance, sometimes even when all other techniques fail.

Query rewriting is a very powerful way to improve performance. Still, it often requires understanding the business logic (“can I replace this left join with an inner join?”).

It is possible to rewrite a query in a purely technical way. Still,  understanding the business logic of a query reveals more tuning opportunities.

See also our article on query rewriting here: Teradata Tuning – Query Rewriting

5. Physical Data Model Design

I was not sure if I should add the physical data model to this list for one reason. Often we can’t do any significant changes in the physical database design. Too many consumers on top of the database (such as reporting tools, data marts) would require a redesign.

Still, if we can improve the physical model, this is one of the most productive changes.

The best advice I can give:

Keep your core model normalized, denormalize in your data marts. Many of you will not agree. I can live with this. My experience is that early denormalization causes bad performance, especially when done without apparent reason. Check all columns of all tables if the information stored cannot be further broken down. Ensure also that they have the same data types and character sets.    Columns which are containing more than one piece of information force the user to use expression joins. Most likely the Optimizer will not use any statistics and the primary index for joining.

Ensure that all primary key columns are defined as NOT NULL. Add default values where appropriate. If you store codes, there is no reason to use UNICODE. It will just waste space.

Apply Multi Value compression on all tables: More rows can fit into each data block. Data blocks are the smallest unit transferred between disk and memory. More rows per data block lead to less disk IOs and better performance.

Please consider that the above advice can only be a loose collection of ideas about how to fix a broken data model.

6. Make Use of Teradata-Specific Features

There are several unique optimization opportunities which you should consider:

  • Using MULTISET tables can decrease Disk IOs
  • Use Multi-Statement-Requests, as this avoids the usage of the transient journal and does block optimization
  • Use CLOB columns instead of VARCHAR if you seldom select the column. Teradata stores CLOBs in sub-tables
  • DELETE instead of DROP tables
  • Use ALTER TABLE instead of INSERT…SELECT into a copy
  • User MERGE INTO instead of INSERT & UPDATE

7. Real-Time Monitoring

Observing a query while it’s running allows detecting the critical steps. Most people use Viewpoint for real-time monitoring. I prefer another tool called dbmon. The author is a guy from Teradata Austria (I hate the slowness of Viewpoint).

Bad SQL performance is either caused by:

  • Skewed steps or
  • Stale and missing statistics. They fool the Optimizer into creating wrong join decisions: Doing product joins instead of merge joins, duplicating instead of rehashing

That’s my way of real-time monitoring:

I wait for a step that is either skewed or in which the estimated input rows to a join don’t make sense. (such as having two spools with millions of rows joined with a product join). I concentrate my optimization on the steps of the previously mentioned type.

If the skew is the issue, I will analyze the join column value skew. If estimations are wrong, I will go over the statistics and make sure that they are up to date.

Issues with statistics can be fixed quickly. Skew issues can be quite stubborn. Query rewriting is always my last option unless I find something foolish and easy to repair.

8. Comparison of Resource Usage

Always measure resource usage before and after the optimization. As I said earlier: query run times are no reliable test!

Here is a SQL query you can use in your daily work to extract appropriate steps from the query log. You have to set a different QUERYBAND for each query version you are running to be able to distinguish them. You need “select” access to “DBC.DBQLOGTBL”.

SET QUERY_BAND = 'Version=1;' FOR SESSION;
SELECT
   AMPCPUTIME,
   (FIRSTRESPTIME-STARTTIME DAY(2) TO SECOND(6)) RUNTIME,
   SPOOLUSAGE/1024**3 AS SPOOL_IN_GB,
   CAST(100-((AMPCPUTIME/(HASHAMP()+1))*100/NULLIFZERO(MAXAMPCPUTIME)) AS INTEGER) AS CPU_SKEW,
   MAXAMPCPUTIME*(HASHAMP()+1) AS CPU_IMPACT,
   AMPCPUTIME*1000/NULLIFZERO(TOTALIOCOUNT) AS LHR
FROM
   DBC.DBQLOGTBL
WHERE
     QUERYBAND = 'Version=1;'

The query will return:

  • The total CPU Usage
  • The Spool Space needed
  • The LHR (ratio between CPU and IO usage)
  • The CPU Skew
  • The Skew Impact on the CPU

The goal is to cut total CPU usage, consumed spool space and skew.

9. Tactical Workload

Tactical workload requires a very particular skillset. The best tuner will fail if he doesn’t recognize that he is dealing with a tactical workload. I have seen complete projects failing because developers ignored this important fact.

The skillset required for the strategic workload is unique.

I strongly recommend to read this post which explains all the details:

Tactical Workload Tuning on Teradata

1

Teradata Statistics Case Study

The Optimizer usually is doing a good job when it comes to the usage of statistics. Nevertheless, sometimes it pays off to take a closer look at the execution plan and the Optimizer’s cardinality estimations.

Since Teradata 14.10 I got used to including the SHOW STATISTICS statement into my considerations, as the output metrics can help a lot to discover statistic problems.

Teradata Statistics – Avoid the Heuristics

The following case study shows how the Optimizer can be convinced (or forced) to use collected statistics instead of applying inaccurate heuristics.

The case study is based on the following two tables:

SHOW TABLE TheDatabase.TableOneDate;

CREATE SET TABLE TheDatabase.TableOneDate
(
OneDate DATE FORMAT ‘YYYY-MM-DD’
)  UNIQUE PRIMARY INDEX ( OneDate );

SELECT COUNT(*) FROM TheDatabase.TableOneDate;
–> 1 Row

SHOW TABLE TheDatabase.TheCalendar

CREATE MULTISET TABLE TheDatabase.TheCalendar
(
CalendarDate DATE FORMAT ‘yyyy-mm-dd’
PRIMARY INDEX ( CalendarDate );

SELECT COUNT(*) FROM TheDatabase.TheCalendar;

–> 36.889 Rows

I removed all columns of both tables, which are not relevant for this example and collected statistics on the remaining columns:

COLLECT STATS ON TheDatabase.TableOneDate COLUMN OneDate;
COLLECT STATS ON TheDatabase.TheCalendar COLUMN CalendarDate;


The following query is used in several hundreds of bad performing reports, and therefore caught my attention (it’s not the same query, but you should get the idea):

Explain SELECT * FROM TheDatabase.TheCalendar
WHERE CalendarDate >=
(
SELECT
OneDate
FROM TheDatabase.TableOneDate
)

1) First, we lock a distinct TheDatabase.”pseudo table”
for read on a RowHash to prevent global deadlock for
TheDatabase.TableOneDate.
2) Next, we lock a distinct TheDatabase.”pseudo table” for
read on a RowHash to prevent global deadlock for
TheDatabase.TheCalendar.
3) We lock TheDatabase.TableOneDate for read, and we
lock TheDatabase.TheCalendar for read.
4) We do an all-AMPs RETRIEVE step from
TheDatabase.TableOneDate by way of an all-rows
scan with no residual conditions into Spool 1 (all_amps), which is
built locally on the AMPs. The size of Spool 1 is estimated with
high confidence to be 1 row (25 bytes). The estimated time for
this step is 0.03 seconds.
5) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
by way of an all-rows scan and send the rows back to the
Dispatcher. The size is estimated with high confidence to be 1
row. The estimated time for this step is 0.01 seconds.
6) We do an all-AMPs RETRIEVE step from
TheDatabase.TheCalendar by way of an all-rows scan with a
condition of (“TheDatabase.TheCalendar.CalendarDate >= :%SSQ20”)
into Spool 2 (group_amps), which is built locally on the AMPs.
The size of Spool 2 is estimated with no confidence to be 12,297
rows (4,943,394 bytes). The estimated time for this step is 0.04
seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 2 are sent back to the user as the result of
statement 1. The total estimated time is 0.08 seconds.

Although above execution plan doesn’t look that bad, there was something I did not like about it: The estimated number of rows in step 6, namely 12,297, was much too little. The result set in reality has 32,482 rows.

The reason is that the Optimizer is not using the available statistics (we collected above), but applies heuristics (with “no confidence”):

12,297 (estimation) / 36,889 (table cardinality) = 33%

You can easily prove this with a similar setup: The optimizer will always estimate the number of rows in return set to be 33% of the total table rows.

Below you can see the statistic histograms for related statistics on CalendarDate:

COLLECT STATISTICS
COLUMN ( CalendarDate )
ON TheDatabase.TheCalendar
VALUES
(
/** SummaryInfo **/
/* NumOfBiasedValues */ 0,
/* NumOfEHIntervals */ 249,
/* NumOfHistoryRecords */ 7,
/* MinVal */ DATE ‘2000-01-02’,
/* MaxVal */ DATE ‘2100-12-31’,
/* ModeVal */ DATE ‘2000-01-02’,
/* HighModeFreq */ 1,
/* NumOfDistinctVals */ 36889,
/* NumOfRows */ 36889,
/** Interval: MaxVal, ModeVal, ModeFreq, LowFreq, OtherVals, OtherRows **/
/* 1 */ DATE ‘2000-10-04’, DATE ‘2000-01-02’, 1, 1, 276, 276,
..
/* 249 */ DATE ‘2100-12-31’, DATE ‘2100-09-08’, 1, 1, 114, 114,

While it seems strange that the optimizer is not using available statistics, I had to accept this fact. I changed the query, replacing the subquery with a date literal (2012-01-26 is the same date returned in the subquery of our previous query):

Explain SELECT * FROM TheDatabase.TheCalendar
WHERE CalendarDate >=
(
SELECT
DATE’2012-01-26′
)

1) First, we lock a distinct TheDatabase.”pseudo table”
for read on a RowHash to prevent global deadlock for
TheDatabase.TheCalendar.
2) Next, we lock TheDatabase.TheCalendar for read.
3) We do an all-AMPs RETRIEVE step from
TheDatabase.TheCalendar by way of an all-rows scan with a
condition of (“TheDatabase.TheCalendar.CalendarDate >= DATE
‘2012-01-26′”) into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with high
confidence to be 32,482 rows (1,006,942 bytes). The estimated
time for this step is 0.03 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.03 seconds.

The new execution plan shows that the Optimizer is using the collected statistics, and correctly estimates the result set rows. Unfortunately, using a literal is not an option for my report optimization task.

To get a better understanding how the Optimizer is designed, I again changed the query, selecting the literal from a “dummy table”:

Explain SELECT * FROM TheDatabase.TheCalendar
WHERE DAY_DATE >=
(
SELECT DATE’2012-01-26′ FROM
(
SELECT 1 AS x
) x
)

1) First, we lock a distinct TheDatabase.”pseudo table”
for read on a RowHash to prevent global deadlock for
TheDatabase.TheCalendar.
2) Next, we lock TheDatabase.TheCalendar for read.
3) We do an INSERT into Spool 2.
4) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
an all-rows scan into Spool 1 (used to materialize view, derived
table, table function or table operator x) (group_amps), which is
built locally on the AMPs. The size of Spool 1 is estimated with
high confidence to be 1 row (22 bytes). The estimated time for
this step is 0.01 seconds.
5) We do a group-AMP RETRIEVE step from Spool 1 (Last Use) by way of
an all-rows scan into Spool 3 (group_amps), which is built locally
on that AMP. The size of Spool 3 is estimated with high
confidence to be 1 row (25 bytes). The estimated time for this
step is 0.01 seconds.
6) We do a group-AMP DISPATCHER RETRIEVE step from Spool 3 (Last Use)
by way of an all-rows scan and send the rows back to the
Dispatcher. The size is estimated with high confidence to be 1
row. The estimated time for this step is 0.01 seconds.
7) We do an all-AMPs RETRIEVE step from
TheDatabase.TheCalendar by way of an all-rows scan with a
condition of (“TheDatabase.TheCalendar.DAY_DATE >= :%SSQ20”)
into Spool 4 (group_amps), which is built locally on the AMPs.
The size of Spool 4 is estimated with no confidence to be 12,297
rows (381,207 bytes). The estimated time for this step is 0.03
seconds.
8) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 4 are sent back to the user as the result of
statement 1.

The execution plan shows that we are back to the heuristics. Not what I want. Actually, by optimizing the “calendar view part” of all my reports I expected an improvement for all of them.

Finally, I came up with the following query, doing a correlated subquery:

Explain SELECT * FROM TheDatabase.TheCalendar t01
WHERE EXISTS
(
SELECT
*
FROM TheDatabase.TableOneDate t02
WHERE t01.DAY_DATE>=OneDate
)

1) First, we lock a distinct TheDatabase.”pseudo table”
for read on a RowHash to prevent global deadlock for
TheDatabase.t01.
2) Next, we lock a distinct TheDatabase.”pseudo table” for
read on a RowHash to prevent global deadlock for
TheDatabase.t02.
3) We lock TheDatabase.t01 for read, and we lock
TheDatabase.t02 for read.
4) We do an all-AMPs SUM step to aggregate from
TheDatabase.t02 by way of an all-rows scan with no
residual conditions. Aggregate Intermediate Results are computed
globally, then placed in Spool 3.
5) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 2 (all_amps), which is duplicated on
all AMPs. The size of Spool 2 is estimated with high confidence
to be 72 rows (1,224 bytes).
6) We do an all-AMPs JOIN step from TheDatabase.t01 by way
of an all-rows scan with no residual conditions, which is joined
to Spool 2 (Last Use) by way of an all-rows scan.
TheDatabase.t01 and Spool 2 are joined using an
inclusion product join, with a join condition of (
“TheDatabase.t01.DAY_DATE >= OneDate”). The
result goes into Spool 1 (group_amps), which is built locally on
the AMPs. The size of Spool 1 is estimated with low confidence to be
32,482 rows (1,006,942 bytes). The estimated time for this
step is 3.77 seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1.

The execution plan changed completely, but this plan allowed the Optimizer to use the statistics. The estimations match exactly the number of rows in the result set.

Although the new execution plan involves a product join, because of the small amount of data, this is not a big deal. More important is:  The estimations are excellent now. As the “calendar view part” is cross joined in each report several times, having the correct estimates is crucial.

I hope this case study motivates you to dive deeper into the optimizer’s details. As mentioned above, especially the new “SHOW STATISTICS” command has a lot of useful information.

3

Faster than Lightning: Teradata SQL Tuning

teradata SQL TuningIt is time to publish another case study,  which impressively demonstrates the terrific impact query rewriting can have on performance.

Our object of study is the query below, which initially ran about 40 minutes.

As usually, I added all missing statistics and ensured that no statistic is stale: Something a SQL tuning specialist always should do first.

Next, I analyzed the code of the SQL statement, which creates a report based on the call details of a telecom company; The total minutes of use (MOU)  for one specific day are selected.

CALL_DATE partitions the call detail (CDR) table, and I expected partition elimination when using the CALL_DATE as a predicate of the WHERE condition.

Indeed, I was able to find an execution step doing partition elimination, but not in the best performing way: A product join to the huge call detail table, enhanced by dynamic partition elimination.

Further analysis revealed that the filtering of the call detail records was done at the end of the execution plan (by the HAVING clause).

By moving the filter on CALL_DATE to the WHERE condition of the driving table, I achieved that the Optimizer applied static partition elimination at the very begin of its execution plan:

This tiny change reduced the runtime of the query to a few seconds; Remember, the original query finished after 40 minutes!

A real Teradata SQL Tuning Home Run

Conclusion:

Always apply filters in data retrieval steps as early as possible in the SQL statement (i.e., on the driving table). Although the Optimizer does a lot of query optimization, it can’t recognize all opportunities.
This is the bad performing query:

SELECT
TOT.PHONE_ID
, TOT.COUNTRY_CD
, TOT.BILLING_ENGINE_CD
, TOT.PRICEPLAN_GROUP_CD
, TOT.RATING_CODE
, TOT.CUSTOMER_ID
, TOT.REPORTING_GROUP_CD1
, TOT.ROAMING_IND
, TOT.CORRECTIONS_IND
, TOT.CALL_DATE
, TOT.BILL_PERIOD_CD
, ‘A6’ AS PROVIDER_CD
, SUM(TOT.MINUTES_OF_USE) AS MINUTES_OF_USE
, TOT.BILLED_IND
, TOT.PREPAID_IND
FROM
(SELECT
RG.PHONE_ID
, RG.COUNTRY_CD
, RG.BILLING_ENGINE_CD
, RG.PRICEPLAN_GROUP_CD
, RG.RATING_CODE
, RG.CUSTOMER_ID
, RG.REPORTING_GROUP_CD1
, RG.ROAMING_IND
, RG.CORRECTIONS_IND
, RG.REPORTING_GROUP_CD2
, RG.CALL_DATE
, RG.BILL_PERIOD_CD
, RG.MINUTES_OF_USE
, RG.BILLED_IND
, RG.PREPAID_IND
FROM
(SELECT
CD.PHONE_ID
, CD.COUNTRY_CD
, CD.BILLING_ENGINE_CD
, CD.PRICEPLAN_GROUP_CD
, CD.RATING_CODE
, CD.CUSTOMER_ID
, CD.REPORTING_GROUP_CD1
, CD.ROAMING_IND
, CD.CORRECTIONS_IND
, CD.REPORTING_GROUP_CD2
, CD.CALL_DATE
, CD.MINUTES_OF_USE
, RG.BILL_PERIOD_CD
, CD.BILLED_IND
, CD.PREPAID_IND
FROM
CALL_DETAILS CD
LEFT JOIN
(SELECT BILL_PERIOD_CD, CALL_DATE FROM REPORTING_GROUP GROUP BY 1,2) RG
ON CD.CALL_DATE = RG.CALL_DATE
WHERE
BILLING_ENGINE_CD IN (‘AMDOCS’)
AND BILLED_IND = ‘N’
AND REPORTING_GROUP_CD2 IS NOT NULL
) RG

LEFT JOIN
REPORTING_GROUP R1
ON RG.CALL_DATE = R1.CALL_DATE
AND RG.REPORTING_GROUP_CD1 = R1.REPORTING_GROUP_CD1
AND RG.BILL_PERIOD_CD = R1.BILL_PERIOD_CD

LEFT JOIN
REPORTING_GROUP R2
ON RG.CALL_DATE = R2.CALL_DATE
AND RG.REPORTING_GROUP_CD2 = R2.REPORTING_GROUP_CD1
AND RG.BILL_PERIOD_CD = R2.BILL_PERIOD_CD
WHERE NOT (R1.REPORTING_GROUP_CD1 IS NULL OR R2.REPORTING_GROUP_CD1 IS NULL)
) TOT
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,14,15
 HAVING TOT.CALL_DATE = DATE ‘2015-05-21’; 

Here the execution plan of the bad performing query:

1) First, we lock CALL_DB.REPORTING_GROUP for access,
and we lock CALL_DB.CALL_DETAILS for access.
2) Next, we do an all-AMPs SUM step to aggregate from
CALL_DB.REPORTING_GROUP by way of an all-rows
scan with no residual conditions , grouping by field1 (
CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD
,CALL_DB.REPORTING_GROUP.CALL_DATE).  Aggregate
Intermediate Results are computed globally, then placed in Spool 3.
The size of Spool 3 is estimated with high confidence to be 12
rows (612 bytes).  The estimated time for this step is 0.02
seconds.

3) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 1 (used to materialize view, derived
table or table function RG) (all_amps) (compressed columns
allowed), which is built locally on the AMPs.  The size of Spool 1
is estimated with high confidence to be 12 rows (444 bytes).  The
estimated time for this step is 0.01 seconds.

4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by
way of an all-rows scan into Spool 8 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs.  Then we
do a SORT to order Spool 8 by the hash code of (
CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD,
CALL_DB.REPORTING_GROUP.CALL_DATE).  The size
of Spool 8 is estimated with high confidence to be 1,080 rows
(31,320 bytes).  The estimated time for this step is 0.01
seconds.

2) We do an all-AMPs RETRIEVE step from
CALL_DB.REPORTING_GROUP by way of an
all-rows scan with no residual conditions locking for access
into Spool 9 (all_amps) (compressed columns allowed), which
is built locally on the AMPs.  Then we do a SORT to order
spool 9 by the hash code of (
CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD,
CALL_DB.REPORTING_GROUP.CALL_DATE).  The size
of Spool 9 is estimated with high confidence to be 4,277 rows
(149,695 bytes).  The estimated time for this step is 0.01
seconds.

5) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of a
RowHash match scan, which is joined to Spool 9 (Last Use) by way
of a RowHash match scan.  Spool 8 and Spool 9 are joined using a
merge join, with a join condition of (“(CALL_DATE = CALL_DATE) AND
(BILL_PERIOD_CD = BILL_PERIOD_CD)”).  The result
goes into Spool 10 (all_amps) (compressed columns allowed), which
is duplicated on all AMPs.  Then we do a SORT to partition by
rowkey.  The size of Spool 10 is estimated with low confidence to
be 384,930 rows (19,631,430 bytes).  The estimated time for this
step is 0.03 seconds.

6) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way
of an all-rows scan, which is joined to
CALL_DB.CALL_DETAILS with a condition of (
“(CALL_DB.CALL_DETAILS.BILLED_IND = ‘N’) AND (CALL_DB.CALL_DETAILS.BILLING_ENGINE_CD = ‘AMDOCS ‘)”).

Spool 10 and CALL_DB.CALL_DETAILS are joined
 using a product join , with a join condition of (
“(CALL_DB.CALL_DETAILS.CALL_DATE = CALL_DATE) AND
((CALL_DB.CALL_DETAILS.CALL_DATE = CALL_DATE) AND
(CALL_DB.CALL_DETAILS.REPORTING_GROUP_CD2 = REPORTING_GROUP_CD1
))”)  enhanced by dynamic partition elimination. 
The inputtable CALL_DB.CALL_DETAILS will not be cached in
memory, but it is eligible for synchronized scanning.  The
result goes into Spool 11 (all_amps) (compressed columns
allowed), which is built locally on the AMPs.  The size of
Spool 11 is estimated with low confidence to be 136,515 rows
(21,159,825 bytes).  The estimated time for this step is 4.15
seconds.

2) We do an all-AMPs RETRIEVE step from
CALL_DB.REPORTING_GROUP by way of an
all-rows scan with no residual conditions locking for access
into Spool 12 (all_amps) (compressed columns allowed), which
is duplicated on all AMPs.  The size of Spool 12 is estimated
with high confidence to be 384,930 rows (13,857,480 bytes).

The estimated time for this step is 0.02 seconds.

7) We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an
all-rows scan, which is joined to Spool 12 (Last Use) by way of an
all-rows scan.  Spool 11 and Spool 12 are joined using a single
partition hash join, with a join condition of (
“(BILL_PERIOD_CD = BILL_PERIOD_CD) AND ((CALL_DATE
= CALL_DATE) AND ((CALL_DATE = CALL_DATE) AND
((BILL_PERIOD_CD = BILL_PERIOD_CD) AND ((CALL_DATE
= CALL_DATE) AND (REPORTING_GROUP_CD1 = REPORTING_GROUP_CD1 )))))”).  The result goes into Spool 7 (all_amps) (compressed columns allowed), which is
built locally on the AMPs.  The size of Spool 7 is estimated with
low confidence to be 136,515 rows (18,702,555 bytes).  The
estimated time for this step is 0.04 seconds.

8) We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by
way of an all-rows scan, grouping by field1 (
CALL_DB.CALL_DETAILS.PHONE_ID
, CALL_DB.CALL_DETAILS.COUNTRY_CD
, CALL_DB.CALL_DETAILS.BILLING_ENGINE_CD
, CALL_DB.CALL_DETAILS.PRICEPLAN_GROUP_CD
, CALL_DB.CALL_DETAILS.RATING_CODE
, CALL_DB.CALL_DETAILS.CUSTOMER_ID
, CALL_DB.CALL_DETAILS.REPORTING_GROUP_CD1
, CALL_DB.CALL_DETAILS.ROAMING_IND
, CALL_DB.CALL_DETAILS.CORRECTIONS_IND
, CALL_DB.CALL_DETAILS.CALL_DATE
, CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD
,’A6′ ,CALL_DB.CALL_DETAILS.BILLED_IND
, CALL_DB.CALL_DETAILS.PREPAID_IND).  Aggregate

Intermediate Results are computed globally, then placed in Spool
The size of Spool 13 is estimated with low confidence to be
102,387 rows (31,637,583 bytes).  The estimated time for this step
is 0.17 seconds.
9) We do an all-AMPs RETRIEVE step from Spool 13 (Last Use) by way of
an all-rows scan with a condition of (“CALL_DATE = DATE
‘2015-05-21′”) into Spool 5 (group_amps), which is built locally
on the AMPs.  The size of Spool 5 is estimated with low confidence
to be 102,387 rows (14,846,115 bytes).  The estimated time for
this step is 0.02 seconds.

10) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.

-> The contents of Spool 5 are sent back to the user as the result of
statement 1.  The total estimated time is 4.44 seconds.

 

This is the tuned query:

EXPLAIN

SELECT
TOT.PHONE_ID
, TOT.COUNTRY_CD
, TOT.BILLING_ENGINE_CD
, TOT.PRICEPLAN_GROUP_CD
, TOT.RATING_CODE
, TOT.CUSTOMER_ID
, TOT.REPORTING_GROUP_CD1
, TOT.ROAMING_IND
, TOT.CORRECTIONS_IND
, TOT.CALL_DATE
, TOT.BILL_PERIOD_CD
, ‘A6’ AS PROVIDER_CD
, SUM(TOT.MINUTES_OF_USE) AS MINUTES_OF_USE
, TOT.BILLED_IND
, TOT.PREPAID_IND

FROM
(SELECT
RG.PHONE_ID
, RG.COUNTRY_CD
, RG.BILLING_ENGINE_CD
, RG.PRICEPLAN_GROUP_CD
, RG.RATING_CODE
, RG.CUSTOMER_ID
, RG.REPORTING_GROUP_CD1
, RG.ROAMING_IND
, RG.CORRECTIONS_IND
, RG.REPORTING_GROUP_CD2
, RG.CALL_DATE
, RG.BILL_PERIOD_CD
, RG.MINUTES_OF_USE
, RG.BILLED_IND
, RG.PREPAID_IND

FROM
(SELECT
CD.PHONE_ID
, CD.COUNTRY_CD
, CD.BILLING_ENGINE_CD
, CD.PRICEPLAN_GROUP_CD
, CD.RATING_CODE
, CD.CUSTOMER_ID
, CD.REPORTING_GROUP_CD1
, CD.ROAMING_IND
, CD.CORRECTIONS_IND
, CD.REPORTING_GROUP_CD2
, CD.CALL_DATE
, CD.MINUTES_OF_USE
, RG.BILL_PERIOD_CD
, CD.BILLED_IND
, CD.PREPAID_IND

FROM
CALL_DETAILS CD
LEFT JOIN
(SELECT BILL_PERIOD_CD, CALL_DATE FROM REPORTING_GROUP GROUP BY 1,2) RG
ON CD.CALL_DATE = RG.CALL_DATE
WHERE
BILLING_ENGINE_CD IN (‘AMDOCS’)
AND BILLED_IND = ‘N’
AND REPORTING_GROUP_CD2 IS NOT NULL

 AND CD.CALL_DATE = DATE ‘2015-05-21’  — The optimized place of the filter

) RG

LEFT JOIN
REPORTING_GROUP R1
ON RG.CALL_DATE = R1.CALL_DATE
AND RG.REPORTING_GROUP_CD1 = R1.REPORTING_GROUP_CD1
AND RG.BILL_PERIOD_CD = R1.BILL_PERIOD_CD
LEFT JOIN
REPORTING_GROUP R2
ON RG.CALL_DATE = R2.CALL_DATE
AND RG.REPORTING_GROUP_CD2 = R2.REPORTING_GROUP_CD1
AND RG.BILL_PERIOD_CD = R2.BILL_PERIOD_CD
WHERE NOT (R1.REPORTING_GROUP_CD1 IS NULL OR R2.REPORTING_GROUP_CD1 IS NULL)

) TOT
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,14,15;

This is the execution plan of the tuned query:

1) First, we lock CALL_DB.REPORTING_GROUP for access,
and we lock CALL_DB.CALL_DETAILS for access.
2) Next, we do an all-AMPs SUM step to aggregate from a single
partition of CALL_DB.REPORTING_GROUP with a
condition of (“CALL_DB.REPORTING_GROUP.CALL_DATE =
DATE ‘2015-05-21′”) with a residual condition of (
“CALL_DB.REPORTING_GROUP.CALL_DATE = DATE
‘2015-05-21′”) , grouping by field1 (
CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD
,CALL_DB.REPORTING_GROUP.CALL_DATE).  Aggregate
Intermediate Results are computed globally, then placed in Spool 3.

The size of Spool 3 is estimated with low confidence to be 4 rows
(204 bytes).  The estimated time for this step is 0.01 seconds.
3) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 1 (used to materialize view, derived
table or table function RG) (all_amps) (compressed columns
allowed), which is built locally on the AMPs.  The size of Spool 1
is estimated with low confidence to be 4 rows (148 bytes).  The
estimated time for this step is 0.01 seconds.

4) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by
way of an all-rows scan with a condition of (“RG.CALL_DATE =
DATE ‘2015-05-21′”) into Spool 8 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs.  Then we
do a SORT to order Spool 8 by the hash code of (
CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD).

The size of Spool 8 is estimated with low confidence to be
360 rows (10,440 bytes).  The estimated time for this step is
0.01 seconds.

2) We do an all-AMPs RETRIEVE step  from a single partition  of
CALL_DB.REPORTING_GROUP with a condition of
(“CALL_DB.REPORTING_GROUP.CALL_DATE = DATE
‘2015-05-21′”) with a residual condition of (
“CALL_DB.REPORTING_GROUP.CALL_DATE = DATE
‘2015-05-21′”) locking for access into Spool 9 (all_amps)
(compressed columns allowed), which is built locally on the
AMPs.  Then we do a SORT to order Spool 9 by the hash code of(
CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD).

The size of Spool 9 is estimated with high confidence to be
1,591 rows (55,685 bytes).  The estimated time for this step
is 0.00 seconds.

3) We do an all-AMPs RETRIEVE step  from a single partition  of
CALL_DB.REPORTING_GROUP with a condition of
(“CALL_DB.REPORTING_GROUP.CALL_DATE = DATE
‘2015-05-21′”) with a residual condition of (
“CALL_DB.REPORTING_GROUP.CALL_DATE = DATE
‘2015-05-21′”) locking for access into Spool 10 (all_amps)
(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 10 is estimated with high confidence to be
143,190 rows (5,011,650 bytes).  The estimated time for this
step is 0.01 seconds.

4) We do an all-AMPs RETRIEVE step  from a single partition  of
CALL_DB.CALL_DETAILS with a condition of (
“CALL_DB.CALL_DETAILS.CALL_DATE = DATE
‘2015-05-21′”) with a residual condition of (“(NOT(CALL_DB.CALL_DETAILS.REPORTING_GROUP_CD2 IS NULL ))
AND ((CALL_DB.CALL_DETAILS.CALL_DATE = DATE
‘2015-05-21’) AND ((CALL_DB.CALL_DETAILS.BILLED_IND = ‘N’) AND
(CALL_DB.CALL_DETAILS.BILLING_ENGINE_CD = ‘AMDOCS
‘)))”) into Spool 11 (all_amps) (compressed columns allowed),
which is built locally on the AMPs.  The size of Spool 11 is
estimated with low confidence to be 212,693 rows (27,224,704
bytes).  The estimated time for this step is 0.06 seconds.

5) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of a
RowHash match scan, which is joined to Spool 9 (Last Use) by way
of a RowHash match scan.  Spool 8 and Spool 9 are joined using a
merge join, with a join condition of (“(BILL_PERIOD_CD =
BILL_PERIOD_CD) AND (CALL_DATE = CALL_DATE)”).  The result
goes into Spool 12 (all_amps) (compressed columns allowed), which
is duplicated on all AMPs.  The size of Spool 12 is estimated with
low confidence to be 143,190 rows (7,302,690 bytes).  The
estimated time for this step is 0.02 seconds.

6) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an
all-rows scan, which is joined to Spool 11 (Last Use) by way of an
all-rows scan.  Spool 10 and Spool 11 are joined using a single
partition hash join, with a join condition of (“(REPORTING_GROUP_CD1 =
REPORTING_GROUP_CD1) AND (CALL_DATE = CALL_DATE)”).  The result goes into
Spool 13 (all_amps) (compressed columns allowed), which is built
locally on the AMPs.  The size of Spool 13 is estimated with low
confidence to be 654,535 rows (94,907,575 bytes).  The estimated
time for this step is 0.11 seconds.

7) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an
all-rows scan, which is joined to Spool 13 (Last Use) by way of an
all-rows scan.  Spool 12 and Spool 13 are joined using a single
partition hash join, with a join condition of (“(REPORTING_GROUP_CD2 =
REPORTING_GROUP_CD1) AND ((CALL_DATE = CALL_DATE) AND ((CALL_DATE = CALL_DATE)AND ((BILL_PERIOD_CD = BILL_PERIOD_CD) AND
((CALL_DATE = CALL_DATE) AND ((CALL_DATE = CALL_DATE) AND
(BILL_PERIOD_CD = BILL_PERIOD_CD ))))))”).  The
result goes into Spool 7 (all_amps) (compressed columns allowed),
which is built locally on the AMPs.  The size of Spool 7 is
estimated with low confidence to be 473,348 rows (64,848,676
bytes).  The estimated time for this step is 0.10 seconds.

8) We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by
way of an all-rows scan , grouping by field1 (
CALL_DB.CALL_DETAILS.PHONE_ID,CALL_DB.CALL_DETAILS.COUNTRY_CD
,CALL_DB.CALL_DETAILS.BILLING_ENGINE_CD
,CALL_DB.CALL_DETAILS.PRICEPLAN_GROUP_CD
,CALL_DB.CALL_DETAILS.RATING_CODE
,CALL_DB.CALL_DETAILS.CUSTOMER_ID
,CALL_DB.CALL_DETAILS.REPORTING_GROUP_CD1
,CALL_DB.CALL_DETAILS.ROAMING_IND
,CALL_DB.CALL_DETAILS.CORRECTIONS_IND
,CALL_DB.CALL_DETAILS.CALL_DATE
,CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD
,’TOT’ ,CALL_DB.CALL_DETAILS.BILLED_IND
,CALL_DB.CALL_DETAILS.PREPAID_IND).  Aggregate
Intermediate Results are computed globally, then placed in Spool
The size of Spool 14 is estimated with no confidence to be
355,011 rows (109,698,399 bytes).  The estimated time for this
step is 0.32 seconds.

9) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of
an all-rows scan into Spool 5 (group_amps), which is built locally
on the AMPs.  The size of Spool 5 is estimated with no confidence
to be 355,011 rows (51,476,595 bytes).  The estimated time for
this step is 0.06 seconds.

10) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.

-> The contents of Spool 5 are sent back to the user as the result of
statement 1.  The total estimated time is 0.70 seconds.

 

2

Teradata Statistics Extrapolation for DATE Columns

What is Teradata Statistics Extrapolation?

A quite usual scenario in data warehousing is the daily population of snapshot historized tables, with data valid for the most recent date.

Here is a typical example:

SELECT Snapshot_Date,Count(*) CNT FROM The_Snapshot_Table;

Snapshot Date CNT
2015-04-10 10
2015-04-11 15
2015-04-12 10
2015-04-13 25
2015-04-14 15
2015-04-15 30

— We assume in our example, that statistics are collected on 2015-05-15, after the daily snapshot for 2015-01-15 was loaded:

COLLECT STATISTICS ON THE_SNAPSHOT_TABLE COLUMN(SNAPSHOT_DATE);

After loading 2015-04-16, and without extrapolation, for queries accessing snapshot date 2015-04-16, the optimizer would expect zero rows to be returned (assuming statistics are not recollected). For example, the optimizer would expect 0 rows to be returned in the query below:

SELECT * FROM The_Snapshot_Table WHERE Snapshot_Date=DATE’2015-04-16′

Extrapolation on DATE columns allows the optimizer to make reasonable estimations in such cases, even when statistics are stale. The optimizer considers date extrapolation only if it assumes that the current statistics are stale.

Which is Statistics Information extrapolated?

The optimizer is extrapolating the following statistical information:

  • The number of table rows, based on the available statistics summary information and random-AMP sampling
  • The number of distinct primary index values and the maximum value per statistic histogram
  • The number of rows per value for the date column

How is the Statistics Information extrapolated?

It is important to understand that the optimizer only estimates a certain number of dates into the future (future in this context means any date after the last date for which collected statistics exist). The highest estimated date used for extrapolation is based on a random AMP sample. In the extrapolation process, the random AMP sample gives information about the number of distinct future dates.

Below extrapolation formula is used to calculate the highest future date:

Maximum Extrapolated Date = Maximum Date with Collected Statistics + ((Maximum Date with Collected Statistics – Minimum Date with Collected Statistics) * ((Distinct Random AMP Sample Dates * Average Rows Per Date from Collected Statistics) / (Distinct Dates from Collected Statistics * Average Rows Per Date from Collected Statistics))

Here is an example with 1000 Average Rows per Date from Collected Statistics, the random AMP sample is assumed to return 10 different future dates:

Maximum Extrapolated Date = 2015-03-30 + ((2015-03-30 – 2015-01-01) * ((10 * 1000) / (89 * 1000)) = 2015-04-09

teradata statistics extrapolation

Assuming that our query selects a date range between 2015-03-25 and 2015-04-09, the estimated number of rows will be:

Estimated Rows from Collected Stats + Estimated Rows from Extrapolation: (6*1000) + (10*1000)  = 16000 Rows.

As we can see, the estimation is much better than without extrapolation (the optimizer would only have estimated 6000 rows from the collected, but stale statistics).

The quality of the estimation based on a random AMP sample highly depends on the skew factor of the table.

The excellent case is when the random AMP sampling delivers all distinct future dates, but a skewed table may result in an entirely wrong estimation of different future dates.

Another important influence factor is how the extrapolated dates are covering the date range of your query.

The optimal situation is if the collected statistics plus the extrapolated statistics include the query date range entirely. Even if only a part of the extrapolated statistics includes your query, the overall estimated statistics will be better than without statistics extrapolation.

Only in the case that your query date range is not covered by the extrapolated statistics at all, there is no advantage from extrapolation

teradata statistics extrapolation

Although statistics extrapolation can be very helpful in the case of snapshot tables which are loaded on a daily base, this should not be an excuse to stop collecting statistics all. Extrapolation helps you to move your recollection process to a point in time when your Teradata system is less loaded.

2

Teradata Statistics – the new Syntax in Teradata 14.00

Starting with Release 14.00 there are several improvements for Teradata statistics which are worth being described in detail.

First of all, you should get comfortable with the new syntax, which you should use from now on. This article will cover the new syntax and discuss its advantages which are available starting with Teradata 14.00

COLLECT STATISTICS COLUMN (A), COLUMN(B), … , COLUMN(Z) ON TABLE_NAME;

As you can see, with the new syntax you can group several collect statistics requests for one table into only one request. It is not the new syntax alone which catches our attention (you can still use the old syntax if you like, even it is not recommended) but the improvement in performance and resource usage.

Whenever you use the new syntax and are grouping together statistics collections on columns, the Teradata optimizer tries to optimize your request in a way that the table scans are being minimized. Let’s look at an example:

COLLECT STATISTICS COLUMN(A,B), COLUMN(A), COLUMN(B);

In above example, the optimizer may just need one table scan to build the statistics for column A, column B, and columns (A, B). From a technical point of view, first the statistics for (A, B) will be calculated, and the statistics for A and B will be calculated by reusing the spool which was created by calculating the statistics for (A, B).

Although this approach is not chosen always (or only starting with the second time these particular statistics are refreshed), ou will recognize the huge improvements in performance and resource usage possible. Keep in mind that the old syntax would have issued 3  table scans!

Conclusion :

It is highly recommended to rewrite your existing collect statistic statements into the new syntax!

>