- The cache space requirements exceed the memory allocated for any of the transformations that create caches: Agg, Rnk, Lkp, Jnr, Srt.
- Incremental aggragation is used with an aggragator transformation to store the finished aggragated values until the next run
- Persistant lookup cache is selected to store the lookup caches until they are reused by the next lookup transformation.
Aggregate
Cache Calculation
|
||
Cache
|
Calculation
|
Columns in Cache
|
Index
|
# groups [( column size) + 17]
|
Group-by columns.
|
Data
|
# groups[( column size) + 7]
|
|
* Each aggregate function has different cache space requirements.
As a general rule, you can multiply the column containing the aggregate
function by three.
|
Rank
Cache Calculation
|
||
Cache
|
Calculation
|
Columns in Cache
|
Index
|
# groups [( column size) + 17]
|
Group-by columns.
|
Data
|
# groups [(#
ranks *( column size + 10)) + 20]
|
|
Joiner
Cache Calculation
|
||
Cache
|
Calculation
|
Columns in Cache
|
Index
|
# master rows
[( column size) + 16]
|
Master column in join conditions.
|
Data
|
# master rows
[( column size) + 8]
|
Master column not in join condition and
used for output.
|
Table
19-6. Lookup Cache Calculation
|
||
Cache
|
Calculation
|
Columns in Cache
|
Index
|
# rows in
lookup table [( column size) + 16]
|
Columns in lookup condition.
|
Data
|
# rows in
lookup table [( column size) + 8]
|
Connected output ports not in the lookup
condition.
Return port (for unconnected Lookup
transformations).
|
Cache Column Sizes
Column
Sizes for Cache Calculations
|
||
Datatype
|
Aggregator, Rank
|
Joiner, Lookup
|
Binary
|
precision + 2
|
precision + 8
Round to nearest multiple of 8
|
Date/Time
|
18
|
24
|
Decimal, high precision off (all
precision)
|
10
|
16
|
Decimal, high precision on (precision
<=18)
|
18
|
24
|
Decimal, high precision on (precision
>18, <=28)
|
22
|
32
|
Decimal, high precision on (precision
>28)
|
10
|
16
|
Decimal, high precision on (negative
scale)
|
10
|
16
|
Double
|
10
|
16
|
Real
|
10
|
16
|
Integer
|
6
|
16
|
Small integer
|
6
|
16
|
NString, NText, String, Text
|
Unicode mode: 2*(precision + 2)
ASCII mode: precision + 3
|
Unicode mode: 2*(precision + 5)
ASCII mode: precision + 9
|