RepoFIBtori

TRAINING 4

TRAINING ON SELECTION COST

Q眉esti贸 1.

0.5BD = 0.54001 = 200

Q眉esti贸 2.

hD + (O-1)/u * D + OD = (log100(42050)-1)1 + (150-1)/100 * 1 + 150*1 = 153.49

Q眉esti贸 3.

hD + (O-1)/u * D + OD = (log80(100010)-1)1 + (100000.5-1)/80 * 1 + (100000.5)*1 = 5064 Com es m茅s gran que el n煤mero de blocs (1000), el cost 茅s 1000.

Q眉esti贸 4.

hD + D + 1.5(O-1)/R * D = (log80(100010)-1)1 + 1 + 1.5(100000.5-1)/10 * 1 = 752.85

Q眉esti贸 5.

Com v = 1, perque 茅s codiAssig = 1, utilitzem la seg眉ent formula:

H + D + kD = 0 + 1 + (42050)/200 *1 = 106

Q眉esti贸 6.

hD + D = (log100(40050)-1)*1 + 1 = 3

Q眉esti贸 7.

hD + D + 1.5(O-1)/R * D = (log100(42050)-1)1 + 1 + 1.5((42050)/140 - 1)/50 * 1 = 7.47

Q眉esti贸 8.

BD = 4201 = 420

Q眉esti贸 9.

hD + D = (log100(42050)-1)*1 + 1 = 3

Q眉esti贸 10.

hD + (O-1)/u * D + OD = (log80(100010)-1)1 + ((100010)/200 -1)/80 * 1 + ((100010)/200)*1 = 52.6125

Q眉esti贸 11.

hD + D = (log80(100010)-1)*1 +1 = 3

Q眉esti贸 12.

O = (100010)(1000000000-900000000)(1000000000-600000000) = 2500 hD + (O-1)/u * D + OD = (log80(100010)-1)1 + (2500-1)/80 * 1 + 2500*1 = 2533.2375 Com es m茅s gran que el n煤mero de blocs (1000), el cost 茅s 1000.

Q眉esti贸 13.

H + D + D = 0 + 1 + 1 = 2

Q眉esti贸 14.

hD + D + 1.5(O-1)/R * D = (log80(101000)-1)1 + 1 + 1.5((101000)/200 - 1)/10 * 1 = 10.35

Q眉esti贸 15.

hD + D = (log80(101000)-1)*1 + 1 = 3

Q眉esti贸 16.

O = (100010)(1000000000-900000000)(1000000000-600000000) = 2500 hD + D + 1.5(O-1)/R * D = (log80(101000)-1)1 + 1 + 1.5*(2500 - 1)/10 * 1 = 377.85

Q眉esti贸 17.

k = (101000)/(2000-1901) = 100 v(H + D + kD) = 50(0 + 1 + 100*1) = 5050 Com es m茅s gran que el n煤mero de blocs (1000), el cost 茅s 1000.

Q眉esti贸 18.

k = (101000)/(200) = 50 H + D + kD = 0 + 1 + 50*1 = 51

TRAINING ON SELECTION AND JOIN COST

BR = Taula amb blocs m茅s petit. BS = Taula amb bloc m茅s gran.

Q眉esti贸 1.

Carinalitat = 1000010 = 100000 Cost = M = M-2 (always BlockNestedLoops) = 102-2 = 100 BS = 10000/0.6666 = 100001.5 = 150000 (clustered) BR路D + BS路 BR/M路D = 50001 + 15000(5000/100) = 755000

Q眉esti贸 2.

Carinalitat = 1000010 = 100000 Cost = RowNestedLoops = Not useful for no index attributes. BlockNestedLoops = BR路D + BS路 BR/M路D = 50001 + 10000(5000/(102-2)) = 505000 SortMatch = 2BR路logM(BR)路D = 25000log100(5000)1 = 20000 2BS路logM(BS)路D = 210000log100(10000)1 = 40000 (BR+BS)D = (5000+10000)1 = 15000 20000 + 40000 + 15000 = 75000 HashJoin = (2BR)路D + (2BS)路D + (BR+BS)路D = 250001 + 2100001 + (5000+10000)1 = 45000

Q眉esti贸 3.

Carinalitat = 500020(400/490) = 81632 Cost = BR = 5001.5 = 750 BS = 50001.5(400/490) = 6122 BlockNestedLoops = BR路D + BS路 BR/M路D = 7501 + 6122(750/4) = 1148625 RowNestedLoops = k = (500020)/5000 = 20 BR路D+ |R|路(hS路D+D+(1.5(k-1)/RS)路D) = 7501 + 7500(log100(81632)-1)1 + 1 + 1.5(20-1)/20 = 33937.5 SortMatch = 2BR路logM(BR)路D = 2750log2(750)1 = 15000 2BS路logM(BS)路D = 26122log2(6122)1 = 159175 (BR+BS)D = (750+6122)1 = 6872 15000+ 159175+ 6872 = 181044 Resultat = 9958.41

Q眉esti贸 4.

Carinalitat = 33343/1000 * 10 = 100 Cost = Select = h路D + ((|O|-1)/u)路D + |O|路D = (log40(33344)-1)1 + ((10-1)/40)1 + 10*1 = 12.225

BR = 1.53334/1000 = 5 BS = 33334/0.8 = 41667 SortMatch = 2BR路logM(BR)路D = 25log4(5)1 = 20 2BS路logM(BS)路D = 241667log4(41667)1 = 666672 (BR+BS)D = (5+41667)1 = 41672 20 + 666672 + 41672 = 708364 HashJoin: Nothing because Bsmaller > M^2 + M = 20 BlockNestedLoops: BR路D + BS路 BR/M路D = 51 + 416675/4 = 52088.75 RowNestedLoops: BR路D+ |R|路(H+D+k路D) = (10/3)1 + 10(0+1+101) = 113.33

Resultat = ?

Q眉esti贸 5.

Carinalitat = 10000*10 = 100000 Cost = M = M-2 (always HashJoin) = 102-2 = 100

(2BR)路D + (2BS)路D + (BR+BS)路D = 250001 + 2100001 + (5000+100001.5)1 = 50000

This site is open source. Improve this page.