Define statistics on a populated table without collecting statistics

Database
Enthusiast

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
Junior Contributor

Re: Define statistics on a populated table without collecting statistics

There's a syntax to copy statistics:
COLLECT STATS ON newtab FROM oldtab;
Enthusiast

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;

 

 

 

Enthusiast

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"

Junior Contributor

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).

 

Enthusiast

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..

 

 

Junior Contributor

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.

Enthusiast

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.