Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DBMS_STATS.GATHER_TABLE_STATS & DBMS_STATS.GATHER_SCHEMA_STATS

614497Apr 1 2008 — edited Apr 3 2008
Hi All,

In the GATHER_TABLE_STATS or GATHER_SCHEMA_STATS procedures if for parameter method_opt we use 'FOR ALL INDEXED COLUMNS size skewonly', does this mean we only collect stats for index columns or collect stats for all columns in the table?

Thanks a lot,

Shirley

Comments

Chris Antognini
Shirley

Since the syntax is FOR ALL INDEXED COLUMNS, you are gathering statistics for all indexed columns only. I.e. not for all columns. FOR ALL COLUMNS should be used for that...

HTH
Chris
591186
FOR ALL INDEXED COLUMNS size skewonly',
This is for generating histograms on the indexed columns where column data is skewed. Collects the optimizer statistics for the table, columns, indexes and histograms for columns where the data shape is skewed. Only collects histograms for skewed column. In general it is slower to execute therefore it would not be suitable when the optimizer statistic collection window is short.

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(
ownname => '<table owner>',
tabname => '<table name>',
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
granularity => 'ALL',
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE)
Chris Antognini
Anantha
This is for generating histograms on the indexed columns
where column data is skewed. Collects the optimizer
statistics for the table, columns, indexes and histograms
for columns where the data shape is skewed.
This is wrong. Sorry. When the option FOR ALL INDEXED COLUMNS is specified, columns statistics are gathered only for the indexed columns.

Best regards,
Chris
71610
Hello All:
I also believe when you specify "FOR ALL INDEXED COLUMNS " in the method_opt , it generates histogram for the said columns.

Thanks
S~
Chris Antognini
Hi
I also believe when you specify "FOR ALL INDEXED COLUMNS " in
the method_opt , it generates histogram for the said columns.
If the size clause is not specified, whether the histograms are gathered depends on the version and, as of 10g, on the configuration.

My point was that the parameter method_opt not only impacts histograms, but also column statistics.

Cheers,
Chris
614497
Thanks, Chris.

If I want to gather stats for all columns in a table what value I should pass on to parameter method_opt?

Shirley
Jaffy
Hi,

You can use for all columns in method_opt option.

http://www.psoug.org/reference/dbms_stats.html

Regards

Jafar
591186
I agree.

method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', - For Generating Histograms.

Thanks for correction.
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 1 2008
Added on Apr 1 2008
#general-database-discussions
8 comments
53,507 views