Query Execution
-----------------
Sort -> blocking operator
----------
PAGES(R) = 1,000
Sort: M=1,000 blocks for this operation
Cost = 1,000 (Read once + output)
--------
Sort: M=500 blocks for this operation
Step 1: Read 500 blocks, sort and write, Read 500 blocks, sort and write
(results in 2 sorted groups)
Cost = 2,000
Step 2: Read one block from each sorted group, merge and output
Cost = 1,000 (Read R once and output)
Total Cost = 3,000
--------------------
PAGES(R) = 1,000
Sort: M = 50 blocks
Step 1: Read in 50 blocks at a time, sort and write to temp.
Cost = 2,000 (Read R once + Write R once)
Sorted groups: 1,000/50= 20 sorted groups
Step 2: I need 20 blocks to be able to merge, I have 50!
Read R and merge amd output,
Cost = 1,000
Total cost = 3,000
-------------
Sort: M = 10 blocks
Step 1: Read 10 blocks at a time, sort and write.
Cost = 2,000 (read once write once)
Sorted groups: 100
Step 2: Merge 10 groups at a time and write!
Cost = 2,000 (read once/write once)
Reduce 100 sorted groups -> 10 sorted groups
Step 2: Merge 10 groups and output
Cost = 1,000
Total cost = 5,000
------------------------------
select
R.A
, count(*) as num
from
R,S
where
R.A=S.A
group by
R.A
order by
R.A, num ;
------------------