Very Difficult Query

[diepa]

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
tablespace GRANDE
NOLOGGING
storage (
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
)
AS
select distinct viewpriority, fieldcode, keyingcenterid, state,
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 /*+ 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))



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

 

[HenryMonster]

Run this simple query

select * from AV_FLOWFIELDSVIEWPRIORITY

and tell me how long does it take before it returns
the first row ?
I think you need some more indexes here.
As a matter of fact your PK on the big_table
especially if the order of the columns in it is
(DOCUMENTID,FIELDCODE) does not speed up your queries,
because you do not have document_id in your where clauses;
you do have fieldcode, but since it come second in your
PK it really does not help.

I bet if your PK is changed to (FieldCode, DocumentId)
you'll see some performance gain.

In order to advise you of what indexes will help
you we need no know what data cardinality
you have on the columns which you use in your where
clauses, i.e. how many distinct values each of these
columns have and how evenly the values for each column
are spread across the table.
A column which has more distinct values which are
evenly spread is the best candidate for the first
column of the index that you need.

Let me give you an example:

say you have a table xtab with two columns a and b;
say we have 100 records in it;
now we run a query:

select a, count(*)
from xtab
group by a

and it yield these results:

a count(*)
-- --------
2 1
4 8
5 2
6 3
9 6
10 2
11 3
12 9
16 5
20 7
22 9
25 3
.......


and then you run another query

select b, count(*)
from xtab
group by b

which yields:

b count(*)
-- --------
5 20
8 30
9 1
10 1
12 2
14 46

it's obvoius that 'a' is by far a better
candidate for the first component of an index
which you want to have to speed up performance
if you have to run queries like this:

select *
from xtab
where a = ...
and b = ...

Of course this does not matter for a table of 100 records,
but provides an incredible performance gains for tables
of millions records.


WHen you create a view like

create view .... as
select distinct a,b,c,d,... from vtab

and vtab is very big
it's a good idea to have and index (or primary key)
on your first selected columns, i.e (a) or (a,b)
or (a,b,c), etc. Again keep in mind that the
best candidate for the first index column
is the one with the greatest cardinatility