Re: Aggregate query

"bob123" <bob123_at_gmail.com> wrote in message news:4996d5da$0$20706$426a74cc_at_news.free.fr...
> 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
>
>

Are the estimates realistic - some of the numbers don't seem to be entirely consistent with a dimension/fact relationship.

You seem to have 900MB of fact data in 20M rows, with 100 bytes per row in the dimension table, with a join leading to 221M rows and 12GB - but the join to a dimension table should be on the primary key, which means the row count shouldn't change.

First thought - you do need a large temp space, but you could reduce the requirement by aggregating first on the fact table then joining to the dimension table (this transformation should be legal if you are joining on the PK of the dimension table). You may have to aggregate again - but it should only be 8,000 rows you start with - because two dimension rows with different keys may have identical non-key values on the column that appears in the final group by.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html