Aggregate query

From: bob123 <bob123_at_gmail.com>
Date: Sat, 14 Feb 2009 15:31:55 +0100
Message-ID: <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 Received on Sat Feb 14 2009 - 08:31:55 CST