Re: what does Grouping column cardinality mean in 10053 trace ?
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "djeday84@xxxxxxxxx" <djeday84@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Mar 2014 10:22:32 -0800 (PST)
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@xxxxxxxxx> 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%)')
>
Other related posts:
