1 %\RequirePackage{snapshot} % stats of included files: $filename.dep
3 \documentclass[a4paper]{scrartcl}
4 \usepackage[utf8]{inputenc}
5 \usepackage[naustrian]{babel}
6 \usepackage[T1]{fontenc}
7 \usepackage{amsmath,enumerate}
14 \lstset{language=sql,basicstyle=\small,keywordstyle=\ttfamily,morekeywords={REFERENCES,DEFERRED}}
16 \PassOptionsToPackage{hyphens}{url}\usepackage{hyperref}
20 \pagestyle{scrheadings}
22 % includable git commit info
23 \usepackage[missing=run\ build.sh\ or\ gitinfohook.sh]{gitinfo}
25 \newcommand{\ul}[1]{\underline{#1}}
26 \newcommand{\ra}{\rightarrow}
27 \newcommand{\R}{\ensuremath{\mathcal{R}}}
29 \newtheorem{ex}{Aufgabe}
30 \newenvironment{exercise}[2]%
36 \begin{ex}[#1][#2 Punkt\ifx\points\tmp\else e\fi]
45 %% Die vorgegebene Formatierung der Loesung ist nur als
46 %% Beispiel bzw. Hilfe gedacht und kann gerne geaendert
50 \title{Exercise Sheet 1, 2019}
51 \subtitle{6.0 VU Advanced Database Systems}
52 \author{David Kaufmann (00700719)\\Jan VALES (00726236)}
64 \subsubsection*{Version}
66 \url{https://git.somenet.org/priv/jan/adbs.git}\\
67 This is revision: \textbf{\gitAbbrevHash} Document (.tex) compiled on: \textbf{\today}
68 \end{footnotesize}\newpage
71 \begin{exercise}{I/O}{1}
72 \begin{enumerate}[label=\alph*)]
74 Assumed sector size: 4kB \\
75 Average Rotational Delay \\
76 $ AvgRotDelay = 0.5 * rotations * rotationalSpeed $ \\
77 $ 1000 rotations = 60000 ms $ \\
78 $ 1 rotation = 6 ms $ \\
80 $ TimeToRead = 8192 kB / 133120 kB * 1000 ms $ \\
81 $ AvgSeekTime = 4 ms $ \\
82 $ TransferTime = AvgSeekTime + AvgRotDelay + TimeToRead $ \\
83 $ TransferTime = 4 ms + 3 ms + 0.06 ms $ \\
84 $ TransferTime = 7.06 ms $
85 \item Random Blocks: \\
86 $ TotalTransferTime = 20 * TransferTime $ \\
87 $ TotalTransferTime = 20 * 7.06 ms $ \\
88 $ TotalTransferTime = 141.2 ms $ \\
89 Consecutive Blocks: \\
90 $ BytesToRead = 20 * 8 kB $ \\
91 $ BytesToRead = 160 kB => 1 Track $ \\
92 $ TotalTransferTime = 1 * TrackSeekTime + 1 * AvgRotDelay + 20 * TransferTime $ \\
93 $ TotalTransferTime = 4 ms + 3 ms + 20 * 0.06 ms $ \\
94 $ TotalTransferTime = 8.2 ms $
96 $ RecordSize = 120 B + 8 B + 12 B + 4 B + 1 B = 145 B $
97 \item Blocking Factor: \\
98 $ MaximumRecordCountPerBlock = math.floor( 8192 kB / 8 kB ) $ \\
99 $ MaximumRecordCountPerBlock = 56 $ \\
100 $ NumberOfBlocks = math.ceil( NumRecords / MaximumRecordCountPerBlock ) $ \\
101 $ NumberOfBlocks = 715 $ \\
102 $ WastedBytesPerBlock = BlockSize - ( MaximumRecordCountPerBlock * RecordSize ) $
103 \item Average Search Time: \\
104 $ NumberOfTracks = math.ceil( NumberOfBlocks * BlockSize / AverageTrackSize ) $ \\
105 $ NumberOfTracks = math.ceil( 11.17 ) $ \\
106 $ NumberOfTracks = 12 $ \\
107 $ SeqScanReadtime = (NumberOfBlocks * BlockSize) * 1000 ms / 133129 kB $ \\
108 $ SeqScanReadtime = (715 * 8 kB) * 1000 ms / 133129 kB $ \\
109 $ SeqScanReadtime = 5720 kB * 1000 ms / 133129 kB $ \\
110 $ SeqScanReadtime = 42.969 ms $ \\
111 $ AverageSearchTime = 1 * AvgSeekTime + 0.5 * ( NumberOfTracks * AvgRotDelay + (NumberOfTracks - 1) * TrackToTrackSeekTime + SeqScanReadTime ) $ \\
112 $ AverageSearchTime = 4 ms + 0.5 * ( 12 * 3 ms + 11 * 0.2 ms + (715 * 8 kB) ) $ \\
113 $ AverageSearchTime = 44.585 ms $ \\
114 $ AverageNonContinuousSearchTime = NumberOfBlocks * TransferTime $ \\
115 $ AverageNonContinuousSearchTime = 715 * 7.06 ms $ \\
116 $ AverageNonContinuousSearchTime = 5047.9 ms $
117 \item AverageNumberOfBlockAccess: \\
118 $ AverageNumberOfBlockAccess = log(NumberOfBlocks) $ \\
119 $ AverageNumberOfBlockAccess = log(715) $ \\
120 $ AverageNumberOfBlockAccess = 6.572 $ \\
121 Average Block Search Time: \\
122 $ AverageBlockSearchTime = AverageNumberOfBlockAccess * 7.06 $ \\
123 $ AverageBlockSearchTime = 46.40 ms $
128 \begin{exercise}{I/O in Query Plans}{2}
133 \begin{exercise}{Selectivity}{3}
138 \begin{exercise}{The Query Planner and You}{4}
140 \begin{enumerate}[label=\alph*)]
141 \item Hash Join, then Merge Join
143 Only Merge join: Total cost: 190481.63
144 Only Hash join: Total cost: 378418.91
145 Only Nestloop: Total cost: 174352535.00
151 \begin{exercise}{Optimizing Queries}{5}