Hi
After estimating with 100% I get 4 buckets instead of 5
So this is expected behaviour?
On Thu, Oct 19, 2017 at 10:31 AM, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> wrote:
I'd guess the "bad cardinality" is about 20,000 for PENDIENTE and
PENDIENTE2.
You either have to gather stats on just that column at estimate_percent =>
100 at the right time of day, or you can fake a histogram ( sample here for
a numeric column - just use the chararray for your requirment:
https://jonathanlewis.wordpress.com/2009/05/28/frequency-histograms/ ;).
12c could handle this properly with its auto_sample_size.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Ls Cheng <exriscer@xxxxxxxxx>
Sent: 19 October 2017 09:02:49
To: Oracle Mailinglist
Subject: dbms_stats and number of buckets
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://ipmcdn.avast.com/images/icons/icon-envelope-
tick-round-orange-animated-no-repeat-v1.gif]<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>
