Archives of the TeradataForum

Message Posted: Wed, 19 Sep 2012 @ 09:30:26 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I have a query which is specifically user/BO query It is taking lot of time .. How to improve the performance of the same..

Below is the query and index/stats information..

     LOCKING FACT_MSC_CDR_AGGR_DAILY_1 FOR ACCESS SELECT
       VW_DIM_TIME.TODAY_DATE,
       VW_CIRCLE.CIRCLE_NAME,

     COALESCE(VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID,'SMS_MO','SMMO',
            'SMS_MT','SMMT','DIAMETER','GPRS',VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID),
       COUNT(DISTINCT FACT_MSC_CDR_AGGR_DAILY_1.CP_ACCOUNT_KEY),
       SUM(FACT_MSC_CDR_AGGR_DAILY_1.CALL_COUNT),
       SUM(FACT_MSC_CDR_AGGR_DAILY_1.CALL_DURATION)/60
     FROM
       VW_DIM_TIME,
       VW_CIRCLE,
       VW_DIM_CALL_DIRECTION,
       FACT_MSC_CDR_AGGR_DAILY_1
     WHERE
       (VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY=FACT_MSC_CDR_AGGR_DAILY_1.CALL_DIRECTION_KEY  )
       AND  ( VW_CIRCLE.GEOGRAPHY_KEY=FACT_MSC_CDR_AGGR_DAILY_1.CIRCLE_KEY  )
       AND  ( VW_DIM_TIME.TIME_KEY=FACT_MSC_CDR_AGGR_DAILY_1.DAY_KEY  )
       AND  ( VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID  NOT IN
     ('TRANSIT-O','TRANSIT-I','TRANS','TRANSIT','TRA','TRAN','-901','-902','0','50','1','2','3')  )
       AND
       VW_DIM_TIME.TODAY_DATE  BETWEEN  {d '2012-07-01'}  AND  {d '2012-07-07'} GROUP BY
       1,
       2,
       3

     PRIMARY INDEX ( DAY_KEY ,CIRCLE_KEY ,CALL_SCENARIO_KEY ,CALL_DIRECTION_KEY ,
            CALL_TYPE_KEY ,CP_ACCOUNT_KEY )
        PARTITION BY RANGE_N(CAST((SUBSTR(TRIM(BOTH  FROM DAY_KEY ),1 ,8 )) AS DATE FORMAT 'YYYYMMDD')
            BETWEEN '2012-01-01' AND '2017-12-31' EACH INTERVAL '1' DAY , NO RANGE, UNKNOWN)
     INDEX idx1 ( DAY_KEY ,CIRCLE_KEY ,CALL_DIRECTION_KEY );


     COLLECT STATS ON FACT_MSC_CDR_AGGR_DAILY_1 COLUMN (day_key)
     COLLECT STATS ON FACT_MSC_CDR_AGGR_DAILY_1 COLUMN (cp_account_key);
     COLLECT STATS ON FACT_MSC_CDR_AGGR_DAILY_1 COLUMN (day_key,circle_key,call_direction_key);

Regards,

Lav

	<Prev	Next>		<<First	<Prev	Next>	Last>>