Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-04-2016
06:36 AM

10-04-2016
06:36 AM

Is it possible to DEFINE statistics on a (very large) populated table without actually collecting the statistics (I don't want to consume huge amount of CPU) . This will allow me to copy the statistics from an identical (populated) table which has full statistics collected already at a later date ?

7 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-04-2016
06:44 AM

10-04-2016
06:44 AM

There's a syntax to copy statistics:

COLLECT STATS ON newtab FROM oldtab;

COLLECT STATS ON newtab FROM oldtab;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-04-2016
07:17 AM

10-04-2016
07:17 AM

Hi Dieter

that is exactly what I intend to do but I am finding that the statistics need to be defined on the target table before they can be copied. My example may be a specific consideration as several of our stats definitions are of this type

COLUMN ( END(EFFECTIVE_PERIOD )) AS END_EFFECTIVE_PERIOD

I've just tried a COLLECT STATISTICS statement with

USING NO SAMPLE FOR CURRENT

which appears to allows me to define the stats on the target table without incurring the overhead of collecting them

I can then follow this with a

COLLECT STATISTICS ON table FROM table

which appears to work.

The only difference I am now seeing is that the SHOW STATS on the source table has the following comment

-- default SYSTEM SAMPLE PERCENT

-- default SYSTEM THRESHOLD PERCENT

and on the target table only has

-- default SYSTEM SAMPLE PERCENT

I don't know if the missing -- default SYSTEM THRESHOLD PERCENT will be significant when we issue (future) table level stats refresh statements COLLECT STATISTICS ON tablename;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-04-2016
07:34 AM

10-04-2016
07:34 AM

Ignore MY last post, I think I misunderstood the

USING NO SAMPLE FOR CURRENT

I thought this would mean the stats collection would not touch the data, but the explain suggests otherwise, so back to the drawing board,

how can I get a stats definition on a populated table which has stats defined on columns suing "expression specification"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-04-2016
10:22 AM

10-04-2016
10:22 AM

Can you post the actual stats using SHOW STATS ON source_table?

Copying expression stats works fine for me (as long as the column names match).

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-04-2016
11:59 PM

10-04-2016
11:59 PM

Here is some code which demonstrates the issue I am seeing.

I am on RELEASE 14.10.07.02

The final COLLECT STATISTICS statement gives error COLLECT Failed. [3706] Syntax error: Expression specification is not allowed in copy statistics operation.

I've tried various forms of the COLLECT with no success when includsing the BEGIN_EFFECTIVE_PERIOD stats

CREATE TABLE TESTA ( YEAR_OF_CALENDAR SMALLINT NOT NULL ,MONTH_OF_YEAR BYTEINT NOT NULL ,EFFECTIVE_PERIOD PERIOD(DATE) NOT NULL ) PRIMARY INDEX(YEAR_OF_CALENDAR,MONTH_OF_YEAR); INSERT INTO TESTA SEL YEAR_OF_CALENDAR ,MONTH_OF_YEAR ,PERIOD(MIN(CALENDAR_DATE),MAX(CALENDAR_DATE)) FROM SYS_CALENDAR.CALENDAR GROUP BY 1,2; COLLECT STATISTICS COLUMN (YEAR_OF_CALENDAR,MONTH_OF_YEAR) ,COLUMN (YEAR_OF_CALENDAR) ,COLUMN (MONTH_OF_YEAR) ,COLUMN(BEGIN(EFFECTIVE_PERIOD)) AS BEGIN_EFFECTIVE_PERIOD ON TESTA; SHOW STATISTICS ON TESTA; CREATE TABLE TESTB AS TESTA WITH DATA AND NO STATISTICS; COLLECT STATISTICS COLUMN (YEAR_OF_CALENDAR,MONTH_OF_YEAR) ,COLUMN (YEAR_OF_CALENDAR) ,COLUMN (MONTH_OF_YEAR) ON TESTB FROM TESTA COLUMN (YEAR_OF_CALENDAR,MONTH_OF_YEAR) ,COLUMN (YEAR_OF_CALENDAR) ,COLUMN (MONTH_OF_YEAR); COLLECT STATISTICS COLUMN (YEAR_OF_CALENDAR,MONTH_OF_YEAR) ,COLUMN (YEAR_OF_CALENDAR) ,COLUMN (MONTH_OF_YEAR) ,COLUMN(BEGIN(EFFECTIVE_PERIOD)) AS BEGIN_EFFECTIVE_PERIOD ON TESTB FROM TESTA COLUMN (YEAR_OF_CALENDAR,MONTH_OF_YEAR) ,COLUMN (YEAR_OF_CALENDAR) ,COLUMN (MONTH_OF_YEAR) ,COLUMN(BEGIN(EFFECTIVE_PERIOD)) AS BEGIN_EFFECTIVE_PERIOD;

Thanks for any help. Just to reiterate, the table that I wish to copy stats to has 10's of millions of rows per amp and I am trying to avoid / minimise CPU consumption to get stats in place. The table is a copy of a table which has full stats already collected..

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-05-2016
07:45 AM

10-05-2016
07:45 AM

As you want to copy all stats anyway there's no need to specify each stats.

This works fine for me:

COLLECT STATISTICS ON TESTB FROM TESTA;

Otherwise you can run

SHOW STATS VALUES ON TESTA;

cut & paste, search & replace the table name and run it.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-05-2016
08:30 AM

10-05-2016
08:30 AM

DOH !

I'd adapted the COLLECT STATS statement from a previous exercise where the 2 table definitions were different and therefore I needed to specify the columns but of course I should have just tried keeping it simple for my current scenario of identical table structures.

Many thanks Dieter.

PS. I had no idea you could use the SHOW STATS VALUES results to create stats on a table. Many thanks for that information also.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.