Archives of the TeradataForum

Message Posted: Sun, 07 Apr 2013 @ 20:25:19 GMT

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

Hi Forum,

One of the users on the box is trying to optimize his query and when he runs a explain on the query, it takes 23 seconds to just run the explain. I checked the DBQLOGTBL and found that the parseCPUTime is 23 seconds. I am unable to understand why running an EXPLAIN should take 23 seconds to give the output? Since we have TASM enabled, I was thinking if the workload priority is causing it, however I ran the query with system ID and it still took the same 23 seconds and so I ruled out TASM.

Could any of you help me understand , the reason for this?

     EXPLAIN SELECT A11.R_T_ ID,
                    A13.R_C_G_ID,
                    A11.S_M_ID,
                    A12.M_S_CD,
                    A15.DAY_OF_WEEK_NR  ,
                    A11.C_ID,
                    SUM((CASE WHEN (A11.SC_ID >=  20121205) AND (A11.SC_ID <=  20130404)
                              THEN A11.R_O_NR ELSE NULL END))  WJXBFS1,
                    SUM((CASE WHEN (A11.SC_ID >=  20121205) AND (A11.SC_ID <=  20130404)
                              THEN A11.R_R_AMT ELSE NULL END))  WJXBFS2,
                    SUM((CASE WHEN (A11.SC_ID >=  20111205) AND (A11.SC_ID <=   20120404)
                              THEN A11.R_O_NR ELSE NULL END))  WJXBFS3,
                    SUM((CASE WHEN (A11.SC_ID >=  20111205) AND (A11.SC_ID <=   20120404)
                              THEN A11.R_R_AMT ELSE NULL END))  WJXBFS4,
                    SUM((CASE WHEN (A14.M_M_CD NOT IN ('OTZ') ) AND ( A11.SC_ID >=  20111205  AND A11.SC_ID <=  20120404)
                              THEN A11.R_O_NR ELSE NULL END))  WJXBFS5,
                    SUM((CASE WHEN (A11.SC_ID >=  20121205) AND (A11.SC_ID <=  20130404) AND (A14.M_M_CD NOT IN ('OTZ'))
                              THEN A11.R_O_NR ELSE NULL END))  WJXBFS6
                    FROM STAY A11
                         JOIN CHANNEL         A12
                           ON (A11.C_ID = A12.C_ID)
                         JOIN R_CODE     A13
                           ON (A11.R_C_ID = A13.R_C_ID)
                         JOIN MARKET            A14
                           ON (A11.S_M_ID = A14.M_ID)
                         JOIN CALENDAR        A15
                           ON (A11.SC_ID = A15._ID)
                    WHERE    (A11.H_ID IN (13)
                    AND A11.C_TYPE_CD IN ('RP')
                    AND A14.B_UNIT_NM IN ('H')
                    AND (A11.SC_ID >=  20121205
                    AND A11.SC_ID <=  20130404)
                    OR (A11.SC_ID >=  20111205  AND SC_ID <= 20120404))
                    GROUP BY A11.ROOM_TYPE_ID,
                             A13.RATE_CODE_GROUP_ID,
                             A11.STAY_MARKET_ID,
                             A12.MAJOR_SOURCE_CD,
                             A15.DAY_OF_WEEK_NR,
                             A11.COUNTRY_ID

Thanks

Roopalini

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