Very Difficult Query

Hi to all,
I'm trying to optimize a query. Can someone help me? I have a big table like:

DESC big_table
(

  DOCUMENTID                         NUMBER(10, 0)   NOT NULL ,
  FIELDCODE                          NUMBER(5, 0)    NOT NULL ,
  FORMID                             NUMBER(10, 0)   NOT NULL ,
  VERIFIERUID                        NUMBER(10, 0)   DEFAULT NULL,
  PRIORITY                           DATE            NOT NULL ,
  STATE                              NUMBER(6, 0)    NOT NULL ,
  SUBSTATE                           NUMBER(6, 0)    NOT NULL ,
  IMAGEFILE                          VARCHAR2(256)   NOT NULL ,
  LEFTX                              NUMBER(6, 2)   ,
  UPPERY                             NUMBER(6, 2)   ,
  RIGHTX                             NUMBER(6, 2)   ,
  LOWERY                             NUMBER(6, 2)   ,
  PAGENUMBER                         NUMBER(3, 0)   ,
  RECOGNIZEDVALUE                    VARCHAR2(256)  ,
  RECOGNIZEDDATE                     DATE           ,
  ACTUALVALUE                        VARCHAR2(256)  ,
  ACTUALVALUEDATE                    DATE           ,
  LASTMODIFIED                       DATE            NOT NULL ,
  KEYINGCENTERID                     NUMBER(3, 0)   ,
  INUSE                              NUMBER(10, 0)   NOT NULL ,
  VIEWPRIORITY                       DATE           ,
  EXPORTDATE                         DATE           ,
  SLAVERIFICATION                    NUMBER(6, 0)   ,
  PROCESSINGDATE                     VARCHAR2(10)   )
pctfree       20
pctused       40
initrans      2
maxtrans      255

          initial         358M
          next            0M
          pctincrease     0
          maxextents      2147483645
        )

With DOCUMENTID and FIELDCODE as PK. The table is partitioned on FIELDCODE and it has more than 1.5 million rows with a lot of inserts/updates per day.

I also have a view like:
Create or Replace View AV_FLOWFIELDSVIEWPRIORITY  (

     VIEWPRIORITY,
     FIELDCODE,
     KEYINGCENTERID,
     STATE,
     FORMID,
     SUBSTATE,
     VERIFIERUID
     )

       formid, substate, verifieruid
from big_table
where inuse = 0 and viewpriority is not null
/

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 /*+

                              where av.FieldCode = p_FieldCode
                                and av.KeyingCenterId =
p_KeyingCenterId
                                and av.State = p_State
                                and (av.VERIFIERUID <> p_UserID OR

The problem is that when I'm doing the MIN I'm running a full table scan.
Does anybody have an idea how to improve the performnace???

Thanks a lot

Diego Received on Tue Feb 26 2002 - 16:14:09 CET