Appendix 3
Data values to determine n (for figures 6.2(a, b, c))

For the AGE, CRED_LIM and JOIN_DATE columns there are two tables each. The first shows maximum scan time saving, maxSTS, and minimum scan time saving, minSTS (= maxSTS × ∏QSj ), that can be made on each column for each of the seven queries. Recall QSi refers to the particular column under consideration, and ∏QSj is the product of the QS values of the other columns referenced by the query. There is the heuristic constraint that the minimum QS in any other dimension apart from i (the column under discussion) is limited to a minimum of 0.1. Table a3.1 shows ∏QSj as seen by the column under discussion with this heuristic minimum for each referenced column of each query.

∑maxSTS and ∑minSTS in the second table show the total proportional maximum STS and minimum STS respectively of each of the seven queries. These values are multiplied by the file size (expressed in thousands of pages, i.e. 900) to give maxSTS and minSTS in terms of pages accesses (in thousands). ΔmaxSTS = maxSTSn - maxSTSn+1. Similarly for minSTS, maxWLS and minWLS. pcostn is derived as shown in chapter 6.4.

Table a3.1 ∏QSj on the "other" columns
query GENDER AGE CRED_LIM JOIN_DATE
2 - 1 - -
3 0.05 → 0.1 0.5 - -
4 - - - 1
5 0.167 × 0.33 = 0.55 0.5 × 0.33=0.167 0.5 × 0.167=0.084 -
6 - - 1 -
7 - 0.167 0.117 -
Note: - is used to indicate that a column is not referenced in a query.
Table a3.2 AGE column maximum and minimum scan time savings (for fig. 6.2a)
  Q1   Q2   Q3   Q4   Q5   Q6   Q7  
  QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj
  1 1 0.167 1 0.05 0.5 1 1 0.167 0.167 1 1 0.117 0.167
n maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS
1 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000
2 0.000 0.000 -0.400 -0.400 -0.474 -0.237 0.000 0.000 -0.400 -0.067 0.000 0.000 -0.434 -0.072
3 0.000 0.000 -0.533 -0.533 -0.632 -0.316 0.000 0.000 -0.533 -0.089 0.000 0.000 -0.578 -0.097
4 0.000 0.000 -0.600 -0.600 -0.711 -0.355 0.000 0.000 -0.600 -0.100 0.000 0.000 -0.651 -0.109
5 0.000 0.000 -0.640 -0.640 -0.758 -0.379 0.000 0.000 -0.640 -0.107 0.000 0.000 -0.694 -0.116
6 0.000 0.000 -0.666 -0.666 -0.789 -0.395 0.000 0.000 -0.666 -0.111 0.000 0.000 -0.723 -0.121
7 0.000 0.000 -0.694 -0.694 -0.812 -0.406 0.000 0.000 -0.694 -0.116 0.000 0.000 -0.744 -0.124
8 0.000 0.000 -0.712 -0.712 -0.829 -0.414 0.000 0.000 -0.712 -0.119 0.000 0.000 -0.759 -0.127
9 0.000 0.000 -0.726 -0.726 -0.842 -0.421 0.000 0.000 -0.726 -0.121 0.000 0.000 -0.773 -0.129
10 0.000 0.000 -0.736 -0.736 -0.853 -0.426 0.000 0.000 -0.736 -0.123 0.000 0.000 -0.785 -0.131
11 0.000 0.000 -0.743 -0.743 -0.861 -0.431 0.000 0.000 -0.743 -0.124 0.000 0.000 -0.794 -0.133
12 0.000 0.000 -0.750 -0.750 -0.868 -0.434 0.000 0.000 -0.750 -0.125 0.000 0.000 -0.802 -0.134
13 0.000 0.000 -0.757 -0.757 -0.874 -0.437 0.000 0.000 -0.757 -0.126 0.000 0.000 -0.808 -0.135
14 0.000 0.000 -0.763 -0.763 -0.880 -0.440 0.000 0.000 -0.763 -0.127 0.000 0.000 -0.813 -0.136
15 0.000 0.000 -0.768 -0.768 -0.884 -0.442 0.000 0.000 -0.768 -0.128 0.000 0.000 -0.817 -0.136
16 0.000 0.000 -0.772 -0.772 -0.888 -0.444 0.000 0.000 -0.772 -0.129 0.000 0.000 -0.821 -0.137
17 0.000 0.000 -0.775 -0.775 -0.892 -0.446 0.000 0.000 -0.775 -0.129 0.000 0.000 -0.824 -0.138
18 0.000 0.000 -0.777 -0.777 -0.895 -0.447 0.000 0.000 -0.777 -0.130 0.000 0.000 -0.828 -0.138
19 0.000 0.000 -0.781 -0.781 -0.898 -0.449 0.000 0.000 -0.781 -0.130 0.000 0.000 -0.831 -0.139
20 0.000 0.000 -0.784 -0.784 -0.900 -0.450 0.000 0.000 -0.784 -0.131 0.000 0.000 -0.834 -0.139
21 0.000 0.000 -0.786 -0.786 -0.902 -0.451 0.000 0.000 -0.786 -0.131 0.000 0.000 -0.836 -0.140
22 0.000 0.000 -0.788 -0.788 -0.905 -0.452 0.000 0.000 -0.788 -0.132 0.000 0.000 -0.838 -0.140
23 0.000 0.000 -0.790 -0.790 -0.907 -0.453 0.000 0.000 -0.790 -0.132 0.000 0.000 -0.840 -0.140
Table a3.3 AGE column costs and savings
n ∑ maxSTS ∑ minSTS ∑ maxSTS
× file size		 Δ maxSTS
(K pages)		 ∑ minSTS
× file size		 Δ minSTS
(K pages)		 pcostn
(pages)		 pcostn
(K pgs)		 maxWLS
(K pgs)		 Δ maxWLS
(K pgs)		 minWLS
(K pgs)		 Δ minWLS
(K pgs)
1 0.000 0.000 0 1536 0 698 0 0 0 1528 0 690
2 -1.707 -0.776 -1536 512 -698 233 7890 8 -1528 504 -690 225
3 -2.276 -1.034 -2048 256 -931 116 15781 16 -2033 248 -915 108
4 -2.560 -1.164 -2304 154 -1047 70 23671 24 -2281 146 -1024 62
5 -2.731 -1.241 -2458 103 -1117 47 31562 32 -2426 95 -1086 39
6 -2.845 -1.293 -2561 88 -1164 42 39452 39 -2521 80 -1124 34
7 -2.943 -1.340 -2648 63 -1206 29 47342 47 -2601 55 -1158 22
8 -3.012 -1.372 -2711 48 -1235 22 55233 55 -2656 40 -1180 14
9 -3.066 -1.397 -2759 38 -1257 17 63123 63 -2696 30 -1194 9
10 -3.109 -1.416 -2798 30 -1274 13 71014 71 -2727 22 -1203 6
11 -3.142 -1.431 -2828 25 -1288 11 78904 79 -2749 17 -1209 3
12 -3.169 -1.443 -2852 24 -1299 11 86794 87 -2766 16 -1212 4
13 -3.196 -1.456 -2877 20 -1310 9 94685 95 -2782 12 -1215 1
14 -3.218 -1.466 -2897 16 -1319 8 102575 103 -2794 9 -1217 0
15 -3.237 -1.474 -2913 14 -1327 6 110466 110 -2803 6 -1217 -1
16 -3.252 -1.482 -2927 12 -1333 5 118356 118 -2809 4 -1215 -2
17 -3.265 -1.488 -2939 11 -1339 5 126246 126 -2813 3 -1213 -3
18 -3.277 -1.493 -2950 11 -1344 5 134137 134 -2816 4 -1209 -3
19 -3.290 -1.499 -2961 10 -1349 5 142027 142 -2819 2 -1207 -3
20 -3.301 -1.504 -2971 9 -1353 4 149918 150 -2821 1 -1203 -4
21 -3.311 -1.508 -2980 8 -1357 3 157808 158 -2822 0 -1200 -4
22 -3.319 -1.512 -2987 7 -1361 3 165698 166 -2821 -1 -1195 -5
23 -3.326 -1.515 -2994 n/a -1364 n/a 173589 174 -2820 n/a -1190 n/a
Table a3.4 CRED_LIM column maximum and minimum scan time savings (for fig. 6.2b)
  Q1   Q2   Q3   Q4   Q5   Q6   Q7  
  QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj
  1 1 1 1 1 1 1 1 0.33 0.084 0.167 1 0.167 0.117
n maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS
1 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000
2 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.254 -0.021 -0.400 -0.400 -0.400 -0.047
3 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.338 -0.028 -0.533 -0.533 -0.533 -0.062
4 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.440 -0.037 -0.600 -0.600 -0.600 -0.070
5 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.484 -0.041 -0.640 -0.640 -0.640 -0.075
6 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.504 -0.042 -0.666 -0.666 -0.666 -0.078
7 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.534 -0.045 -0.694 -0.694 -0.694 -0.081
8 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.550 -0.046 -0.712 -0.712 -0.712 -0.083
9 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.559 -0.047 -0.726 -0.726 -0.726 -0.085
10 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.573 -0.048 -0.736 -0.736 -0.736 -0.086
11 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.582 -0.049 -0.743 -0.743 -0.743 -0.087
12 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.587 -0.049 -0.750 -0.750 -0.750 -0.088
13 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.595 -0.050 -0.757 -0.757 -0.757 -0.089
14 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.600 -0.050 -0.763 -0.763 -0.763 -0.089
15 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.604 -0.051 -0.768 -0.768 -0.768 -0.090
16 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.609 -0.051 -0.772 -0.772 -0.772 -0.090
17 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.612 -0.051 -0.775 -0.775 -0.775 -0.091
18 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.615 -0.052 -0.777 -0.777 -0.777 -0.091
19 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.618 -0.052 -0.781 -0.781 -0.781 -0.091
20 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.621 -0.052 -0.784 -0.784 -0.784 -0.092
21 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.623 -0.052 -0.786 -0.786 -0.786 -0.092
22 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.625 -0.053 -0.788 -0.788 -0.788 -0.092
23 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 -0.627 -0.053 -0.790 -0.790 -0.790 -0.092
Table a3.5 CRED_LIM column costs and savings
n ∑ maxSTS ∑ minSTS ∑ maxSTS
× file size		 Δ maxSTS
(K pages)		 ∑ minSTS
× file size		 Δ minSTS
(K pages)		 pcostn
(pages)		 pcostn
(K pgs)		 maxWLS
(K pgs)		 Δ maxWLS
(K pgs)		 minWLS
(K pgs)		 Δ minWLS
(K pgs)
1 0.000 0.000 0 948 0 421 0 0 0 930 0 404
2 -1.053 -0.468 -948 316 -421 140 17534 18 -930 298 -404 123
3 -1.404 -0.624 -1264 212 -561 75 35069 35 -1229 194 -526 57
4 -1.640 -0.707 -1476 111 -636 43 52603 53 -1423 93 -584 26
5 -1.763 -0.755 -1587 67 -680 28 70137 70 -1516 49 -609 11
6 -1.837 -0.787 -1653 75 -708 30 87671 88 -1566 58 -620 12
7 -1.921 -0.820 -1729 49 -738 20 105206 105 -1624 31 -632 2
8 -1.975 -0.842 -1777 32 -758 14 122740 123 -1655 15 -635 -3
9 -2.011 -0.857 -1810 30 -772 11 140274 140 -1669 13 -631 -6
10 -2.044 -0.870 -1840 22 -783 8 157808 158 -1682 4 -625 -9
11 -2.069 -0.879 -1862 16 -791 7 175343 175 -1687 -2 -616 -11
12 -2.086 -0.887 -1878 20 -798 8 192877 193 -1685 3 -605 -10
13 -2.109 -0.896 -1898 15 -806 6 210411 210 -1688 -2 -596 -11
14 -2.126 -0.903 -1914 11 -812 5 227945 228 -1686 -6 -584 -13
15 -2.139 -0.908 -1925 11 -817 4 245480 245 -1680 -6 -572 -13
16 -2.152 -0.913 -1937 9 -822 4 263014 263 -1674 9 -559 4
17 -2.162 -0.917 -1946 7 -825 3 263014 263 -1683 7 -562 3
18 -2.170 -0.920 -1953 9 -828 4 263014 263 -1690 9 -565 4
19 -2.180 -0.924 -1962 8 -832 3 263014 263 -1699 8 -569 3
20 -2.188 -0.928 -1969 6 -835 3 263014 263 -1706 6 -572 3
21 -2.195 -0.930 -1975 6 -837 2 263014 263 -1712 6 -574 2
22 -2.201 -0.933 -1981 5 -840 2 263014 263 -1718 5 -577 2
23 -2.207 -0.935 -1986 n/a -841 n/a 263014 263 -1723 n/a -578 n/a
Table a3.6 JOIN_DATE column maximum and minimum scan time savings (for fig. 6.2c)
  Q1   Q2   Q3   Q4   Q5   Q6   Q7  
  QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj QSi ∏QSj
  1 1 1 1 1 1 0.4 1 1 1 1 1 1 1
n maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS maxSTS minSTS
1 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000
2 0.000 0.000 0.000 0.000 0.000 0.000 -0.479 -0.479 0.000 0.000 0.000 0.000 0.000 0.000
3 0.000 0.000 0.000 0.000 0.000 0.000 -0.639 -0.639 0.000 0.000 0.000 0.000 0.000 0.000
4 0.000 0.000 0.000 0.000 0.000 0.000 -0.719 -0.719 0.000 0.000 0.000 0.000 0.000 0.000
5 0.000 0.000 0.000 0.000 0.000 0.000 -0.767 -0.767 0.000 0.000 0.000 0.000 0.000 0.000
6 0.000 0.000 0.000 0.000 0.000 0.000 -0.799 -0.799 0.000 0.000 0.000 0.000 0.000 0.000
7 0.000 0.000 0.000 0.000 0.000 0.000 -0.821 -0.821 0.000 0.000 0.000 0.000 0.000 0.000
8 0.000 0.000 0.000 0.000 0.000 0.000 -0.839 -0.839 0.000 0.000 0.000 0.000 0.000 0.000
9 0.000 0.000 0.000 0.000 0.000 0.000 -0.852 -0.852 0.000 0.000 0.000 0.000 0.000 0.000
10 0.000 0.000 0.000 0.000 0.000 0.000 -0.863 -0.863 0.000 0.000 0.000 0.000 0.000 0.000
11 0.000 0.000 0.000 0.000 0.000 0.000 -0.871 -0.871 0.000 0.000 0.000 0.000 0.000 0.000
12 0.000 0.000 0.000 0.000 0.000 0.000 -0.878 -0.878 0.000 0.000 0.000 0.000 0.000 0.000
13 0.000 0.000 0.000 0.000 0.000 0.000 -0.885 -0.885 0.000 0.000 0.000 0.000 0.000 0.000
14 0.000 0.000 0.000 0.000 0.000 0.000 -0.890 -0.890 0.000 0.000 0.000 0.000 0.000 0.000
15 0.000 0.000 0.000 0.000 0.000 0.000 -0.894 -0.894 0.000 0.000 0.000 0.000 0.000 0.000
16 0.000 0.000 0.000 0.000 0.000 0.000 -0.898 -0.898 0.000 0.000 0.000 0.000 0.000 0.000
17 0.000 0.000 0.000 0.000 0.000 0.000 -0.902 -0.902 0.000 0.000 0.000 0.000 0.000 0.000
18 0.000 0.000 0.000 0.000 0.000 0.000 -0.905 -0.905 0.000 0.000 0.000 0.000 0.000 0.000
19 0.000 0.000 0.000 0.000 0.000 0.000 -0.908 -0.908 0.000 0.000 0.000 0.000 0.000 0.000
20 0.000 0.000 0.000 0.000 0.000 0.000 -0.910 -0.910 0.000 0.000 0.000 0.000 0.000 0.000
21 0.000 0.000 0.000 0.000 0.000 0.000 -0.913 -0.913 0.000 0.000 0.000 0.000 0.000 0.000
22 0.000 0.000 0.000 0.000 0.000 0.000 -0.915 -0.915 0.000 0.000 0.000 0.000 0.000 0.000
23 0.000 0.000 0.000 0.000 0.000 0.000 -0.917 -0.917 0.000 0.000 0.000 0.000 0.000 0.000
Table a3.7 JOIN_DATE column costs and savings
n ∑maxSTS ∑minSTS ∑maxSTS
× file size		 ∑minSTS
× file size		 pcostn (K pages) maxWLS
(K pages)		 minWLS
(K pages)		 ΔWLS
(K pages)
1 0.000 0.000 0 0 0 0 0 431
2 -0.479 -0.479 -431 -431 0 -431 -431 144
3 -0.639 -0.639 -575 -575 0 -575 -575 72
4 -0.719 -0.719 -647 -647 0 -647 -647 43
5 -0.767 -0.767 -690 -690 0 -690 -690 29
6 -0.799 -0.799 -719 -719 0 -719 -719 21
7 -0.821 -0.821 -739 -739 0 -739 -739 15
8 -0.839 -0.839 -755 -755 0 -755 -755 12
9 -0.852 -0.852 -767 -767 0 -767 -767 10
10 -0.863 -0.863 -776 -776 0 -776 -776 8
11 -0.871 -0.871 -784 -784 0 -784 -784 7
12 -0.878 -0.878 -791 -791 0 -791 -791 6
13 -0.885 -0.885 -796 -796 0 -796 -796 5
14 -0.890 -0.890 -801 -801 0 -801 -801 4
15 -0.894 -0.894 -805 -805 0 -805 -805 4
16 -0.898 -0.898 -809 -809 0 -809 -809 3
17 -0.902 -0.902 -812 -812 0 -812 -812 3
18 -0.905 -0.905 -815 -815 0 -815 -815 3
19 -0.908 -0.908 -817 -817 0 -817 -817 2
20 -0.910 -0.910 -819 -819 0 -819 -819 2
21 -0.913 -0.913 -821 -821 0 -821 -821 2
22 -0.915 -0.915 -823 -823 0 -823 -823 2
23 -0.917 -0.917 -825 -825 0 -825 -825 n/a
Note: Δ WLS is the same for both maxWLS and minWLS, hence it is shown only once

Table of Contents | Appendix 2 | Appendix 4