• append to file

• now sort

• delete Peter, change position of all of the rows after Peter $$O(n)$$

• table R has n rows and table S has m rows.

• tables R, S, and T and each has 1000 rows

1
2
3

SELECT *
FROM R, S, T
WHERE R.A = S.B AND S.C = T.D;

FOR EACH Row in R
   FOR EACH Row in S
       FOR EACH Row in T
           combine the row

$$O(n^3)$$ complexity

generalizing this: for an m-way join we have $$O(n^m)$$

$$O(n) = 1 \cdots n$$ $$O(log n) = 10^9 \cdots n$$ O-notation ignores constant factors

• C adds padding to align data types to avoid having to move values at runtime when accessing them

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  #include <stdio.h> typedef struct A { char a; // 1 byte -- at Memory location 0 // inject padding 7 byte long b; // 8 bytes -- at memory location 8 } A; int main(int argc, char *argv[]) { A *x = malloc(sizeof(A)); // = sizeof(char) + sizeof(long) - 1 + sizeof(long) printf("the size of A is %u", sizeof(A)); return 0; }

  the size of A is 16
  

• you can force the compiler to not align to save space, e.g., in gcc use __attribute__((packed))

  1 2 3 4 5 6 7 8 9 10 11 12 13

  #include <stdio.h> typedef struct A { char a; // 1 byte -- at Memory location 0 long b; // 8 bytes -- at memory location 1 (not aligned to 8 byte boundry) } __attribute__((packed)) A; int main(int argc, char *argv[]) { A *x = malloc(sizeof(A)); // = sizeof(char) + sizeof(long) - 1 + sizeof(long) printf("the size of A is %u", sizeof(A)); return 0; }

  the size of A is 9
  

• assume that 6 bytes stored on each pages (no page headers)

Block 1
[13,a]
[15,b]
Block 2
[16,c]

Block 1
[13,15,16]

Block 2
[a,b,c]

1

SELECT A FROM R;

Block 1
[13,15]
[a,b]
Block 2
[16],[c]

• table R(A,B,C,D)

  • #rows = 10,000,000

  • #rows where A = 3: 1,000,000

  • #rows where B = 4: 2,000,000

  • #rows where A = 3 AND B = 4: 300

• index on A and an index on B

1
2
3

SELECT *
FROM R
WHERE A = 3 AND B = 4

• index A and B

• 2 dimensions with 100 values

  • number of directory entries?

• 10 dimensions with 100 values

• number of directory entries?

• relations (bags and sets are unordered)

  • order-by cannot exist as an operator

• order as data

1

SELECT * FROM R ORDER BY A ASC;

• query result

• algebra result (order by operator) $$\omega_{A \rightarrow position}(R)$$

1
2
3
4

  SELECT *
    FROM Employee
   ORDER BY salary
   LIMIT 3

$$\sigma_{position \leq 3}(\omega_{salary \to position}(Employee)$$

1
2
3
4

  SELECT *
    FROM Employee
   ORDER BY salary
  LIMIT 1;

$$q_{max} = \pi_{name, salary}(\alpha_{max(salary) \to X}(Employee) \bowtie_{X = salary} (Employee)))$$ $$q_{top-2} = \pi_{name, salary}(\alpha_{max(salary) \to X}(\pi_{salary}(Employee) - \alpha_{max(salary)}(Employee)) \bowtie_{X = salary} (Employee)))$$ $$q = q_{max} \cup q_{top-2}$$

1
2
3

  SELECT *
    FROM R
   WHERE EXISTS (SELECT * FROM S)

• unnesting

• decorrelation

1
2
3
4
5
6

  SELECT R.*
    FROM R, (SELECT DISTINCT 1 FROM S)

  SELECT R.*
    FROM R, (SELECT count(*) AS cnt FROM S) AS sub
  WHERE cnt > 0

$$\pi_{A}(\sigma_{A = 3}(R \times S))$$

$$(\pi_{A}(\sigma_{A=3}(R)) \times \pi_{}(S))$$

• 10 buckets per attribute

• $$n$$ and $$B$$ buckets, then n-dimensional histogram $$B^n$$ buckets.

  digraph {
  rankdir=LR;

  a1 -> a2;
  a1 -> a3;
  a2 -> a4;
  a4 -> a5;

  }

• direct co-authors

1

  SELECT * FROM published;

• direct one step indirect co-authors

1
2
3
4
5

  SELECT * FROM published
  UNION
  SELECT p1.author, p2.coauthor
    FROM published p1, published p2
   WHERE p1.coauthor = p2.author;

• indirect up to two step indirect co-authors

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

  WITH oneStep AS (
    SELECT * FROM published
  ),
  twoStep AS (
  SELECT p1.author, p2.coauthor
    FROM oneStep p1, published p2
   WHERE p1.coauthor = p2.author
  ),
  threeStep AS (
  SELECT p1.author, p2.coauthor
    FROM twoStep p1, published p2
   WHERE p1.coauthor = p2.author
  )
  SELECT * FROM oneStep
   UNION
  SELECT * FROM twoStep
   UNION
  SELECT * FROM threeStep;

• user provides $$Q_{init} \cup Q_{step}$$

  • Initialization $$D_0 = Q_{init}(D)$$

  • Iteration 1: $$D_1 = Q_{step}(D_0)$$

…

• Iteration $i$: $$D_{i+1} = Q_{step}(D_i)$$

• Fix point computation: stop when $$D_{i} = D_{i-1}$$

• query $$q = (student \bowtie_{name=student} \sigma_{hoursAttended =15}(attendsLecture) \bowtie_{lecture= title} lecture$$

• exam duration: lecture duration

• exam handed out at lecture start through blackboard

• exam will be submitted to blackboard (send an email also just in case)

$$R \bowtie S$$ $$B(R) = 10,000$$ $$B(S) = 30$$ $$M = 101$$

• counter example

• 10 tables $$R_i$$ each has 1000 rows

• size of the cross produce $$1000^10 = 10^30$$ with using $$10^6$$ cores, $$10^24$$