From dec0010cac9ef0b5fa98b034efa1a0e516e66fa8 Mon Sep 17 00:00:00 2001 From: Jan Vales Date: Wed, 3 Apr 2019 03:49:56 +0200 Subject: [PATCH] tex file aufgesplittet. --- ex1/main.tex | 80 +++++++------------------------------------------- ex1/main_1.tex | 55 ++++++++++++++++++++++++++++++++++ ex1/main_2.tex | 6 ++++ ex1/main_3.tex | 6 ++++ ex1/main_4.tex | 11 +++++++ ex1/main_5.tex | 6 ++++ 6 files changed, 95 insertions(+), 69 deletions(-) create mode 100644 ex1/main_1.tex create mode 100644 ex1/main_2.tex create mode 100644 ex1/main_3.tex create mode 100644 ex1/main_4.tex create mode 100644 ex1/main_5.tex diff --git a/ex1/main.tex b/ex1/main.tex index 82e8b8d..95decb8 100644 --- a/ex1/main.tex +++ b/ex1/main.tex @@ -69,86 +69,28 @@ This is revision: \textbf{\gitAbbrevHash} Document (.tex) compiled on: \textbf{\ \begin{exercise}{I/O}{1} -\begin{enumerate}[label=\alph*)] -\item - Assumed sector size: 4kB \\ - Average Rotational Delay \\ - $ AvgRotDelay = 0.5 * rotations * rotationalSpeed $ \\ - $ 1000 rotations = 60000 ms $ \\ - $ 1 rotation = 6 ms $ \\ - Transfer Time \\ - $ TimeToRead = 8192 kB / 133120 kB * 1000 ms $ \\ - $ AvgSeekTime = 4 ms $ \\ - $ TransferTime = AvgSeekTime + AvgRotDelay + TimeToRead $ \\ - $ TransferTime = 4 ms + 3 ms + 0.06 ms $ \\ - $ TransferTime = 7.06 ms $ -\item Random Blocks: \\ - $ TotalTransferTime = 20 * TransferTime $ \\ - $ TotalTransferTime = 20 * 7.06 ms $ \\ - $ TotalTransferTime = 141.2 ms $ \\ - Consecutive Blocks: \\ - $ BytesToRead = 20 * 8 kB $ \\ - $ BytesToRead = 160 kB => 1 Track $ \\ - $ TotalTransferTime = 1 * TrackSeekTime + 1 * AvgRotDelay + 20 * TransferTime $ \\ - $ TotalTransferTime = 4 ms + 3 ms + 20 * 0.06 ms $ \\ - $ TotalTransferTime = 8.2 ms $ -\item Record size: \\ - $ RecordSize = 120 B + 8 B + 12 B + 4 B + 1 B = 145 B $ -\item Blocking Factor: \\ - $ MaximumRecordCountPerBlock = math.floor( 8192 kB / 8 kB ) $ \\ - $ MaximumRecordCountPerBlock = 56 $ \\ - $ NumberOfBlocks = math.ceil( NumRecords / MaximumRecordCountPerBlock ) $ \\ - $ NumberOfBlocks = 715 $ \\ - $ WastedBytesPerBlock = BlockSize - ( MaximumRecordCountPerBlock * RecordSize ) $ -\item Average Search Time: \\ - $ NumberOfTracks = math.ceil( NumberOfBlocks * BlockSize / AverageTrackSize ) $ \\ - $ NumberOfTracks = math.ceil( 11.17 ) $ \\ - $ NumberOfTracks = 12 $ \\ - $ SeqScanReadtime = (NumberOfBlocks * BlockSize) * 1000 ms / 133129 kB $ \\ - $ SeqScanReadtime = (715 * 8 kB) * 1000 ms / 133129 kB $ \\ - $ SeqScanReadtime = 5720 kB * 1000 ms / 133129 kB $ \\ - $ SeqScanReadtime = 42.969 ms $ \\ - $ AverageSearchTime = 1 * AvgSeekTime + 0.5 * ( NumberOfTracks * AvgRotDelay + (NumberOfTracks - 1) * TrackToTrackSeekTime + SeqScanReadTime ) $ \\ - $ AverageSearchTime = 4 ms + 0.5 * ( 12 * 3 ms + 11 * 0.2 ms + (715 * 8 kB) ) $ \\ - $ AverageSearchTime = 44.585 ms $ \\ - $ AverageNonContinuousSearchTime = NumberOfBlocks * TransferTime $ \\ - $ AverageNonContinuousSearchTime = 715 * 7.06 ms $ \\ - $ AverageNonContinuousSearchTime = 5047.9 ms $ -\item AverageNumberOfBlockAccess: \\ - $ AverageNumberOfBlockAccess = log(NumberOfBlocks) $ \\ - $ AverageNumberOfBlockAccess = log(715) $ \\ - $ AverageNumberOfBlockAccess = 6.572 $ \\ - Average Block Search Time: \\ - $ AverageBlockSearchTime = AverageNumberOfBlockAccess * 7.06 $ \\ - $ AverageBlockSearchTime = 46.40 ms $ -\end{enumerate} +\input{main_1.tex} \end{exercise} - +\newpage \begin{exercise}{I/O in Query Plans}{2} - +\input{main_2.tex} \end{exercise} - +\newpage \begin{exercise}{Selectivity}{3} - +\input{main_3.tex} \end{exercise} - +\newpage \begin{exercise}{The Query Planner and You}{4} - -\begin{enumerate}[label=\alph*)] -\item Hash Join, then Merge Join -\item - Only Merge join: Total cost: 190481.63 - Only Hash join: Total cost: 378418.91 - Only Nestloop: Total cost: 174352535.00 -\end{enumerate} - +\input{main_4.tex} \end{exercise} - +\newpage \begin{exercise}{Optimizing Queries}{5} - +\input{main_5.tex} \end{exercise} +\newpage + \end{document} diff --git a/ex1/main_1.tex b/ex1/main_1.tex new file mode 100644 index 0000000..0ddcf1b --- /dev/null +++ b/ex1/main_1.tex @@ -0,0 +1,55 @@ +%ex1.1 + +\begin{enumerate}[label=(\alph*)] + \item + Assumed sector size: 4kB \\ + Average Rotational Delay \\ + $ AvgRotDelay = 0.5 * rotations * rotationalSpeed $ \\ + $ 1000 rotations = 60000 ms $ \\ + $ 1 rotation = 6 ms $ \\ + Transfer Time \\ + $ TimeToRead = 8192 kB / 133120 kB * 1000 ms $ \\ + $ AvgSeekTime = 4 ms $ \\ + $ TransferTime = AvgSeekTime + AvgRotDelay + TimeToRead $ \\ + $ TransferTime = 4 ms + 3 ms + 0.06 ms $ \\ + $ TransferTime = 7.06 ms $ + \item Random Blocks: \\ + $ TotalTransferTime = 20 * TransferTime $ \\ + $ TotalTransferTime = 20 * 7.06 ms $ \\ + $ TotalTransferTime = 141.2 ms $ \\ + Consecutive Blocks: \\ + $ BytesToRead = 20 * 8 kB $ \\ + $ BytesToRead = 160 kB => 1 Track $ \\ + $ TotalTransferTime = 1 * TrackSeekTime + 1 * AvgRotDelay + 20 * TransferTime $ \\ + $ TotalTransferTime = 4 ms + 3 ms + 20 * 0.06 ms $ \\ + $ TotalTransferTime = 8.2 ms $ + \item Record size: \\ + $ RecordSize = 120 B + 8 B + 12 B + 4 B + 1 B = 145 B $ + \item Blocking Factor: \\ + $ MaximumRecordCountPerBlock = math.floor( 8192 kB / 8 kB ) $ \\ + $ MaximumRecordCountPerBlock = 56 $ \\ + $ NumberOfBlocks = math.ceil( NumRecords / MaximumRecordCountPerBlock ) $ \\ + $ NumberOfBlocks = 715 $ \\ + $ WastedBytesPerBlock = BlockSize - ( MaximumRecordCountPerBlock * RecordSize ) $ + \item Average Search Time: \\ + $ NumberOfTracks = math.ceil( NumberOfBlocks * BlockSize / AverageTrackSize ) $ \\ + $ NumberOfTracks = math.ceil( 11.17 ) $ \\ + $ NumberOfTracks = 12 $ \\ + $ SeqScanReadtime = (NumberOfBlocks * BlockSize) * 1000 ms / 133129 kB $ \\ + $ SeqScanReadtime = (715 * 8 kB) * 1000 ms / 133129 kB $ \\ + $ SeqScanReadtime = 5720 kB * 1000 ms / 133129 kB $ \\ + $ SeqScanReadtime = 42.969 ms $ \\ + $ AverageSearchTime = 1 * AvgSeekTime + 0.5 * ( NumberOfTracks * AvgRotDelay + (NumberOfTracks - 1) * TrackToTrackSeekTime + SeqScanReadTime ) $ \\ + $ AverageSearchTime = 4 ms + 0.5 * ( 12 * 3 ms + 11 * 0.2 ms + (715 * 8 kB) ) $ \\ + $ AverageSearchTime = 44.585 ms $ \\ + $ AverageNonContinuousSearchTime = NumberOfBlocks * TransferTime $ \\ + $ AverageNonContinuousSearchTime = 715 * 7.06 ms $ \\ + $ AverageNonContinuousSearchTime = 5047.9 ms $ + \item AverageNumberOfBlockAccess: \\ + $ AverageNumberOfBlockAccess = log(NumberOfBlocks) $ \\ + $ AverageNumberOfBlockAccess = log(715) $ \\ + $ AverageNumberOfBlockAccess = 6.572 $ \\ + Average Block Search Time: \\ + $ AverageBlockSearchTime = AverageNumberOfBlockAccess * 7.06 $ \\ + $ AverageBlockSearchTime = 46.40 ms $ +\end{enumerate} diff --git a/ex1/main_2.tex b/ex1/main_2.tex new file mode 100644 index 0000000..affcb9c --- /dev/null +++ b/ex1/main_2.tex @@ -0,0 +1,6 @@ +%ex1.2 + +\begin{enumerate}[label=(\alph*)] + + +\end{enumerate} diff --git a/ex1/main_3.tex b/ex1/main_3.tex new file mode 100644 index 0000000..d59b3dd --- /dev/null +++ b/ex1/main_3.tex @@ -0,0 +1,6 @@ +%ex1.3 + +\begin{enumerate}[label=(\alph*)] + + +\end{enumerate} diff --git a/ex1/main_4.tex b/ex1/main_4.tex new file mode 100644 index 0000000..0f21a31 --- /dev/null +++ b/ex1/main_4.tex @@ -0,0 +1,11 @@ +%ex1.4 + +\begin{enumerate}[label=(\alph*)] + +\item Hash Join, then Merge Join +\item +Only Merge join: Total cost: 190481.63 +Only Hash join: Total cost: 378418.91 +Only Nestloop: Total cost: 174352535.00 + +\end{enumerate} diff --git a/ex1/main_5.tex b/ex1/main_5.tex new file mode 100644 index 0000000..1e44e28 --- /dev/null +++ b/ex1/main_5.tex @@ -0,0 +1,6 @@ +%ex1.5 + +\begin{enumerate}[label=(\alph*)] + + +\end{enumerate} -- 2.43.0