Re: query problem - help me

  • From: "Karen Morton" <karen.r.morton@xxxxxxxxx>
  • To: s.sethi@xxxxxxxxxxxx
  • Date: Sat, 24 Jun 2006 10:20:32 -0700

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.
karen.morton@xxxxxxxxxx





On 6/24/06, Ravindra K Singh <s.sethi@xxxxxxxxxxxx> 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

/

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2006