(数据库原理课件）Chapter10 Query Processing教材课程.ppt

External Sort-Merge (Cont.) Merge the runs (N-way merge). We assume (for now) that N M. Use N blocks of memory to buffer input runs, and 1 block to buffer output. Read the first block of each run into its buffer page repeat Select the first record (in sort order) among all buffer pages Write the record to the output buffer. If the output buffer is full write it to disk. Delete the record from its input buffer page.If the buffer page becomes empty then read the next block (if any) of the run into the buffer. until all input buffer pages are empty: External Sort-Merge (Cont.) If N ? M, several merge passes are required. In each pass, contiguous groups of M - 1 runs are merged. A pass reduces the number of runs by a factor of M -1, and creates runs longer by the same factor. E.g. If M=11, and there are 90 runs, one pass reduces the number of runs to 9, each 10 times the size of the initial runs Repeated passes are performed till all runs have been merged into one. Example: External Sorting Using Sort-Merge External Merge Sort (Cont.) Cost analysis: 1 block per run leads to too many seeks during merge Instead use bb buffer blocks per run ? read/write bb blocks at a time Can merge ?M/bb?–1 runs in one pass Total number of merge passes required: ?log ?M/bb?–1(br/M)?. Block transfers for initial run creation as well as in each pass is 2br for final pass, we don’t count write cost we ignore final write cost for all operations since the output of an operation may be sent to the parent operation without being written to disk Thus total number of block transfers for external sorting: br ( 2 ?log ?M/bb?–1 (br / M)? + 1) ? Seeks: next slide External Merge Sort (Cont.) Cost of seeks During run generation: one seek to read each run and one seek to write each run 2 ?br / M? During the merge phase Need 2 ?br / bb? seeks for each merge pass except the final one which does not require a write Total number of seeks: 2 ?br / M? + ?br / bb? (2 ?log?M/bb?–1(br / M)