dbms_stats and number of buckets

From: Ls Cheng <exriscer_at_gmail.com>
Date: Thu, 19 Oct 2017 10:02:49 +0200
Message-ID: <CAJ2-Qb_jYDca17kOKe+JAiEhSXS63MVFTK90+AckH32PA1LwZA_at_mail.gmail.com>



Hi

I am trying to generate histogram in one of columns in a message table, there are 5 differente values in the column as following

select estado, count(*) from hl7.hl7_pending group by estado;

ESTADO                             COUNT(*)
-------------------------------- ----------
OMITIDO                               40155
PENDIENTE                               145
ENVIADO                             8801353
PROCESADO                            363466
PENDIENTE2                              227


When I run dbms_stats as follows ( I tried different combination for mthod_opt)

exec dbms_stats.gather_table_stats('HL7', 'HL7_PENDING', method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 10 ESTADO') exec dbms_stats.gather_table_stats('HL7', 'HL7_PENDING', method_opt => 'FOR ALL COLUMNS SIZE skewonly')

The number of buckets shows 3 only instead of 5

select table_name, column_name, histogram, num_buckets   from dba_tab_columns
 where table_name = 'HL7_PENDING'
   and column_name = 'ESTADO';

TABLE_NAME                     COLUMN_NAME
HISTOGRAM       NUM_BUCKETS
------------------------------ ------------------------------
--------------- -----------
HL7_PENDING                    ESTADO
FREQUENCY                 3

the column is varchar32 and NOT NULL

And the query shows bad cardinality of course. This is RAC 11.2.0.4 with July 217 PSU. Any idea?

Thanks

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 19 2017 - 10:02:49 CEST

Original text of this message