Antwort: Density statistic calculation in case of histogram
From: Martin Klier <Martin.Klier_at_klug-is.de>
Date: Mon, 6 Apr 2009 14:04:35 +0200
Message-ID: <OF93012A7B.AEC93EC4-ONC1257590.004233D2-C1257590.004256B8_at_LocalDomain>
Hi Neeraj,
Date: Mon, 6 Apr 2009 14:04:35 +0200
Message-ID: <OF93012A7B.AEC93EC4-ONC1257590.004233D2-C1257590.004256B8_at_LocalDomain>
Hi Neeraj,
no straight answer to your question, but on which DB version are you testing?
Keep the estimate_percent default values in mind. Maybe if you force dbms_stats to compute stats, the result will differ.
-- Mit freundlichem Gruß Martin Klier Senior Oracle Database Administrator ------------------------------------------------------------------------------ Klug GmbH integrierte Systeme Lindenweg 13, D-92552 Teunz Tel.: +49 9671/9216-245 Fax.: +49 9671/9216-112 mailto: martin.klier_at_klug-is.de www.klug-is.de ------------------------------------------------------------------------------ Geschäftsführer: Johann Klug, Roman Sorgenfrei Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608, HRB Nr. 2037, Amtsgericht Amberg oracle-l-bounce_at_freelists.org schrieb am 06.04.2009 08:47:30:Received on Mon Apr 06 2009 - 07:04:35 CDT
> Von:
>
> Neeraj Bhatia <neeraj.dba_at_gmail.com>
>
> An:
>
> oracle-l_at_freelists.org
>
> Datum:
>
> 06.04.2009 08:48
>
> Betreff:
>
> Density statistic calculation in case of histogram
>
> Gesendet von:
>
> oracle-l-bounce_at_freelists.org
>
> Hi,
> Please tell me how density is calculated in case of histograms.
> I've a table with very skewed data:
> scott_at_ORADB10G> select col_skew, count(*) from tab_skew group by
> col_skew order by 1;
> COL_SKEW COUNT(*)
> ---------- ----------
> 1 10
> 2 10
> 3 10
> 4 10
> 5 10
> 6 10
> 7 10
> 8 10
> 9 10
> 10 9910
> 10 rows selected.
> scott_at_ORADB10G> exec dbms_stats.delete_table_stats(user,'TAB_SKEW');
> PL/SQL procedure successfully completed.
> -- Creating width-based histograms, #buckets = #distinct values
> scott_at_ORADB10G> exec dbms_stats.gather_table_stats(user, 'TAB_SKEW',
> method_opt=>'FOR COLUMNS COL_SKEW size 10');
> PL/SQL procedure successfully completed.
> scott_at_ORADB10G> select * from dba_tab_col_statistics where
> table_name='TAB_SKEW' and column_name='COL_SKEW';
> OWNER TABLE_NA COLUMN_NAME NUM_DISTINCT LOW_VA HIGH_V
> DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE GLO USE
> AVG_COL_LEN HISTOGRAM
> ------ -------- -------------------- ------------ ------ ------
> ---------- ---------- ----------- --------- ----------- --- ---
> ----------- ---------------
> SCOTT TAB_SKEW COL_SKEW 10 C102 C10B
> .00005 0 10 06-APR-09 10000 YES
> NO 3 FREQUENCY
> Question: How density is calculated here? Also it's equal to
> Selectivity of non-popular values.
> scott_at_ORADB10G> exec dbms_stats.gather_table_stats(user, 'TAB_SKEW',
> method_opt=>'FOR COLUMNS COL_SKEW size 5');
> PL/SQL procedure successfully completed.
> -- Creating Height-based histograms, #buckets < #distinct values
> scott_at_ORADB10G> select * from dba_tab_col_statistics where
> table_name='TAB_SKEW' and column_name='COL_SKEW';
> OWNER TABLE_NA COLUMN_NAME NUM_DISTINCT LOW_VA HIGH_V
> DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE GLO USE
> AVG_COL_LEN HISTOGRAM
> ------ -------- -------------------- ------------ ------ ------
> ---------- ---------- ----------- --------- ----------- --- ---
> ----------- ---------------
> SCOTT TAB_SKEW COL_SKEW 10 C102 C10B
> .98209 0 5 06-APR-09 10000 YES
> NO 3 HEIGHT BALANCED
> Question: How density is calculated here and how selectivity will be
> estimated for popolar as well as non-popular values?
>
> Any points/advice will be appreciated.
> Neeraj.
-- http://www.freelists.org/webpage/oracle-l