i have an archive process through trx SARA in my production system.
It is very slow and i see that it hangs in this query:
"WI_ID" , "WI_TYPE" , "WI_TEXT" , "WI_CD" , "WI_AED" , "WI_AAGENT" ,
"CLIENT" = :A0 AND "WI_CD" BETWEEN :A1 AND :A2 AND "WI_AED" BETWEEN :A3 AND
:A4 AND ( "WI_STAT" = :A5 OR "WI_STAT" = :A6 ) AND "WI_CHCKWI" = :A7#
I created an index Z01 with these fields:
anyway the query take another index with only one field inside:
which index could be more able to satisfy this query?
the table is
Last statistics date 07.10.2008
Analyze Method Sample 441.606 Rows
Number of rows 147.202.000
Number of blocks allocated 7.019.464
Number of empty blocks 0
Average space 0
Chain count 0
Average row length 331
The index is not very good given the query:
CLIENT -> Used in the condition with "="
WI_CD -> Used in the condition with "BETWEEN"
WI_AED -> Used in the condition with "BETWEEN"
WI_STAT -> Used in the condition with "=" and OR
WI_CHCKWI -> Used in the condition with "="
If you want an index for that specific query modify the index with WI_CHCKWI so it looks like:
And, if you want an index only for this query (lets say a "one off" index to use and then drop) then make it so it contains also all the query fields, you avoid accessing to the table. but the index would be quite bigger.