Re: Aggregate query
Date: Sat, 14 Feb 2009 07:55:34 -0800 (PST)
Message-ID: <b0db36c8-6038-478a-b17a-4a5409213ac2_at_r15g2000prh.googlegroups.com>
On Feb 14, 9:31 am, "bob123" <bob..._at_gmail.com> 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
If the CBO is correct and a hash join is the best approach to solving this query then your best course of action would be to increase the size of your temp tablespace. If that is not possible then you want to force the CBO to try the query using nested loops; however the group by is still going to require a sort. If your temp tablespace is not large enough (including sort overhead) to sort the data then the rdbms will still not be able to complete the query. Being that you have no filter conditions other than the join condition itself it should not be very hard for you to make an estimate of how many matching rows exist between the two tables and how many total rows will be returned and then how much space this result set will consume.
On a lower release I would suggest trying to ALTER SESSION SET HASH_JOIN_ENABLED = FALSE; however this parameter does not appear in my 10gR2 SQL manual so make sure the tables are listed in the FROM clause in the same order as what should be the join order and use the ORDERED and USE_NL hints (See SQL Manual) to force a nested loop join. Again this will only help if you have enough sort area to handle the group by sort.
Otherwise your database needs a bigger temporary tablespace.
HTH -- Mark D Powell -- Received on Sat Feb 14 2009 - 09:55:34 CST