cancel
Showing results for 
Search instead for 
Did you mean: 

doubt about UPDATE STAT COLUMN

Former Member
0 Kudos

Hi,

I have a doubt of when to execute update stat:

i create this SQL to generate an SQL script to generate update stat for all my tables:

select 'UPDATE STAT ' || schemaname || '.' || tablename || ' ESTIMATE SAMPLE 20 PERCENT' from tables where schemaname = 'DBUSER' and not tablename like 'JDBC%' AND type = 'TABLE'

and created this SQL to generate an SQL script for update stat for all columns.

select 'UPDATE STAT COLUMN(*) FOR ' || schemaname || '.' || tablename || ' ESTIMATE SAMPLE 20 PERCENT' from tables where schemaname = 'DBUSER' and not tablename like 'JDBC%' AND type = 'TABLE'

my doubt is i really need run that second script? or the UPDATE STAT for table dont UPDATE STAT for all columns?

thanks for any insight

Clóvis

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

> my doubt is i really need run that second script? or the UPDATE STAT for table dont UPDATE STAT for all columns?

Hi Clovis,

hmm... good question.

There are a few things to know about the UPDATE STAT command here.

1) It will always generate statistics for key or indexed columns.

2) The optimizer won't be able to generate a better plan when there are column statistics for non-indexed columns present.

3) The command will also collect column statistics for all columns that already have statistics.

The direct effect of 3) is that by running your second command just once - all column statistics will always be collected.

Since you can easily change the default sample size for your tables via


ALTER TABLE SAMPLE SIZE 20 PERCENT

I would say: drop your script and just use


UPDATE STAT DBUSER.* ESTIMATE 

This single command would lead to the same statistics as your script does.

And if you really, really want to leave out the JDBC tables - then just set their default sample size to 0 and they will be ignored by the UPDATE STAT command.

regards,

Lars

Former Member
0 Kudos

Hi Lars,

thanks you catch the point,

best regards

Clóvis

Answers (0)