Re: Aggregate query

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Sat, 14 Feb 2009 12:34:15 -0600
Message-ID: <X8Ell.16261$YU2.15057_at_nlpi066.nbdc.sbc.com>

bob123 wrote:
> Hi,
>
> I have to aggregate some data
> between fact table (20Mrows) and dim tables (8Krows)
> but the query failed due to huge TEMP space needed (10.2.0.4)
>
> how can I execute this query ?
>
> SELECT ( ("FAI_XPO_VNTUGA"."NUMMOIS" || "DMS_OLTP_HIE_UGA
> || "FAI_XPO_VNTUGA"."NUMAMM"
> ),
> "FAI_XPO_VNTUGA"."NUMAMM", "FAI_XPO_VNTUGA"."NUMTYPSRC",
> "FAI_XPO_VNTUGA"."NUMMOIS", "DMS_OLTP_HIE_UGA"."NUMSCR",
> "FAI_XPO_VNTUGA"."NUMTYPINDCA", "FAI_XPO_VNTUGA"."NUMTYPINDUN",
> SUM ("FAI_XPO_VNTUGA"."UN"), SUM ("FAI_XPO_VNTUGA"."CA"),
> SUM ("FAI_XPO_VNTUGA"."UNA1"), SUM ("FAI_XPO_VNTUGA"."CAA1")
> FROM "ZWMED"."DMS_OLTP_HIE_UGA" "DMS_OLTP_HIE_UGA",
> "ZWMED"."FAI_XPO_VNTUGA" "FAI_XPO_VNTUGA"
> WHERE ("DMS_OLTP_HIE_UGA"."NUMUGA" = "FAI_XPO_VNTUGA"."NUMUGA")
> GROUP BY "FAI_XPO_VNTUGA"."NUMAMM",
> "FAI_XPO_VNTUGA"."NUMTYPSRC",
> "FAI_XPO_VNTUGA"."NUMMOIS",
> "DMS_OLTP_HIE_UGA"."NUMSCR",
> "FAI_XPO_VNTUGA"."NUMTYPINDCA",
> "FAI_XPO_VNTUGA"."NUMTYPINDUN"
>
> SELECT STATEMENT CHOOSE Cost: 1,185,516 Bytes: 267,901,824 Cardinality:
> 4,700,032
> 4 HASH GROUP BY Cost: 1,185,516 Bytes: 267,901,824 Cardinality: 4,700,032
> 3 HASH JOIN Cost: 43,352 Bytes: 12,620,234,055 Cardinality: 221,407,615
> 1 TABLE ACCESS FULL TABLE ZWMED.DMS_OLTP_HIE_UGA Cost: 31 Bytes:
> 103,844 Cardinality: 7,988
> 2 TABLE ACCESS FULL TABLE ZWMED.FAI_XPO_VNTUGA Cost: 41,241 Bytes:
> 909,800,144 Cardinality: 20,677,276
>
>

What tool are you using that is double-quoting the object names? this makes things most uncomfortable to read.

Is there any other column you can add to the WHERE CLAUSE to reduce the amount of data being aggregated? Maybe you should do it in phases instead of trying to aggregate the whole thing at one time. Without some other limiting factor - you are going to join and group (20M*select-size) * (8Krows*select-size). Hope you have a really BIG temp space. Received on Sat Feb 14 2009 - 12:34:15 CST