CSCE608 Database Systems Fall 2020 Instructor Jianer Chen

CSCE-608 Database Systems Fall 2020 Instructor: Jianer Chen Office: HRBB 338 C Phone: 845 -4259 Email: chen@cse. tamu. edu Notes 21: One-Pass Algorithms

Algorithms Implementing Relational Algebraic Operations • Projection and selection π, σ • Set/bag operations U S, ∩ S, − S, U B , ∩ B , − B • Join operations ×, , C • Extended operations γ, δ, τ, table-scan π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 2 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation R; 2. Process R; 3. Read in the other relation S block by block; 4. Sent the results to an output block π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , If the operation is binary 3 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation R; 2. Process R; 3. Read in the other relation S block by block; 4. Sent the results to an output block Summary: Memory: M ≥ B(R) main memory R Unary operations γ(R), δ(R), τ(R) Cost: B(R) R process π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 4 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation R; 2. Process R; 3. Read in the other relation S block by block; 4. Sent the results to an output block π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C 5 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rsmall Rlarge disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 6 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rsmall Rlarge disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 7 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rsmall Rlarge disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 8 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rsmall Rlarge Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 9 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rsmall Rlarge Rsmall Build an efficient data structure for Rsmall (e. g. , sort Rsmall) process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 10 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rlarge US Rsmall Rlarge 1. Sort Rsmall; 2. FOR each tuple t in Rlarge DO IF t is not in Rsmall THEN put t to the output; 3. Send Rsmall to the output. Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 11 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rlarge ∩S Rsmall Rlarge 1. Sort Rsmall; 2. FOR each tuple t in Rlarge DO IF t is in Rsmall THEN put t to the output; Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 12 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rsmall Rlarge Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 13 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C −S is not commutative main memory Rsmall Rlarge Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 14 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C −S is not commutative main memory Rsmall Rlarge−S Rsmall 1. sort Rsmall; 2. FOR each tuple t in Rlarge DO IF t is not in Rsmall THEN put t to the output. process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 15 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C −S is not commutative main memory Rsmall Rlarge Rsmall −S Rlarge Rsmall 1. sort Rsmall; 2. FOR each tuple t in Rlarge DO IF t is in Rsmall THEN remove t from Rsmall; 3. send Rsmall to the output process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 16 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rlarge ∩B Rsmall Rlarge 1. Make Rsmall a balance tree; 2. FOR each tuple t in Rlarge DO IF t is in Rsmall THEN output t; and remove a copy of t from Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 17 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C −B is not commutative main memory Rsmall Rlarge Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 18 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C −B is not commutative main memory Rsmall Rlarge −B Rsmall 1. Make Rsmall a balance tree; 2. FOR each tuple t in Rlarge DO IF t is not in Rsmall THEN output t ELSE remove a copy of t from Rsmall; process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 19 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C −B is not commutative main memory Rsmall Rlarge Rsmall −B Rlarge Rsmall 1. Make Rsmall a balance tree; 2. FOR each tuple t in Rlarge DO IF t is in Rsmall THEN remove a copy of t from Rsmall; 3. Output Rsmall. process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 20 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rlarge × Rsmall Rlarge 1. FOR each tuple t in Rlarge DO cross join t and each tuple in Rsmall and send to the output. Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 21 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rlarge Rsmall Rlarge C Rsmall 1. FOR each tuple t in Rlarge DO cross join t and each tuple in Rsmall ; IF the join satisfies C THEN send to the output. Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 22 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rlarge Rsmall Rlarge 1. sort Rsmall by join attributes A; 2. FOR each tuple t in Rlarge DO find the tuples in Rsmall with the same A-value; join them with t and put in the output block Rsmall process disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , Rsmall , 23 C

One-pass algorithms Condition: the main memory M is sufficiently large General framework: 1. Read in an entire relation Rsmall; 2. Process Rsmall; 3. Read in the other relation Rlarge block by block; 4. Sent the results to an output block Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C main memory Rsmall Rlarge Summary: Rsmall Memory: M ≥ B(Rsmall) process Cost: B(Rsmall) + B(Rlarge) disk π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 24 C

