HELP Query never completes

The following query causes our 1Gb TEMP tablespace to overflow with the 'Unable to extend TEMP tablespace' message and consequently cannot be run to completion.

We have analysed it up and down but to no avail.

Here are some other Stats:

The tables are all under 100,000 rows with the exception of the _AW table which is about 6,000,000 rows.
All the joined columns are indexed in single key indexes. All the _AW indexes are bitmaps
Not all the group by columns are indexed but indexing them seems to make no difference.

Thanks for any help.

MICHAELJ_at_CAPITAL-RE.COM

SELECT "PFM_USER"."PFM_TBL_POLICY"."POLICY_NUM",


         "PFM_USER"."PFM_TBL_CLIENT"."CLIENT_D",

         "PFM_USER"."PFM_TBL_REV_SR"."REV_SR_D",

         "PFM_USER"."PFM_TBL_REV_SR"."MASTER_K",

         "PFM_USER"."PFM_TBL_REV_SR"."NAME_REVS1",

         "PFM_USER"."PFM_TBL_REV_SR"."STATE",

         "PFM_USER"."PFM_TBL_TRNSUM"."ATTACH_DAT",

         "PFM_USER"."PFM_TBL_REV_SR"."NAIC_ID",

         "PFM_USER"."PFM_TBL_REV_SR"."MOODY_RATING",

         "PFM_USER"."PFM_TBL_REV_SR"."SP_RATING",

         "PFM_USER"."PFM_TBL_REV_SR"."REVENUE_TYPE",

         SUM("PFM_USER"."PFM_TBL_DSASSREF_AW"."PAR") PAR,
         SUM("PFM_USER"."PFM_TBL_DSASSREF_AW"."INTEREST") INTEREST
    FROM "PFM_USER"."PFM_TBL_CLIENT",


         "PFM_USER"."PFM_TBL_DSASSREF_AW",

         "PFM_USER"."PFM_TBL_POLICY",

         "PFM_USER"."PFM_TBL_REV_SR",

         "PFM_USER"."PFM_TBL_TRNSUM"

   WHERE ( "PFM_USER"."PFM_TBL_DSASSREF_AW"."REV_SR_K" =
"PFM_USER"."PFM_TBL_REV_SR"."REV_SR_K" ) and
         ( "PFM_USER"."PFM_TBL_DSASSREF_AW"."SUM_K" =
"PFM_USER"."PFM_TBL_TRNSUM"."TRNSUM_K" ) and
         ( "PFM_USER"."PFM_TBL_TRNSUM"."POLICY_K" =
"PFM_USER"."PFM_TBL_POLICY"."POLICY_K" ) and
         ( "PFM_USER"."PFM_TBL_DSASSREF_AW"."ORIGIN_CO" =
"PFM_USER"."PFM_TBL_CLIENT"."CLIENT_COD" ) and
         ( ( PFM_USER."PFM_TBL_DSASSREF_AW"."DS_DATE" > '30-JUN-97' )
AND
         ( PFM_USER."PFM_TBL_REV_SR"."REVENUE_TYPE" <> 'NMMORTINS' ) AND

         ( PFM_USER."PFM_TBL_DSASSREF_AW"."OWNER_CO" = 99 ) AND
         ( PFM_USER."PFM_TBL_DSASSREF_AW"."ORIGIN_CO" > 100 ) AND
         ( PFM_USER."PFM_TBL_REV_SR"."MASTER_K" = 10000000 ) )
GROUP BY "PFM_USER"."PFM_TBL_POLICY"."POLICY_NUM",


         "PFM_USER"."PFM_TBL_CLIENT"."CLIENT_D",

         "PFM_USER"."PFM_TBL_REV_SR"."REV_SR_D",

         "PFM_USER"."PFM_TBL_REV_SR"."MASTER_K",

         "PFM_USER"."PFM_TBL_REV_SR"."NAME_REVS1",

         "PFM_USER"."PFM_TBL_REV_SR"."STATE",

         "PFM_USER"."PFM_TBL_TRNSUM"."ATTACH_DAT",

         "PFM_USER"."PFM_TBL_REV_SR"."NAIC_ID",

         "PFM_USER"."PFM_TBL_REV_SR"."MOODY_RATING",

         "PFM_USER"."PFM_TBL_REV_SR"."SP_RATING",

         "PFM_USER"."PFM_TBL_REV_SR"."REVENUE_TYPE"