HI I am using a query like this :
SELECT COUNT (*) FROM (SELECT
s.DIMENSION_ID,
r.dimprefixflag,
LDR_ENTITY_ID,
TRANSIT,
LEGAL,
SECTEUR,
RUBRIQUE,
PROJET,
PRODUIT,
CONSEILLER,
DIVERS,
AMT_CLASS_TYPE,
CURR_CODE,
DATE_LAST_POSTED,
UDAK_SURR_KEY,
BAL_ID_COUR,
BAL_ID_PREC,
ACCT_TYPE_CODE,
SOURCE,
DIM_TYPE,
MOIS_01,
MOIS_02,
MOIS_03,
MOIS_04,
MOIS_05,
MOIS_06,
MOIS_07,
MOIS_08,
MOIS_09,
MOIS_10,
MOIS_11,
MOIS_12,
MOISP_01,
MOISP_02,
MOISP_03,
MOISP_04,
MOISP_05,
MOISP_06,
MOISP_07,
MOISP_08,
MOISP_09,
MOISP_10,
MOISP_11,
MOISP_12,
CUMULMOIS_01,
CUMULMOIS_02,
CUMULMOIS_03,
CUMULMOIS_04,
CUMULMOIS_05,
CUMULMOIS_06,
CUMULMOIS_07,
CUMULMOIS_08,
CUMULMOIS_09,
CUMULMOIS_10,
CUMULMOIS_11,
CUMULMOIS_12,
CUMULMOISP_01,
CUMULMOISP_02,
CUMULMOISP_03,
CUMULMOISP_04,
CUMULMOISP_05,
CUMULMOISP_06,
CUMULMOISP_07,
CUMULMOISP_08,
CUMULMOISP_09,
CUMULMOISP_10,
CUMULMOISP_11,
CUMULMOISP_12,
CALENDRIER
FROM
REP_CTL_DIMENSION r,
REP_CTL_CUBEDIMENSION c,
SA_STRUCT_STRUCTURESGEAC s,
SA_GEAC_TBDATAMART d
WHERE
s.point_name = d.transit AND
r.dimension_id = c.dimension_id AND
s.dimension_id = r.dimension_id
GROUP BY
s.DIMENSION_ID,
r.dimprefixflag,
LDR_ENTITY_ID,
TRANSIT,
LEGAL,
SECTEUR,
RUBRIQUE,
PROJET,
PRODUIT,
CONSEILLER,
DIVERS,
AMT_CLASS_TYPE,
CURR_CODE,
DATE_LAST_POSTED,
UDAK_SURR_KEY,
BAL_ID_COUR,
BAL_ID_PREC,
ACCT_TYPE_CODE,
SOURCE,
DIM_TYPE,
MOIS_01,
MOIS_02,
MOIS_03,
MOIS_04,
MOIS_05,
MOIS_06,
MOIS_07,
MOIS_08,
MOIS_09,
MOIS_10,
MOIS_11,
MOIS_12,
MOISP_01,
MOISP_02,
MOISP_03,
MOISP_04,
MOISP_05,
MOISP_06,
MOISP_07,
MOISP_08,
MOISP_09,
MOISP_10,
MOISP_11,
MOISP_12,
CUMULMOIS_01,
CUMULMOIS_02,
CUMULMOIS_03,
CUMULMOIS_04,
CUMULMOIS_05,
CUMULMOIS_06,
CUMULMOIS_07,
CUMULMOIS_08,
CUMULMOIS_09,
CUMULMOIS_10,
CUMULMOIS_11,
CUMULMOIS_12,
CUMULMOISP_01,
CUMULMOISP_02,
CUMULMOISP_03,
CUMULMOISP_04,
CUMULMOISP_05,
CUMULMOISP_06,
CUMULMOISP_07,
CUMULMOISP_08,
CUMULMOISP_09,
CUMULMOISP_10,
CUMULMOISP_11,
CUMULMOISP_12,
CALENDRIER,
c.cube_id
HAVING c.cube_id = 1 AND
SOURCE IN ('A','G') AND
CALENDRIER IN ('11') AND
LDR_ENTITY_ID IN ('01')) .

The problem is that it take 30 mins to return the count. If I omit the Group by clause, it take 28 secs but I have a lot of duplicates fields. The table r has 6 rows, c has 3 rows, s has 13740 row and d has 992000 rows. I check the explain plan and all columns in the whereb clause are indexed and the indexes are used. Is there a way to speed up this query ?

Thank you,
Christian Cote