Re: what does Grouping column cardinality mean in 10053 trace ?
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Tue, 4 Mar 2014 10:22:32 -0800 (PST)
Message-ID: <1393957352.20169.YahooMailNeo_at_web124704.mail.ne1.yahoo.com>
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%)') >
Date: Tue, 4 Mar 2014 10:22:32 -0800 (PST)
Message-ID: <1393957352.20169.YahooMailNeo_at_web124704.mail.ne1.yahoo.com>
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 Tue Mar 04 2014 - 19:22:32 CET