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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only