| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Rollup Aggregation Tuning
DA Morgan napisal(a):
> Pawel wrote:
> > Hi,
> >
> > I'm working on a data warehouse solution for fact data aggregation.
> > Short description:
> >
> > there's base level fact table:
> >
> > TIME_ID, GEO_ID, PROD_ID, AMOUNT1, AMOUNT2....
> >
> > Fact data is 3-dimensional with, say ~500 nodes in each of the
> > hierarchies lowest level (input data).
> > Rows are distinct. So there's 500^3 rows, with low cardinality for each
> > separate dimension column.
> >
> > I also have a flattened time hierarchy with 3 levels.
> > Flattened with this procedure:
> > When graph is (text-mode compliant :)...
> >
> > X (year)
> > / \
> > / \
> > P Q (halfs)
> > / \ \
> > / \ \
> > A B C (lowest level)
> >
> > ...HIER table rows look like this:
> >
> > Lvl1, Lvl2, Lvl3
> > ----------------
> > X P A
> > X P B
> > X Q C
> >
> > FACT table is joined with HIER (WHERE TIME_ID = Lvl3)
> > Then ROLLUP(Lvl1, Lvl2, Lvl3) is used for full aggregation on time
> > dimension.
> > [The lowest level (Lvl3) might be skipped and gotten from FACT table -
> > it's written here for clarity.]
> >
> > I'd like to know your opinions about the best way to tune the
> > performance of this query - or even rewrite it to leverage other Oracle
> > features (e.g. Dimension objects).
> > Fact table needs to be FULLy ACCESSed, but when GROUPing, there's
> > sorting needed as far as I know, so what about bitmap indexes? Or
> > concatenated bitmap index?
> > Then PARALLEL - will Oracle use it's advantages here?
> > Would partitioning help?
> >
> > Thanks,
> > Pawel
>
>
Hi,
I enclose the query and the explain plan below. It's a bit complicated :) ..
What it comes down to is the idea beyond tuning ROLLUP in general - it's the major operation in this particular query. As I wrote the core schema it quite simple - 3-dimensional facts connected with a flattened hierarchy and rolled-up..
WITH -- view of the input assoc (child-parent)
assocview AS (SELECT UNIQUE dsend_id AS kid, parnt_id AS papa
FROM time_perd_assoc_dnorm_254_vm
WHERE net_lvl = 1),
keepview AS (SELECT dsend_id AS keep_id
FROM time_perd_assoc_dnorm_254_vm
WHERE dsend_id = parnt_id
AND dsend_keep_flag = 'Y'),
-- view of the assoc (with null-child LEAFs and LEAFKID flag rows
added)
lhassoc AS (SELECT kid, papa, NULL AS leaflag
FROM assocview
UNION
SELECT NULL AS kid, kid AS papa, 'Y' AS leaflag
FROM assocview a
WHERE NOT EXISTS (SELECT 1 FROM assocview b
WHERE a.kid = b.papa)),
-- all distinct nodes with LEVELs
lnodes AS (SELECT papa AS node_id, 4 - MAX(LEVEL) AS lvl
FROM lhassoc
START WITH leaflag = 'Y'
CONNECT BY PRIOR papa = kid
GROUP BY papa),
-- all connections between levels 1 and 2
l2conn AS (SELECT papa AS l1, kid AS l2, NULL AS l3
FROM lhassoc
WHERE papa IN (SELECT node_id FROM lnodes WHERE lvl =
1)
AND kid IN (SELECT node_id FROM lnodes WHERE lvl =
2)),
-- all connections between levels 1 and 3
l3conn AS (SELECT papa AS l1, kid AS l2, kid AS l3
FROM lhassoc
WHERE papa IN (SELECT node_id FROM lnodes WHERE lvl =
1)
AND kid IN (SELECT node_id FROM lnodes WHERE lvl =
3)
UNION ALL
SELECT NULL AS l1, papa AS l2, kid AS l3
FROM lhassoc
WHERE papa IN (SELECT node_id FROM lnodes WHERE lvl =
2)
AND kid IN (SELECT node_id FROM lnodes WHERE lvl =
3)),
-- all levels connections joined and denormalized
hier AS (SELECT COALESCE(a.l1, b.l1, l2) AS l1, l2, a.l3
FROM l3conn a LEFT OUTER JOIN l2conn b USING (l2)
UNION
SELECT COALESCE(b.l1, a.l1) AS l1, l2, COALESCE(a.l3,
l2) AS l3
FROM l3conn a RIGHT OUTER JOIN l2conn b USING (l2)),
-- aggregation
aggfact AS (SELECT DECODE(GROUPING_ID(h.l1, h.l2), 0, h.l2, 1,
h.l1, NULL) AS time_perd_id,
captl_id, cust_id, demnd_plan_cust_grp_code,
due_perd, fact_type_code, func_id,
geo_id, iso_crncy_code_char, legal_ent_id,
lyout_id, measr_id, mm_hybrd_id,
org_id, prod_id, proft_ctr_id, site_id,
srce_sys_id, trade_chanl_id,
SUM(f.fact_amt_1) AS fact_amt_1,
SUM(f.fact_amt_2) AS fact_amt_2,
AVG(f.fact_amt_12) AS fact_amt_12,
AVG(f.fact_amt_13) AS fact_amt_13,
DECODE(MAX(DECODE(f.fact_amt_14, 4, -1, 8, -1,
f.fact_amt_14)),
-1, MIN(f.fact_amt_14),
MAX(f.fact_amt_14)) AS fact_amt_14,
SUM(f.fact_amt_28) AS fact_amt_28,
SUM(f.fact_amt_29) AS fact_amt_29,
MAX(f.fact_amt_36) AS fact_amt_36
FROM mm_agg_view_vm f,
hier h
WHERE f.time_perd_id = h.l3
GROUP BY ROLLUP(h.l1, h.l2),
captl_id, cust_id, demnd_plan_cust_grp_code,
due_perd, fact_type_code, func_id,
geo_id, iso_crncy_code_char, legal_ent_id,
lyout_id, measr_id, mm_hybrd_id,
org_id, prod_id, proft_ctr_id, site_id,
srce_sys_id, trade_chanl_id
UNION ALL
SELECT time_perd_id,
captl_id, cust_id, demnd_plan_cust_grp_code,
due_perd, fact_type_code, func_id,
geo_id, iso_crncy_code_char, legal_ent_id,
lyout_id, measr_id, mm_hybrd_id,
org_id, prod_id, proft_ctr_id, site_id,
srce_sys_id, trade_chanl_id,
fact_amt_1,fact_amt_2,fact_amt_12,fact_amt_13,fact_amt_14,fact_amt_28,fact_amt_29,fact_amt_36
FROM mm_agg_view_vm),
daggfact AS (SELECT ROW_NUMBER() OVER (PARTITION BY time_perd_id,
geo_id, prod_id
ORDER BY time_perd_id)
AS oragg_duplicate,
time_perd_id,
captl_id, cust_id, demnd_plan_cust_grp_code,
due_perd, fact_type_code, func_id,
geo_id, iso_crncy_code_char, legal_ent_id,
lyout_id, measr_id, mm_hybrd_id,
org_id, prod_id, proft_ctr_id, site_id,
srce_sys_id, trade_chanl_id,
fact_amt_1,fact_amt_2,fact_amt_12,fact_amt_13,fact_amt_14,fact_amt_28,fact_amt_29,fact_amt_36
FROM aggfact
WHERE time_perd_id IN (SELECT * FROM keepview))
SELECT time_perd_id,
captl_id, cust_id, demnd_plan_cust_grp_code, due_perd,
fact_type_code, func_id,
geo_id, iso_crncy_code_char, legal_ent_id, lyout_id, measr_id,
mm_hybrd_id,
org_id, prod_id, proft_ctr_id, site_id, srce_sys_id,
trade_chanl_id,
fact_amt_1,fact_amt_2,fact_amt_12,fact_amt_13,fact_amt_14,fact_amt_28,fact_amt_29,fact_amt_36
FROM daggfact
WHERE oragg_duplicate = 1
Operation Object Name
Rows Bytes Cost Object Node In/Out
PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE
1 M 257 M
RECURSIVE EXECUTION .SYS_LE_6_0
RECURSIVE EXECUTION .SYS_LE_6_1
RECURSIVE EXECUTION .SYS_LE_6_2
RECURSIVE EXECUTION .SYS_LE_6_3
RECURSIVE EXECUTION .SYS_LE_6_4
TEMP TABLE TRANSFORMATION VIEW
1 M 298 M 257 M
WINDOW SORT PUSHED RANK
1 M 294 M 257 M
HASH JOIN
1 M 294 M 257 M
VIEW
SYS.VW_NSO_1 10 90 48
SORT UNIQUE
10 210 48
VIEW
IDWGQ5.TIME_PERD_ASSOC_DNORM_254_VM 10 210 38
SORT UNIQUE
10 520 38
UNION-ALL
HASH JOIN
9 468 9
PARTITION RANGE SINGLE
KEY KEY
TABLE ACCESS FULL
IDWGQ5.TIME_PERD_ASSOC_DNORM 1 30 1
KEY KEY
TABLE ACCESS FULL
IDWGQ5.REF_CTRL 1 33 1
TABLE ACCESS FULL
IDWGQ5.TIME_PERD 915 19 K 7
SORT AGGREGATE
1 8
TABLE ACCESS FULL
IDWGQ5.WK_AGG_FACT_291 2 M 20 M 1712
1 1
FILTER
HASH JOIN
1 52 9
PARTITION RANGE SINGLE
KEY KEY
TABLE ACCESS FULL
IDWGQ5.TIME_PERD_ASSOC_DNORM 1 30 1
KEY KEY
TABLE ACCESS FULL
IDWGQ5.REF_CTRL 1 33 1
TABLE ACCESS FULL
IDWGQ5.TIME_PERD 27 594 7
SORT AGGREGATE
1 8
TABLE ACCESS FULL
IDWGQ5.WK_AGG_FACT_291 2 M 20 M 1712
1 1
VIEW
11 M 2G 257 M
UNION-ALL
SORT GROUP BY ROLLUP
15 K 2 M 257 M
HASH JOIN
6 G 1105G 18382
VIEW
75 K 1 M 607
SORT UNIQUE
75 K 3 M 607
UNION-ALL
HASH JOIN OUTER
50 K 2 M 23
VIEW
50 K 1 M 4
TABLE ACCESS FULL
SYS.SYS_TEMP_0FD9D68F1_33A540CE 50 K 879 K 4
VIEW
25 K 439 K 2
TABLE ACCESS FULL
SYS.SYS_TEMP_0FD9D68F0_33A540CE 25 K 439 K 2
HASH JOIN OUTER
25 K 1 M 14
VIEW
25 K 439 K 2
TABLE ACCESS FULL
SYS.SYS_TEMP_0FD9D68F0_33A540CE 25 K 439 K 2
VIEW
50 K 1 M 4
TABLE ACCESS FULL
SYS.SYS_TEMP_0FD9D68F1_33A540CE 50 K 879 K 4
HASH JOIN
11 M 1G 11660
PARTITION RANGE SINGLE
KEY KEY
INDEX RANGE SCAN
IDWGQ5.PROD_ASSOC_DNORM_IDX3 10 K 306 K 135
KEY KEY
SORT AGGREGATE
1 33
TABLE ACCESS FULL
IDWGQ5.REF_CTRL 1 33 1
HASH JOIN OUTER
10 M 1G 10757
TABLE ACCESS FULL
IDWGQ5.WK_AGG_FACT_291 2 M 279 M 1712
1 1
VIEW
146 K 3 M 5940
SORT GROUP BY
146 K 7 M 5940
HASH JOIN
285 K 14 M 3444
TABLE ACCESS FULL
IDWGQ5.WK_AGG_FACT_291 10 K 255 K 1712
1 1
TABLE ACCESS BY INDEX ROWID
IDWGQ5.MM_BUS_PRCSS_CTRL 147 2 K 32
INDEX RANGE SCAN
IDWGQ5.MM_BUS_PRCSS_CTRL_PK 147 2
TABLE ACCESS FULL
IDWGQ5.WK_AGG_FACT_291 1 M 27 M 1712
1 1
HASH JOIN
11 M 1G 11708
PARTITION RANGE SINGLE
KEY KEY
INDEX RANGE SCAN
IDWGQ5.PROD_ASSOC_DNORM_IDX3 10 K 306 K 135
KEY KEY
SORT AGGREGATE
1 33
TABLE ACCESS FULL
IDWGQ5.REF_CTRL 1 33 1
HASH JOIN OUTER
10 M 1G 10794
TABLE ACCESS FULL
IDWGQ5.WK_AGG_FACT_291 2 M 279 M 1712
1 1
VIEW
146 K 3 M 5940
SORT GROUP BY
146 K 7 M 5940
HASH JOIN
285 K 14 M 3444
TABLE ACCESS FULL
IDWGQ5.WK_AGG_FACT_291 10 K 255 K 1712
1 1
TABLE ACCESS BY INDEX ROWID
IDWGQ5.MM_BUS_PRCSS_CTRL 147 2 K 32
INDEX RANGE SCAN
IDWGQ5.MM_BUS_PRCSS_CTRL_PK 147 2
TABLE ACCESS FULL
IDWGQ5.WK_AGG_FACT_291 1 M 27 M 1712
1 1
---------------------------
cheers
Pawel
Received on Tue Oct 18 2005 - 18:54:07 CDT
 |
 |