# Re: what does Grouping column cardinality mean in 10053 trace ?

Date: Thu, 06 Mar 2014 06:33:52 +0400

Message-ID: <5317DE90.9030108_at_gmail.com>

Many thanks !

On 03/04/2014 10:22 PM, David Fitzjarrell wrote:

*> Grouping column cardinality reports the number of distinct values for *

*> each column in the GROUP BY clause, in this case there is only one *

*> column. In your original question (and the full query posted in your *

*> last response) there are three columns in the group by clause. The *

*> GROUP BY cardinality is the product of the individual column *

*> cardinalities divided by (the square root of two)*(n-1) where n is the *

*> number of columns in the group by list. In a single-column group by *

*> clause the group by cardinality should equal the grouping column *

*> cardinality. The basic calculation I provided is for group by lists *

*> of two or more columns. The four columns reported in your original email:*

*>*

*> Grouping column cardinality [ CLIENT_UK] 580928*

*> Grouping column cardinality [ DEALS_CNT] 1*

*> Grouping column cardinality [GROUP_PROD] 1*

*> Grouping column cardinality [GROUP_ALL_] 1*

*>*

*>*

*> would then generate a group by cardinality of *

*> (580928*1*1*1)/1.4142/1.4142/1.4142) == 205395.*

*>*

*> This should be roughly the number of distinct rows returned by the *

*> group by expression for the columns listed.*

*> David Fitzjarrell*

*> Primary author, "Oracle Exadata Survival Guide"*

*>*

*>*

*> On Tuesday, March 4, 2014 12:33 AM, Obivan <djeday84_at_gmail.com> wrote:*

*> upd: query run fine after change*

*> where prod not in ('POS + POS','Низкодоходные *

*> (20-30%)','Среднедоходные (30-40%)','Акционные *

*> (<20%)','Высокодоходные( >40%)')*

*> To /where prod *IN*/*

*>*

*> but anyway what does grouping column cardinality means ?*

*> for example /Grouping column cardinality [ DEALS_CNT] 1/*

*>*

*> how it is translate to:*

*> select count (*),DEALS_CNT from MA_USER.segment_all_1 group by *

*> DEALS_CNT order by 1;*

*>*

*> COUNT(*)| DEALS_CNT*

*> ----------|----------*

*> 1| 6*

*> 1| 7*

*> 19| 5*

*> 132| 4*

*> 1273| 3*

*> 12581| 2*

*> 104902| 1*

*>*

*> ps: Histograms are exists and statistics are actual*

*>*

*>*

*> On 03/04/2014 11:01 AM, Obivan wrote:*

*>*

*>> where prod not in ('POS + POS','Низкодоходные *

*>> (20-30%)','Среднедоходные (30-40%)','Акционные *

*>> (<20%)','Высокодоходные( >40%)')*

*>*

*>*

*>*

-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 06 2014 - 03:33:52 CET