SQL & PL/SQL

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!

gather stats on only one column

cs01kksJul 12 2018 — edited Jul 12 2018

Hi ,

I am on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

I am gathering stats on a huge table about 400 million rows which is taking around 15 mins. Table creation took around 15 mins.

I looked at the explain plan, for gathering stats, I could see it is doing to_char(count()) on all columns which why it is taking so long time. Is there anything we can restrict system doing to_char(count()) on all columns?

May be one or two columns is fine. I am using the following syntax:

BEGIN
    sys.DBMS_STATS.gather_table_stats (
        ownname            => 'EMP_OWN'
      , tabname            => 'DEPT'
      , estimate_percent   => DBMS_STATS.auto_sample_size
      , method_opt         => 'FOR ALL COLUMNS SIZE 1'
      , degree             => 4);
END;
/

I do not want system to perform count on all the columns while gathering the stats. Any suggestions?

Thanks

kumar

This post has been answered by Solomon Yakobson on Jul 12 2018

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 9 2018

Added on Jul 12 2018

8 comments

1,740 views