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

From: Obivan <djeday84_at_gmail.com>
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-l
```
Received on Thu Mar 06 2014 - 03:33:52 CET

Original text of this message