Algorithms Implementing Relational Algebraic Operations Quick Review Operations requiring almost no space: π, σ, UB, table-scan Memory: M=2 Cost: π (R), σ(R), table-scan(R): B(R) UB(R, S): B(R) + B(S) One-pass Algorithms: γ, δ, τ, US, ∩S, −S, ∩B, − B, × , , C Unary: Memory: M ≥ B(R) Cost: B(R) Binary: Memory: M ≥ B(Rsmall) Cost: B(R) + B(S) π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 25 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 26 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 27 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 28 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S R US S 1. \ in the first execution of the \ t. R-loop, output t. S; 2. \ in an execution of the t. R-loop IF t. R = t. S THEN mark t. R; 3. \ at the end of the t. R-loop IF t. R is unmarked THEN output t. R. Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 29 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S R ∩S S 1. \ in an execution of the t. R-loop IF t. R = t. S THEN mark t. R; 2. \ at the end of the t. R-loop IF t. R is marked THEN output t. R. Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 30 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S R −S S 1. \ in an execution of the t. R-loop IF t. R = t. S THEN mark t. R; 2. \ at the end of the t. R-loop IF t. R is unmarked THEN output t. R. Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 31 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S R −S S 1. \ in an execution of the t. R-loop IF t. R = t. S THEN mark t. R; 2. \ at the end of the t. R-loop IF t. R is unmarked THEN output t. R. Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , C Not working for S −S R because −S 32 is not commutative

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 33 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S R ∩B S and R −B S Nested-loop does not seem to be effective for R ∩B S and R −B S Remark: we cannot simply mark t. R. Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 34 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S R ∩B S and R −B S Nested-loop does not seem to be effective for R ∩B S and R −B S Remark: we cannot simply mark t. R. Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 35 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 36 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop is particularly simple for join operations Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 37 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Nested-loop is particularly simple for join operations Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S R join S IF t. R and t. S are joinable THEN Join t. R and t. S; IF (the join is×or ) THEN output the join; ELSE \ the join is C output the join if it satisfies C Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 38 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Summary: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S Memory: M ≥ 2 Cost: T(R)*T(S) + T(R) Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 39 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Summary: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each tuple t. S in S DO Apply the operation □ on t. R and t. S Memory: M ≥ 2 Cost: T(R)*T(S) + T(R) Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , Very bad , 40 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Summary: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each block b. S in S DO Apply the operation □ on t. R and the tuples in b. S Memory: M ≥ 2 Cost: T(R)*B(S) + T(R) Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 41 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Summary: Nested-loop (R □ S): FOR each tuple t. R in R DO FOR each block b. S in S DO Apply the operation □ on t. R and the tuples in b. S Memory: M ≥ 2 Cost: T(R)*B(S) + T(R) Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , Still large , 42 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Summary: Nested-loop (R □ S): FOR each block b. R in R DO FOR each block b. S in S DO Apply the operation □ on the tuples in b. R and the tuples in b. S Memory: M ≥ 2 Cost: B(R)*B(S) + B(R) Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 43 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Summary: Nested-loop (R □ S): FOR each block b. R in R DO FOR each block b. S in S DO Apply the operation □ on the tuples in b. R and the tuples in b. S Memory: M ≥ 2 Cost: B(R)*B(S) + B(R) Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , Can it be further improved? , 44 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Summary: Nested-loop (R □ S): FOR max # blocks fitting in M in R DO FOR each block b. S in S DO Apply the operation □ on the tuples in R and the tuples in b. S Memory: M ≥ 2 Cost: B(R)*B(S)/M + B(R) Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 45 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Summary: Nested-loop (R □ S): FOR max # blocks fitting in M in R DO FOR each block b. S in S DO Apply the operation □ on the tuples in R and the tuples in b. S Memory: M ≥ 2 Cost: B(R)*B(S)/M + B(R) Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , Very good if B(R) or B(S) is only slightly larger than M , 46 C

For larger relations When relations cannot fit in main memory, onepass algorithms cannot be used. A generic algorithm for binary operations: Summary: Nested-loop (R □ S): FOR max # blocks fitting in M in R DO FOR each block b. S in S DO Apply the operation □ on the tuples in R and the tuples in b. S Memory: M ≥ 2 Cost: B(R)*B(S)/M + B(R) Binary operations on two relations R and S: US, ∩S, −S, ∩B, −B, ×, , C π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , Should pick the smaller relation for the outer loop (not working for −S ) , C 47

Algorithms Implementing Relational Algebraic Operations Quick Review Operations requiring almost no space: π, σ, UB, table-scan One-pass Algorithms: γ, δ, τ, US, ∩S, −S, ∩B, −B, × , , C Memory: M=2 Unary: Memory: M ≥ B(R) Cost: π (R), σ(R), table-scan(R): B(R) UB(R, S): B(R) + B(S) Nested-loop Algorithms For binary operations: US , ∩S , − S , × , , C Memory: M≥ 2 Cost: B(R)*B(S)/M + B(R) Binary: Memory: M ≥ B(Rsmall) Cost: B(Rsmall) + B(Rlarge) π, σ, US, ∩S, −S, UB, ∩B, −B, γ, δ, τ, table-scan, × , , 48 C