I'll take a guess that the bold line is causing the problem possibly due to the TRUNC.  If you have an index on T3.trnsctn_tm, the TRUNC will cause the optimizer to not use the index and likely do a full scan on T3 instead.  It appears you are only wanting 10 days of data (May 1 - May 10) and I'd think that range should be small enough that using an index to get to those rows would be much more efficient than the table scan.  Try rewriting the TRUNC usages to isolate the T3.trnsctn_tm column so that the index could be selected.

 

This is just a guess since you didn't post the explain plan or any other info, but perhaps it'll get you started.

 

 

Karen Morton

Hotsos Enterprises, Ltd.

 

 

 

On 6/24/06, Ravindra K Singh <s.sethi@telserra.com> wrote:

Hello

 

 if I use this query , it is taking huge time to execute ,

 

but if I exclude bold lline then this is not taking time .

PLEASE HELP ME TO SORT OUT THIS PROBLEM

 

(we have 1 MB hash area size , is it possible to increase size from 1 mb to 10 mb )

 

SELECT

 T3.USR_ID ,T3.GM_ID,T3.PLY_ID ,

        SUM(TTL_TCKTS ) SOLD_TICKETS ,

             SUM(TTL_TCKTS*U1.PUR_PRICE ) COLL_TICKETS,

       0  AS TTL_PROJ_WNN,

       0 AS PROJPAYOUT

  FROM  ABC T3 ,  DEF   U1 , GHI  H1

    WHERE  T3.CNCLLD_FLG='N' AND

                        EXISTS  (

                  SELECT USR_ID

               FROM HRRCHY_USR_ID

               WHERE USR_ID = T3.USR_ID

                 )   AND

                  EXISTS  (

                     SELECT SCHM_ID

                        FROM ORG_SCHM_ID O1

                  WHERE O1.SCHM_ID= T3.SCHM_ID

                  )

          AND T3.SCHM_ID = U1.SCHM_ID

          AND H1.USR_ID = T3.USR_ID

               AND U1.USR_ID   = H1.BSS_ID  -- IN  ( SELECT STO FROM YYY  WHERE OPR=   T3.USR_ID )

          AND ( TRUNC(T3.trnsctn_tm) BETWEEN U1.WEF_DT AND NVL(U1.TILL_DT,SYSDATE) )

          AND (TRUNC(T3.trnsctn_tm) BETWEEN  TO_DATE('01/05/2006','DD/MM/YYYY') AND TO_DATE('10/05/2006','DD/MM/YYYY'))

  GROUP BY T3.USR_ID  ,T3.GM_ID,T3.PLY_ID

/