As also mentioned, it depends on which version you use, I think in the newer versions you don't need to creeate a sumindex field, though it may hurt performance if you don't. Here is an example of a SumIndex:
Order By "Item No.", "Location Code", and SumIndex field is Quantity:
| Item No. |
Location Code |
Quantity (accumulated) |
Calculate total |
| 1000 |
BLUE |
1 |
|
| 1000 |
BLUE |
2 |
|
| 2000 |
BLUE |
3 |
3 |
| 2000 |
RED |
4 |
|
| 2000 |
RED |
5 |
|
| 2000 |
RED |
6 |
|
| 2000 |
RED |
7 |
|
| 2000 |
RED |
8 |
|
| 2000 |
RED |
9 |
9 |
| 2000 |
ORANGE |
10 |
|
| 3000 |
BLACK |
11 |
|
| 3000 |
WHITE |
12 |
|
The table is from a number of transactions, each with Quantity 1. It keeps an accumulated total by the order specified. Each time a new transaction is recorded, the Sumindex gets updated.
Now say that you want to know the total for Item 2000, on RED location. Then all you do, is you take the accumulated number from the last line in that section (9), and deduct the accumulated number from the line just before (3), and just substract that, so we get 9 - 3 = 6. So we have quantity = 6 for Item 2000 on RED location.
The smart thing is that using this method, you can always get the total from only 2 reads, whether we are totalling up 5 transactions, or 5 million transactions, but it works only as long as you have the accumulated totals in the right order. When we don't have the right index, then of course the system needs to read and summarize all the entries in the scope
