Re: query problem - help me

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_at_hotsos.com

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

--
http://www.freelists.org/webpage/oracle-l