Re: Very Difficult Query

"bdick" <bdick_at_cox.net> wrote in message news:<cOOe8.14437$s03.409467_at_news2.east.cox.net>...
> "Diego" <dpafumi_at_yahoo.com> wrote in message
> news:5b2b7013.0202260714.51022976_at_posting.google.com...
> > My query is like:
> >

Try this to put ff inside your hint. The hint should be

           /*+ INDEX(ff FOLWFILDS) */
> > 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?
Received on Tue Mar 05 2002 - 22:10:11 CET