Re: Very Difficult Query

"Diego" <dpafumi_at_yahoo.com> wrote in message news:5b2b7013.0202260714.51022976_at_posting.google.com...
> My query is like:
>
> UPDATE /*+ INDEX(FLOWFIELDS) */ big_table ff set FF.inuse = p_UserID,
> FF.lastmodified = sysdate
> WHERE FF.Inuse = 0
> AND FF.FieldCode = p_FieldCode
> AND FF.KeyingCenterId = p_KeyingCenterId
> AND FF.State = p_State
> AND ( FF.VERIFIERUID <> p_UserID OR FF.VERIFIERUID is null)
> AND FF.ViewPriority is not null
> and to_char(FF.ViewPriority, 'dd/mon/yy hh:mi:ss') = (select /*+
> parallel (av_FlowFieldsViewpriority, 4)*/
> to_char(min(av.ViewPriority), 'dd/mon/yy hh:mi:ss') from
> av_FlowFieldsViewpriority av
> where av.FieldCode = p_FieldCode
> and av.KeyingCenterId =
> p_KeyingCenterId
> and av.State = p_State
> and (av.VERIFIERUID <> p_UserID OR
> av.VERIFIERUID is null))

1. Why are you converting VIEWPRIORITY to char? You are using the full precision of the column, so you should be able to remove the to_char() function.
2. What is your current indexing? Do you have a index on FIELDCODE, KEYINGCENTERID, and STATE?

--
Later,
BEDick