Fragmentation Elimination
| Fragmentation Elimination | ||||||||
|
It is important to understand that the possibility of eliminating fragments is determined by the fragmentation expression of the table being searched and the form of the WHERE clause of the query. Consider the following fragmentation expression for a table: FRAGMENT BY EXPRESSION 100 < col1 AND col2 < 0 IN dbspacel, 100 >= col1 AND col2 < 0 IN dbspace2, col2 >= 0 IN dbspace3
Poor or incomplete fragmentation expressions can lead to fragments being scanned instead of one or two FRAGMENT BY EXPRESSION
col1 = 'ABC123' in dbs1,
col1 = 'ERU698' in dbs2,
col1 = 'XCV234' or col1 = 'DEF789' in dbs3.
col1 = 'SDR334' or col1 = 'ERT456' in dbs4,
col1 = 'KLO789' or col1 = 'SDF386' in dbs5,
col1 = 'ASD123' or col1 = 'NJK444' in dbs6,
col1 >= 'FGH783' in dbs7,
col1 >= ' ' in dbs8
QUERY:
SELECT sum(col2), sum(col3), col1
FROM eric
WHERE eric.col1 in ('DEF678','PPP999')
GROUP BY 1, 2
ORDER BY 3,2,1;
Estimated Cost: 123215287
Estimated of Rows Returned: 12431245
Temporary Files Required For: Order By Group By
1) informix.eric: SEQUENTIAL SCAN (Parallel, fragments: ALL)
Filters: informix.eric.col1 IN ('DEF678','PPP999')
By re-writing the fragmentation statement eliminates fragments that do not need to be scanned. FRAGMENT BY EXPRESSION
col1 = 'ABC123' in dbs1,
col1 = 'ERU698' in dbs2,
col1 = 'XCV234' or col1 = 'DEF789' in dbs3.
col1 = 'SDR334' or col1 = 'ERT456' in dbs4,
col1 = 'KLO789' or col1 = 'SDF386' in dbs5,
col1 = 'ASD123' or col1 = 'NJK444' in dbs6,
col1 >= ' ' AND col1 <= 'FGH783' in dbs7
col1 > 'FGH783' AND col1 <= 'ZZZZZZ' in dbs8,
QUERY:
SELECT sum(col2), sum(col3), col1
FROM eric
WHERE eric.col1 in ('DEF678','PPP999')
GROUP BY 1, 2
ORDER BY 3,2,1;
Estimated Cost: 13215287
Estimated of Rows Returned: 2431245
Temporary Files Required For: Order By Group By
1) informix.eric: SEQUENTIAL SCAN (Parallel, fragments: 2, 7)
Filters: informix.eric.col1 IN ('DEF678','PPP999')
|