Database

turn on suggestions

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

Showing results for

- 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

Define statistics on a populated table without collecting statistics

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

Re: Define statistics on a populated table without collecting statistics

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

Re: Define statistics on a populated table without collecting statistics

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

Re: Define statistics on a populated table without collecting statistics

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

Re: Define statistics on a populated table without collecting statistics

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

Re: Define statistics on a populated table without collecting statistics

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

Re: Define statistics on a populated table without collecting statistics

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

Re: Define statistics on a populated table without collecting statistics

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.